Group-wise dependent serial numbering in Google Sheets. What does it mean?
Sometimes we may have a group of items placed under different categories in a Spreadsheet, for example, products coming under different grades.
In such a case, instead of group-wise serial numbering items, we can consider group-wise dependent serial numbering in Google Sheets.
I hope the best way to convey this idea of group-wise dependent serial numbering is to take the help of an image. Here you go!
Example:
There are four columns, and our topic of discussion is the array formula output in the fourth column. There is a formula in cell D1 in that column.
Let’s understand the data spread out in columns A and B.
The groups here are the items “A,” “B,” and “C.” If you consider the category, three unique records are coming under the first group. They are “Gr. 1,” “Gr. 2,” and “Gr. 3”
Under the group-wise dependent serial numbering column (the fourth column in the above example), you can see the numbering accordingly.
I assume I could convey the idea in a better way. Now let’s start writing the formula for the same in Google Sheets.
Array Formula for Group-Wise Dependent Serial Numbering in Google Sheets
Delete columns C and D (because they are not part of the data) and insert a column left to column A because it would be the ideal positioning of such a numbering column.
Insert the following formula in cell A1 in that newly inserted column.
=ArrayFormula(
{"Sr. No.";
if(B2:B="",,
vlookup(
B2:B&C2:C,
{index(unique(B2:C),0,1)&index(unique(B2:C),0,2),
COUNTIFS(
index(unique(B2:C),0,1),
index(unique(B2:C),0,1),
sequence(ROWS(index(unique(B2:C),0,1)),1),
"<="&sequence(ROWS(index(unique(B2:C),0,1)),1)
)},2,0
)
)
}
)The above formula would return the group-wise dependent serial numbering in column A.
Formula Logic and Explanation
In the below example in Google Sheets, I have slightly modified the data. Here the dependents are the Date of Receipt, not the Category as above. But there are no changes in the formula.
The idea here is to unique the data based on the group and category (here Item and Date of Receipt) columns.
In that unique data, return the cumulative count of the group (product/item). Then assign that to the original data based on group and dependent column.
Steps
1. Unique Product and Date of Receipt (Group and Dependent Column).
Insert the following Unique formula in cell E2.
=unique(B2:C)2. The output will be two columns. Split it into two individual columns using Index.
The following formulas are for cell E2 and F2.
=index(unique(B2:C),0,1)
=index(unique(B2:C),0,2)3. Return the running count of the above unique data.
In cell G2, insert the below formula.
=ARRAYFORMULA(COUNTIFS(E2:E11,E2:E11,ROW(E2:E11),"<="&ROW(E2:E11)))4. Assign the above returned numbers to original data Using Vlookup.
Insert the below Vlookup array formula in cell A2 to return the group-wise dependent serial numbers in column A.
=ArrayFormula(vlookup(B2:B13&C2:C13,{E2:E11&F2:F11,G2:G11},2,0))
The above simple formulas become a little complex when we are combining them in Google Sheets.
But we can’t skip that steps because most of us don’t like helper columns to solve the problems.
Removing Helper Columns
Combining the above four formulas may be one of the hurdles many of you may face. Because we can’t predict the total rows in the unique data as the data may grow in the future.
Among the above, we have to concentrate on the running (cumulative) count formula, which uses the row numbers in the unique data. Let’s clear that first.
I am talking about the formula in cell G2.
There is the cell range reference E2:E11 four times in the formula in cell G2. Among them, we can replace the first two references with the formula in cell E2.
The other two are within the Row function. That refers to the row numbers of E2:E11 rather than the data in E2:E11. Here we can replace ROW(E2:E11) with the below Sequence formula.
=SEQUENCE(ROWS(index(unique(B2:C),0,1)),1)You will see formula errors until you change the above two references and two expressions in the formula in cell G2. Once completed, it will start working correctly.
Here is the G2 formula after the said four corrections.
=ARRAYFORMULA(
COUNTIFS(
index(unique(B2:C),0,1),
index(unique(B2:C),0,1),
sequence(ROWS(index(unique(B2:C),0,1)),1),
"<="&sequence(ROWS(index(unique(B2:C),0,1)),1)
)
)The above is the vital step in writing the array formula that returns group-wise dependent serial numbering in Google Sheets.
Now let’s edit the A2 formula.
Replace E2:E11&F2:F11 with the corresponding two formulas from E2 and F2. Then replace G2:G11 with the G2 formula.
Then open the range. I mean, replace B2:B13&C2:C13 in that formula with B2:B&C2:C.
=ArrayFormula(
vlookup(
B2:B&C2:C,
{index(unique(B2:C),0,1)&index(unique(B2:C),0,2),
COUNTIFS(
index(unique(B2:C),0,1),
index(unique(B2:C),0,1),
sequence(ROWS(index(unique(B2:C),0,1)),1),
"<="&sequence(ROWS(index(unique(B2:C),0,1)),1)
)},2,0
)
)Note:- In the final formula, I have used an IF logic to exclude blank rows. In addition to that, I have added a field label to place the formula on the top row.
Group-Wise Dependent Serial Numbering in an Unsorted Data Range
Should I sort the data before using the above array formula?
Nope! You can test it yourself. Please do as follows.
Select the range B2:C13 (please refer to the image above). Go to the menu Data > Randomize range. It will make the selected range in random order.
Then please check the formula result. You can see that the formula correctly returns the group-wise dependent serial numbering in column A.
That’s all. Thanks for the stay. Enjoy!
Resources:
- Insert Sequential Numbers Skipping Hidden | Filtered Rows in Google Sheets.
- Backward Sequence Numbering in Google Sheets.
- Fill a Column with Sequence of Decimals in Google Sheets.
- Assign Same Sequential Numbers to Duplicates in a List in Google Sheets.
- Skip Blank Rows in Sequential Numbering in Google Sheets.
