A standard format normally starts with a serial number column. If your table has one such column, no doubt you can easily generate continues numbers in that column with the ROW function. But how to populate group wise serial numbers? I mean I want the serial number to restart again with the number 1 when a new group of rows begins. In this Google Sheets tutorial, you can learn this Group wise serial numbering technique.

Here is a screenshot that exactly shows what I wish to convey to you by saying group wise serial numbering in Google Sheets.

If you look at the values in column B, you can understand that the serial number in Column A restarts from one when a new group starts in Column B.

I’ve separated each group with underline and also different color patterns. What’s the formula I’ve used in Cell A2 to auto-populate group based serial numbering? I know you are curious to know about that.

Group Wise Serial Numbering in Google Sheets

Here is the formula to populate group wise serial numbers in Google Sheets.

=ArrayFormula(row(A2:A11)-match(B2:B11,B2:B11,0))

This formula is for the range A2: A11. But you can extend the numbering to any number of rows by just changing the row number 11 in this formula.

With the help of LEN function, you can even use this for infinitive ranges as below.

=ArrayFormula(if(len(B2:B),row(A2:A)-match(B2:B,B2:B,0),))

This formula will only populate the serial numbers up to the last non-blank cell in column B.

The Logic Behind the Formula that Used for Auto Generating Group Based Serial Numbers

In one line, I hope I can explain the logic. Here it is.

Groupwise Serial Number = Serial Numbers – Relative Position of items in the group

Let me explain the formula.

I am starting with the second part of the formula, i.e., the MATCH part.

The MATCH function in the above formula populates the relative position of the items as below.

I have entered the Match related formula separately in Cell H2 to show you the output. It’s the relative position of the values in Column B.

In Cell B2 the value is “Admin”. Its relative position is 1. But in the second row, it’s relative position is again 1 as it’s the same value. If it’s a different value, the relative position would be 2.

Now I am going to explain the first part of the formula, i.e., the ROW function part.

To make you understand, I’ve entered the Row formula in cell H2 and Match formula in I2 (please refer the below image). I’ve already detailed above what the Match Formula does.

Now the Row formula. The ROW formula just generates the numbers from 2 to 11.

Because the cell reference used in this formula argument is A2: A11. That means if it’s A1: A11, the formula would generate the serial numbers from 1 to 11.

If you just want to populate serial numbers, you can use this plain Row based formula. But what we want is group wise serial numbering in Google Sheets.

Again take a look at the values in Column H and I. If you deduct the values in Column I from H, you will get the group-wise serial number.

That’s it. Likewise, you can easily populate serial numbers matching to the group in any column. In the above example, the grouping is based on Column B. Enjoy!