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:

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.

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​