Create a numbering system by levels

[FryGirl]

I posted this question years ago and have a need to use it again. I'm specifically asking about the macro in post #7.

If you want, you can use a Level number of 0 at any time (including at the start of the Level list) to start a new, non-dotted (chapter) Number sequence (that is, a 1 or 2 or 3 etc.). If you start the Level list with 0, then the (chapter) Number list will start with 1; if you start the Level list with 1, then the (chapter) Number list will start with 1.1; if you start the Level list with 2, then the (chapter) Number list will start with 1.1.1; etc

​

In reading this note, I would think I could change something in the code to start the list with a 1 as seen in column T. Column T are the results I would like based on the level in Column R.

Data Range

	R​	S​	T​
1​	Level​	​	​
2​	1​	1.1​	1​
3​	2​	1.1.1​	1.1​
4​	3​	1.1.1.1​	1.1.1​
5​	3​	1.1.1.2​	1.1.2​
6​	2​	1.1.2​	1.2​
7​	3​	1.1.2.1​	1.2.1​
8​	3​	1.1.2.2​	1.2.2​
9​	2​	1.1.3​	1.3​
10​	3​	1.1.3.1​	1.3.1​
11​	3​	1.1.3.2​	1.3.2​

 

[J.Ty.]

I have made a formula based solution, mostly for fun of doing it.

Columns B-E describe how it has been done, column F contains a single formula solution composed from them, plus on IF to make it blank when column A contains a blank.
The solution does not handle jumps, e.g., when after R=2 you immediately put R=4, omitting R=3. I assume that you never want to do that.

You can experiment with it here.

J.Ty.

Book1
	A	B	C	D	E	F
1	R	row of the most recent change	row of the most recent reset to 0	increments sice the most recent reset	counter	complete counter with blank support
2	1	0	0	1	1	1
3	2	2	2	1	1.1	1.1
4	3	3	3	1	1.1.1	1.1.1
5	3	4	3	2	1.1.2	1.1.2
6	3	5	3	3	1.1.3	1.1.3
7	4	6	6	1	1.1.3.1	1.1.3.1
8	2	3	2	2	1.2	1.2
9	3	8	8	1	1.2.1	1.2.1
10	1	2	0	2	2	2
11	2	10	10	1	2.1	2.1
12	3	11	11	1	2.1.1	2.1.1
13	4	12	12	1	2.1.1.1	2.1.1.1
14	3	12	11	2	2.1.2	2.1.2
15	4	14	14	1	2.1.2.1	2.1.2.1
Sheet1

Cell Formulas
Range	Formula
B2:B15	B2	=MAX(ROW(A$1:A1)*(A$1:A1<=A2))
C2:C15	C2	=MAX(ROW(A$1:A1)*(A$1:A1<A2))
D2:D15	D2	=COUNTIF(INDEX(A$1:A1,C2):A2,A2)
E2:F2	E2	="1"
E3:E15	E3	=IF(A3=1+A2,E2&".1",LEFT(INDEX(E$1:E2,B3),LEN(INDEX(E$1:E2,B3))-LEN(D3-1))&D3)
F3:F15	F3	=IF(ISBLANK(A3),"",IF(A3=1+A2,F2&".1",LEFT(INDEX(F$1:F2,MAX(ROW(A$1:A2)*(A$1:A2<=A3))),LEN(INDEX(F$1:F2,MAX(ROW(A$1:A2)*(A$1:A2<=A3))))-LEN(COUNTIF(INDEX(A$1:A2,MAX(ROW(A$1:A2)*(A$1:A2<A3))):A3,A3)-1))&COUNTIF(INDEX(A$1:A2,MAX(ROW(A$1:A2)*(A$1:A2<A3))):A3,A3)))

 

[FryGirl]

Hi J.TY.,

Thank you for your help here, but I decided to go with this call at the end of the macro. Works well.

Sub ReplaceFirstTwo()
    Dim Cell As Range
    For Each Cell In Range("S2", Range("S" & Rows.Count).End(xlUp))
        Cell.Value = Mid(Cell, 3, Len(Cell))
    Next Cell
End Sub