Dependent hierarchical numbering in Excel

Question

I'm new to Stack Overflow, so please excuse me, If I'm doing something stupid. I have a question regarding Excel, I want a column (let's say A) to do hierarchical numbering depending on the number in another column (let's say B). B clarifies the level of the row. F.e. if the level is 3, the numbering should have the format "X.X.X". I added an image as an example of how I imagine it to be.

I tried to develop an adecuate formular by myself and with the help of AI, but nothing seems to work. Has someone a solution for my issue?

I'm trying to find help for the development of a formular for my excel sheet and expect people either to give me tips regarding functions or even deliver a finished formula.

Answer 1

I've prepared UDF function which can be used as a formula in the sheet.
You should place the code in a standard module (Alt+F11 to open VBA editor, then Insert Module):

Function Numbers(list)
    ' list - 1-D array or range of cells
    list = list
    If UBound(list) = 1 Then list = Application.Transpose(list)
    Dim m As Long
    m = Application.Max(list)
    Dim ind, ind1
    ReDim ind(1 To m) As String
    Dim i As Long, k As Long, c As Long
    Dim res
    ReDim res(1 To UBound(list), 1 To 1) As String
    For i = 1 To UBound(list)
        k = list(i, 1)
        For c = 1 To k - 1
            If ind(c) = "" Then ind(c) = 1
        Next c
        If ind(k) = "" Then ind(k) = 1 Else _
        ind(k) = ind(k) + 1
        For c = k + 1 To m
            ind(c) = ""
        Next c
        ind1 = ind
        ReDim Preserve ind1(1 To k)
        res(i, 1) = Join(ind1, ".")
    Next i
    Numbers = res
End Function

In a sheet you use it as a formula:
=Numbers(B1:B19)
In a new Excel (2021, 365) it is a dynamic formula which gives the whole array at once.

Answer 2

Here is a formula version:

=BYROW(DROP(REDUCE({0,0,0,0,0,0},B1:B19,
   LAMBDA(a,c,LET(
      seq,SEQUENCE(1,6),
      prev,TAKE(a,-1),
      count,SUMPRODUCT(--(prev>0)),
      VSTACK(a,
        IF(c<=count,
           IFS(seq<c,prev,seq=c,prev+1,seq>c,0),
           IFS(seq<=count,prev,seq>c,0,TRUE,1)))))),
      1),
   LAMBDA(r,TEXTJOIN(".",TRUE,IF(r=0,"",r))))