I've got these 3 groups of data in range F2:G22 as below
(3 groups as minimal example, in reality many thousands of groups, and recurrent similar datasets expected in the future):
I need to number each group's rows sequentially, starting over at 1 at each new group.
The expected result would be like in range E1:E22.
I tried the following formula n cell C2 , then in cell D3:
=INDEX(IF(A2:A22="",COUNTIFS(B2:B22&A2:A22, B2:B22&A2:A22, ROW(B2:B22), "<="&ROW(B2:B22)),1))
In C2:
In D3:
That fixed partially the sequence issue, but there's still 2 issues I can't find remedy for.
1st remaining issue:
I'd prefer not having to manually do the C2 to D3 step each time I get new similar data (but would accomodate if there's no simple solution to this issue).
Is there a simple way to modify the formula to make it output the correct sequencing from C2 ?
2nd remaining issue:
At rows 7, 14 and 23 there still remain unecessary ending numbering for these intermediary rows in D7 , D14 , and D23:
I could only think of an extra manual step of filtering out the non-blank rows in Column A to fix this 2nd issue (i.e. Highlighting Column A > Data tab > Create Filter > Untick all > Tick Blanks > Copy All > Paste In new Sheet).
But would there be a way to do it in the same formula? I'm not seeing the way to add the proper filter or using another method in the formula. Any help is greatly appreciated.
EDIT (Sorry for Forgotten Sample):
| Formula Input A | Formula Input B | Formula Output 1 | Formula Output 2 | EXPECTED RESULT | rockinfreakshow | ztiaa | DATA | DATA BY GROUP |
|---|---|---|---|---|---|---|---|---|
| 7 | 1 | 1 | 7 | |||||
| 7 | 2 | 1 | 1 | 1 | 2 | Element-1 | Group-1 | |
| 7 | 3 | 2 | 2 | 2 | 3 | Element-2 | Group-1 | |
| 7 | 4 | 3 | 3 | 3 | 4 | Element-3 | Group-1 | |
| 7 | 5 | 4 | 4 | 4 | 5 | Element-4 | Group-1 | |
| 8 | 1 | 5 | 6 | 8 | ||||
| 8 | 2 | 1 | 1 | 1 | 7 | Element-1 | Group-2 | |
| 8 | 3 | 2 | 2 | 2 | 8 | Element-2 | Group-2 | |
| 8 | 4 | 3 | 3 | 3 | 9 | Element-3 | Group-2 | |
| 8 | 5 | 4 | 4 | 4 | 10 | Element-4 | Group-2 | |
| 8 | 6 | 5 | 5 | 5 | 11 | Element-5 | Group-2 | |
| 8 | 7 | 6 | 6 | 6 | 12 | Element-6 | Group-2 | |
| 9 | 1 | 7 | 13 | 9 | ||||
| 9 | 2 | 1 | 1 | 1 | 14 | Element-1 | Group-3 | |
| 9 | 3 | 2 | 2 | 2 | 15 | Element-2 | Group-3 | |
| 9 | 4 | 3 | 3 | 3 | 16 | Element-3 | Group-3 | |
| 9 | 5 | 4 | 4 | 4 | 17 | Element-4 | Group-3 | |
| 9 | 6 | 5 | 5 | 5 | 18 | Element-5 | Group-3 | |
| 9 | 7 | 6 | 6 | 6 | 19 | Element-6 | Group-3 | |
| 9 | 8 | 7 | 7 | 7 | 20 | Element-7 | Group-3 | |
| 9 | 9 | 8 | 8 | 8 | 21 | Element-8 | Group-3 | |
| 9 |
CodePudding user response：
Can you try:
=INDEX(LAMBDA(y,z,
IF(LEN(z),COUNTIFS(y,y,ROW(z),"<="&ROW(z)),))
(LOOKUP(ROW(G2:G),FILTER(ROW(G2:G),BYROW(G2:G,LAMBDA(z,IF(z<>OFFSET(z,-1,0),row(z),0))))),G2:G))
CodePudding user response：
You can simply use SCAN.
=SCAN(,G2:G,LAMBDA(a,c,IF(c="",,a 1)))
