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! πΊπΈπ
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β
_______
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.
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.
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
Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
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,...
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...
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...