Group Wise Serial Numbering in Google Sheets

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.

relative position for serial numbering in Google Sheets

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.

row and match function to generate group related numbering

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!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

5 COMMENTS

  1. Hi Prashanth,

    I’ve been trying to figure out how to do this for the longest time. I never considered combining match & row. This is fantastic.

    Thank you so much!
    Dan

    • Hi, Bajran,

      You should format the numbers as below.

      1. Select the range/array containing the numbers.
      2. Click the menu Format > Number > More formats > Custum number format.
      3. Enter 00 in the blank field.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.