Automatic numbering in Excel with hierarchy

Question

I would like to do an automatic summary numbering. The only thing we could do is to number the A's (the titles) but the subtitles should be numbered automatically. If the title = 1, subtitle 1.1, below 1.1.1 and so on.

Let's say the titles are A, B and C are subtibles.

The pattern should be like this

1.A

1.1 B

1.2 B

2.A

2.1 B

2.1.1 C

So I tried this : https://stackoverflow.com/a/32321112/7968011

What I get

What we want

What we want

Answer 1

It took me a few years, several failed attempts, many corrections, a lot of help from ChatGPT and all the other artificial intelligences, but I managed to do it using reference cells.

I used the following references: • I - Item • S - Sub-item • B - Sub-sub-item • V - Empty

My initial cell is B8, and the reference cell is I8.

=LET(
last_i, MAX(IF($I$8:I8="i", ROW($I$8:I8), 0)),
last_s, MAX(IF($I$8:I8="s", ROW($I$8:I8), 0)),
last_b, MAX(IF($I$8:I8="b", ROW($I$8:I8), 0)),
range_from_last_i, INDIRECT("I" & last_i + 1 & ":I" & ROW(I8)),
count_i, COUNTIF($I$8:I8, "i"),
count_s, COUNTIF(range_from_last_i, "s"),
count_b, IF(last_s > 0, ROW(I8) - last_s, 1),
result, SWITCH(
    I8,
    "v", "",
    "i", count_i,
    "s", TEXTJOIN(".", , count_i, count_s),
    "b", TEXTJOIN(".", , count_i, count_s, count_b),
    ""
),
result)

worksheet

Answer 2

Following on from the previous answer, here is a solution that will address the problem of sections >9. The formula is lengthy - but works - and I am sure there will be some smarter way of addressing the problem.

Firstly the structure of the working solution I have. Column C contains manually entered numbers that represent the levels in the hierarchy e.g., 1 2 2 3 2 3 3 4 4 4 2 2 3 The top of column B contains the Level 1 number e.g. 1 but could be 10 or 100.

The main formula is placed in column B below the Level 1 number and the formula looks like:

=IFERROR(IF(C3>C2,B2&"."&1,IF(C2>C3,LEFT(B2,HLOOKUP(C3,Dots,ROW()-2)+HLOOKUP(C3+1,Dots,ROW()-2)-HLOOKUP(C3,Dots,ROW()-2)-2)&MID(B2,HLOOKUP(C3+1,Dots,ROW()-2)-1,1)+1,LEFT(B2,LEN(B2)-1)&VALUE(RIGHT(B2,1)+1))),"")

The final part of the solution structure is a table containing the positions of the "."s in each string. The first "." is found using

=IFERROR(FIND(".",B3),"")

The second/ third/fourth dot is found using e.g.

=IFERROR(FIND(".",$B3,D3+1),"")

Add a header to this "table" with the numbers 2,3,4,5 representing the levels of the hierarchy. Name the table "Dots".

How does it work? The key to the solution is knowing where the dots are in the previous section. If the sections are limited to <=9 then the dots are always in the even positions. However they move to the right as soon as there is a double or triple digit number.

The formula has 4 sections; a) Iferror then "". Always keeps the sheet tidy. b) If the level number increases from one section to the next, then the previous section is supplemented with a ".1" c) If the level number remains the same, the the last character is incremented (value) by 1. d) If the level decreases (the complex part) the HLookup takes information from the table to reduce the previous section string back to the new level and add the incremental section number.

Working Solution

Answer 3

Fast and dirty.

Just enter the first section manualy.
Then insert it below:

=IF(A3="down",B2&"1.",IF(A3="up",LEFT(B2,LEN(B2)-4)&MID(B2,LEN(B2)-3,1)+1&".",LEFT(B2,LEN(B2)-2)&MID(B2,LEN(B2)-1,1)+1&"."))

When you write "down" it will add "1." in the end of the string above.
When you write "up" it will remove the last 2 chars and add 1 to the last char of the string above.
if you dont write nothing it will add 1 to the last char.

bug: "up" will not work if section is > 9.

Answer 4

If you have your Level Marker as "A" / "B" / "C" in Column A, and the heading in Column B, then you can use the following (convoluted) code:

=REPT(CHAR(9), CODE(A1)-65) & SUMPRODUCT(--(A:A="A")*--(ROW(A:A)<=ROW(A1))) & "." & IF(CODE(A1)>65,SUMPRODUCT(--(A:A="B")*--(ROW(A:A)<=ROW(A1))*--(ROW(A:A)>=MAX(--ROW(A:A)*--(A:A="A")*--(ROW(A:A)<=ROW(A1))))) & ".","") & IF(CODE(A1)>66,SUMPRODUCT(--(A:A="C")*--(ROW(A:A)<=ROW(A1))*--(ROW(A:A)>=MAX(--ROW(A:A)*--(A:A="B")*--(ROW(A:A)<=ROW(A1))))) & ".","") & CHAR(9) & B1

Let's break it down into steps:

1. Start with Tabs to indent the heading (0 for "A", 1 for "B", 2 for "C"): REPT(CHAR(9), CODE(A1)-65) where Char(9) is a Tab.

2. Next, we want to count how many "A"s have we had. We can use SUMPRODUCT to run this as an Array Formula, looking for cells where the value is "A" and the Row is <= current row: SUMPRODUCT(--(A:A="A")*--(ROW(A:A)<=ROW(A1))). Shove a dot after that, and you have your heading number.

3. Next, IF Column A is "B" or later in the alphabet (IF(CODE(A1)>65, since CODE("A")=65, CODE("B")=66, etc) then we want to count how many "B"s since the last "A". This is very similar to our last query, but we need a ROW(A:A)>=LAST_A. But, what is LAST_A? Well, we want the MAX Row where Column A = "A" and Row <= current row. So, MAX(--ROW(A:A)*--(A:A="A")*--(ROW(A:A)<=ROW(A1))).

4. This gives SUMPRODUCT(--(A:A="B")*--(ROW(A:A)<=ROW(A1))*--(ROW(A:A)>=MAX(--ROW(A:A)*--(A:A="A")*--(ROW(A:A)<=ROW(A1)))))

Now, we need to add the IF and the full-stop, to get

If(Code(A1)>65,SUMPRODUCT(--(A:A="B")*--(ROW(A:A)<=ROW(A1))*--(Row(A:A)>=MAX(--ROW(A:A)*--(A:A="A")*--(ROW(A:A)<=ROW(A1))))) & ".","")

Repeat the same for all "C"s since the last "B", and then finally add a Tab (CHAR(9)) and the value in Column B.

(If you want, for example, 4 spaces or 6 hyphens or 7 dots instead of Tabs at the start of the row or between the number and the tile, just replace the first or last CHAR(9))

{EDIT} Example: