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
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:
REPT(CHAR(9), CODE(A1)-65)
where Char(9)
is a Tab.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.
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)))
.
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)
)