Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Sheets tip 257: I've lost count how many times I've used this formula...
Hi Reader,
Welcome to the Google Sheets Tips newsletter #257, your Monday morning espresso, in spreadsheet form!
_______
➜ Google Sheets Tip #257: How to number groups
Today's tip is one I've used countless times in my spreadsheets.
It's a hugely useful formula if you work with data in groups.
As always, there are many different ways to achieve this. I'll show you two methods in this email: a simple, manual way, and a more advanced single formula method.
Here's the data in column A and the expected output in column B:
We want to create a formula that generates the numbering pattern in column B.
Method 1: Simple method
This formula checks whether the value in the current row is different to the one on the row above.
If they are different, the output of the formula is 1 (i.e. start the numbering anew).
If the are the same then the numbering increments the value from the line above by 1.
The formula in B2 is:
=IF(A2<>A1,1,B1+1)It can be dragged down as far as required.
This picture illustrates what's happening:
One drawback with this formula is that if you delete a row or insert a row, you need to drag the formula down from the top again. Otherwise your references will be out of line.
Method 2: SCAN function method
This method uses the SCAN, IF and OFFSET functions.
The value "name" inside the LAMBDA is the name of the current value from the input array (the values in column A).
The OFFSET(name, -1, 0) grabs the value of the row above and the IF compares it against the current "name" value.
=SCAN(0 , A2:A15 , LAMBDA( counter , name , IF( name<>OFFSET(name,-1,0) , 1 , counter+1 )))This formula has a number of advantages:
- it only needs to be entered once, in cell B2
- it will update the numbering automatically if you delete a row, add a row, or change the groups
In case you haven't noticed, I LOVE these new (2022) LAMBDA functions.
If you're new to them, do yourself a favor and check out my free Lambda Function course.
_______
If you enjoyed this newsletter, please forward it to a friend who might enjoy it.
Have a great week!
Cheers,
Ben
P.S. Map of Ancient Grease
Google Sheets Tips
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 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...
Hi Reader, Welcome to the Future Proof #3 (formerly the Google Sheets Tips newsletter), your Monday morning espresso, in AI form! Here in the Mid-Atlantic region, fall has arrived with cooler temperatures, blustery winds, and grey, rainy days. (As a Brit, it makes me feel right at home!) That means it's officially the end of the shorts-as-the-default-choice season. And, there's a riot of color coming our way over the next few weeks as the maple trees put on a show 🍁. Today, I'm sharing a tip...