0

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):

i1

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:

i1

In D3:

i2

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:

i3

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
Lod
  • 657
  • 1
  • 9
  • 30
  • 1
    any sheet sample? – player0 Feb 04 '23 at 14:17
  • It felt I had soething missing... Sorry @player0 I went out and forgot the table, corrected above. – Lod Feb 04 '23 at 17:26
  • For later reference and ease of search as related questions: https://stackoverflow.com/questions/75449393/how-to-increment-only-if-cell-is-not-empty-on-input-with-ifna-in-google-sheets – Lod Aug 08 '23 at 19:16

2 Answers2

2

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))

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • 1
    the above formula takes into account a consecutive duplicate group as a unique group of itself (as you can see in the screenshot Column_G `Group-1` repeats from row 15 after the first set in rows 3-6). But if your dataset Column_G doesn't accommodate such duplication scenarios you could go with this simple one maybe `=BYROW(G2:G,LAMBDA(aixx,IF(aixx="",,COUNTIF(G2:aixx,aixx))))` – rockinfreakshow Feb 04 '23 at 15:42
  • @rockingfreakshow thank you! Works great on preliminary testing as here: https://i.imgur.com/8FfPi0F.png I'll test on extended datasets asa. Be well! – Lod Feb 04 '23 at 17:28
1

You can simply use SCAN.

=SCAN(,G2:G,LAMBDA(a,c,IF(c="",,a+1)))

enter image description here

Sample sheet

z''
  • 4,527
  • 2
  • 3
  • 12
  • 1
    thanks for the solution too! Very interesting, I just discovered the `SCAN` function thanks to you. Working as expected too: https://i.imgur.com/cCeAR9C.png Many thanks! Be well! – Lod Feb 04 '23 at 17:36