1

I would like to change the existing numbering to multi-level numbering using excel formulas, example below.

enter image description here

The solutions found are too difficult for me to process. Thank you in advance for your help.

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
tompaz
  • 23
  • 2
  • 2
    Shouldn't be something like this with the formula if i am not wrong here: `=A1&"."&COUNTIF(A$1:A1,A1)` and with dynamic spill array `=BYROW(A1:A15,LAMBDA(x,LET(y,A1:A15,x&"."&COUNTIF(INDEX(y,1):x,x))))` or with `MAP()` `=MAP(A1:A15,A1:A15,LAMBDA(x,y,x&"."&COUNTIF(INDEX(y,1):x,x)))` – Mayukh Bhattacharya Mar 24 '23 at 11:13
  • 1
    The first solution meets my needs. Thanks again – tompaz Mar 24 '23 at 12:37
  • 1
    Since your data is grouped, you could use the far more efficient running count presented in [this post](https://stackoverflow.com/a/75773001). With a few minor modifications (instead of `3,` use `6,Data&"."&`), it would boil down to this: `=LET(Data,A2:A16,Data&"."&SCAN(0,Data=DROP(VSTACK("",Data),-1),LAMBDA(a,b,IF(b,a+1,1))))`. – VBasic2008 Mar 25 '23 at 05:09

1 Answers1

2

So if i have understood correctly and based on my comments, one could use the following formulas based on their excel versions:

enter image description here

• Formula used in cell B1

=A1&"."&COUNTIF(A$1:A1,A1)

• Formula used in cell C1

=BYROW(A1:A15,LAMBDA(x,LET(y,A1:A15,x&"."&COUNTIF(INDEX(y,1):x,x))))

• Formula used in cell D1

=MAP(A1:A15,LAMBDA(m,LET(n,A1:A15,m&"."&COUNTIF(INDEX(n,1):m,m))))

Edit : 3/25/2023

As suggested by VBasic2008 Sir, one can also use the following alternatives for more efficient output.

enter image description here

• Formula used in cell E1

=BYROW(A1:A15,LAMBDA(x,LET(y,A1,x&"."&COUNTIF(y:x,x))))

Or,

• Formula used in cell F1

=LET(Data,A1:A15,Data&"."&SCAN(0,Data=DROP(VSTACK("",Data),-1),LAMBDA(a,b,IF(b,a+1,1))))
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32