Sheets tip 261: A handy use case for the WRAPROWS function



Hi Reader,

Welcome to the Google Sheets Tips newsletter #261, your Monday morning espresso, in spreadsheet form!

Happy 4th of July to my US readers! πŸ‡ΊπŸ‡ΈπŸŽ‰

➜ News

I.
​
Gmail now supports mail merge with Google Sheets. You can link to a Sheet with up to 1,500 recipients, and then use those data columns as mail merge tags.

​Read more about Google Sheets <> Gmail mail merge​

_______

➜ Google Sheets Tip #261: A handy use case for the new WRAPROWS function

Suppose we have 20 names in a Google Sheet and we want to put them into four groups of five.

Perhaps it's a (dreaded) team building day and you need to put your colleagues into groups. Or you want to put your students into groups for a project.

Either way, the WRAPROWS and WRAPCOLS functions can do it for you very quickly.

Let's start with this dataset, 20 names in column A:

Use this WRAPROWS formula to generate the groups:

=WRAPROWS(A2:A21,4)

The "4" refers to the maximum number in each row. Each row has 4 items, so each column has 5 names.

Alternatively, you can achieve it with the WRAPCOLS function (although the groups will be different):

=WRAPCOLS(A2:A21,5)

The result is this:

But what if we wanted to randomize the names in the groups?

Currently the names stay in order based on their original position in column A.

Random Groups Method 1

A simple way to randomize the groups is to just use the Randomize Range feature.

Highlight the 20 names in column A, then go to:

Data > Randomize range

This will randomize the order of the names before we feed them into the WRAPROWS function.

But what if you don't want to change the original data?

Well, you can create a formula to randomize the range on the fly.

Random Groups Method 2

The SORT function has this amazing property that you can specify a different sort column from the range you want to sort.

I.e. we can specify the names as the range to sort, but then use a second column of random numbers to determine the order. Best of all, we can generate this array of random numbers inside the SORT function with the RANDARRAY function:

=SORT(A2:A21,RANDARRAY(20),TRUE)

Then we just wrap this randomly ordered list of names in the WRAPROWS function to generate the groups again:

=WRAPROWS(SORT(A2:A21 , RANDARRAY(20) , TRUE) , 4)

Nice!

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. An accurate British translation guide...​

Google Sheets Tips

Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.

Read more from Google Sheets Tips
Add a QA check to prompts to make them more accurate

Hi Reader, Welcome to the Future Proof newsletter, your Monday morning espresso, in AI form! (Formerly the Google Sheets Tips newsletter.) One of the stories that jumped out to me in the past week came from an interview with a tearful Cornell mathematics professor, asking "what's the point?" when AI surpasses human ability and steals the meaning (and joy) from mathematical research. (Which is a calling, not just a job to many.) One line stuck with me, and I'm paraphrasing here, but basically,...

Add custom Gemini instructions in sidebar of Google Sheets

Hi Reader, Welcome to the Future Proof newsletter issue 5, your Monday morning espresso, in AI form! (Formerly the Google Sheets Tips newsletter.) Lots of Gemini and Google Workspace news to share today and a tip on custom instructions. Find it all below, but first a little anecdote from my weekend. Kids say the funniest things. My 9-year-old declared over the weekend that he was going to build his own AI company and call his AI tool "Psycho"! I laughed and told him I loved the name. I was...

Output Google Doc as markdown file

Hi Reader, Welcome to Future Proof #4 (formerly the Google Sheets Tips newsletter), your Monday morning espresso, in AI form! My son and I had some fun 3-D printing over the weekend. I printed a rugged case to hold my Field Notes journals (see the finished result at the end of this email). The print run just beginning. It's impressive to watch something grow from nothing, although since the full print took around 3 hours I didn't watch the whole thing ;) And now, perhaps more than ever...