Challenge Question: A Quest for Elegance

[Tarkemelion]

Hi All,

I've been trying to create a numbering formula in one cell which can instantly itemise a schedule of items just by dragging and copying down said formula. However, it is quite complex (read janky) and I'm sure there is a more elegant solution out there. I'm looking for tips, tricks, and new functions which might make this formula more efficient and robust.

As in the above images, the numbering style I need to emulate is an X.X.X.X style where the first digit is shown and incremented for each major title, and subsequent digits are added according to the "step" in the column. The numbering style is fairly simple but the logic to implement it was not. Here is the formula placed in the cell next to "Toys":

=IF(B3<>"",OFFSET(INDEX($B$2:B2,MATCH(LOOKUP(2,1/($B$2:B2<>""),$B$2:B2),$B$2:B2,0),1),0,-1)+1,IF(C3<>"",IF(LEFT(A2,1)=LEFT(OFFSET(INDEX($C$2:C2,MATCH(LOOKUP(2,1/($C$2:C2<>""),$C$2:C2),$C$2:C2,0)),0,-2),1),CONCAT(LEFT(A2),".",MID(OFFSET(INDEX($C$2:C2,MATCH(LOOKUP(2,1/($C$2:C2<>""),$C$2:C2),$C$2:C2,0)),0,-2),3,1)+1,),CONCAT(LEFT(A2,1),".",IF(C2<>"",MID(A2,3,1)+1,1))),IF(D3<>"",IF(LEFT(A2,5)=LEFT(OFFSET(INDEX($D$2:D2,MATCH(LOOKUP(2,1/($D$2:D2<>""),$D$2:D2),$D$2:D2,0)),0,-3),5),CONCAT(LEFT(A2),".",MID(A2,3,1),".",MID(OFFSET(INDEX($D$2:D2,MATCH(LOOKUP(2,1/($D$2:D2<>""),$D$2:D2),$D$2:D2,0)),0,-3),5,1)+1),CONCAT(LEFT(A2,1),".",MID(A2,3,1),".",IF(D2<>"",MID(A2,5,1)+1,1))),IF(E3<>"",CONCAT(LEFT(A2,1),".",MID(A2,3,1),".",MID(A2,5,1),".",IF(E2<>"",MID(A2,7,1)+1,1)),1))))

It might be difficult to tease out from the above word salad, but effectively I've looked at IF column B contains something, check the first item immediately above in column B and extract the number. Then add 1. This is why the number next to "Title" is a zero.
For each step, it is checking if that cell contains something and then looks at if there was something that came prior or if it is the first in that stack. This proved quite the challenge and really is the bulk of the formula as it gets repeated three times over for each subsequent column.

If this was VBA I could set up a loop or some other tidier solution. My solution also has these current weaknesses (that I've found, there are probably more):

1. If a step heading is repeated, the formula finds the step heading that is at the top, not the most recent heading. This ruins the numbering.
2. If a column is skipped (i.e. something in Step 1 and then the next row contains something in Step 3) the whole formula dissolves. Not great.
3. If a row is blank, everything breaks. Also not great.

My challenge to the community, if any are willing and able, if to produce the same result with a more consolidated formula. Bonus points if you want to explain your logic as I enjoying learning and upskilling.

Thanks in advance!

 

[Eric W]

Here's an idea:

Book1
	A	B	C	D	E
1		Title	Step 1	Step 2	Step 3
2	1	Toys
3	1.1		Plush
4	1.1.1			Teddy Bear
5	1.1.1.1				Brown
6	1.1.1.2				Black
7	1.1.1.3				Pink
8	1.1.2			Penguin
9	1.1.2.1				White
10	1.1.2.2				Black
11	1.1.3			Lion
12	1.1.3.1				Male
13	1.1.3.2				Female
14	1.2		Puzzle
15	1.2.1			Jigsaw
16	1.2.2			Cubes
17	1.2.3			Links
18	1.2.3.1				Metal
19	1.2.3.2				Plastic
20	1.3		Boardgame
21	1.3.1			Game 1
22	1.3.2			Game 2
23	1.3.3			Game 3
24	1.3.4			Game 4
25	2	Sports
26	2.1		Soccer
27	2.1.1			Shoes
28	2.1.1.1				Size 6
29	2.1.1.2				Size 7
30	2.1.1.3				Size 8
31	2.1.1.4				Size 9
32	2.1.2			Knee Pads
33	2.1.3			Gloves
34	2.2		Basketball
35	2.2.1			Shoes
36	2.2.1.1				Size 6
37	2.2.1.2				Size 7
38	2.2.1.3				Size 8
39	2.2.1.4				Size 9
40	2.2.2			Ball
41	2.3		Tennis
42	2.3.1			Racquet
43	2.3.1.1				Size 1
44	2.3.1.2				Size 2
45	2.3.1.3				Size 3
46	2.3.1.4				Size 4
47	2.3.2			Tennis ball
48	3	Delivery
Sheet2

Cell Formulas
Range	Formula
A3:A48	A3	=LET(f,FILTER(A$2:E2,MMULT(--(B$2:E2<>""),{1;1;1;1})),c,XMATCH("*",B3:E3,2),r,ROWS(f),ul,IF(c=1,"",INDEX(f,r,c)),a,textsplit(XLOOKUP("*",INDEX(f,0,c+1),INDEX(f,0,1),"xx",2,-1),"."),s,{1,2,3,4},ac,TEXTJOIN(".",1,IFS(s<c,a,s=c,a+1,1,"")),IF(ul<>"",A2&".1",ac))

This should work even if you have blank lines, because I filtered out all blank lines. As far as extra heading lines, I'd just put them in the top row, then freeze it so that it's always visible.

I discovered that there are only 2 rules you need to follow to get your numbering to work. First, if there is something in the cell one row above and one row to the left, then just take the number from that line and add .1 to the end. If that's not true, then just find the first non-blank cell above the filled cell in the current row, and add 1 to the last division of that number. Maybe someone else can find a simpler way to accomplish that, I found it quite tricky.

 

[Tarkemelion]

Here's an idea:

Book1
	A	B	C	D	E
1		Title	Step 1	Step 2	Step 3
2	1	Toys
3	1.1		Plush
4	1.1.1			Teddy Bear
5	1.1.1.1				Brown
6	1.1.1.2				Black
7	1.1.1.3				Pink
8	1.1.2			Penguin
9	1.1.2.1				White
10	1.1.2.2				Black
11	1.1.3			Lion
12	1.1.3.1				Male
13	1.1.3.2				Female
14	1.2		Puzzle
15	1.2.1			Jigsaw
16	1.2.2			Cubes
17	1.2.3			Links
18	1.2.3.1				Metal
19	1.2.3.2				Plastic
20	1.3		Boardgame
21	1.3.1			Game 1
22	1.3.2			Game 2
23	1.3.3			Game 3
24	1.3.4			Game 4
25	2	Sports
26	2.1		Soccer
27	2.1.1			Shoes
28	2.1.1.1				Size 6
29	2.1.1.2				Size 7
30	2.1.1.3				Size 8
31	2.1.1.4				Size 9
32	2.1.2			Knee Pads
33	2.1.3			Gloves
34	2.2		Basketball
35	2.2.1			Shoes
36	2.2.1.1				Size 6
37	2.2.1.2				Size 7
38	2.2.1.3				Size 8
39	2.2.1.4				Size 9
40	2.2.2			Ball
41	2.3		Tennis
42	2.3.1			Racquet
43	2.3.1.1				Size 1
44	2.3.1.2				Size 2
45	2.3.1.3				Size 3
46	2.3.1.4				Size 4
47	2.3.2			Tennis ball
48	3	Delivery
Sheet2

Cell Formulas
Range	Formula
A3:A48	A3	=LET(f,FILTER(A$2:E2,MMULT(--(B$2:E2<>""),{1;1;1;1})),c,XMATCH("*",B3:E3,2),r,ROWS(f),ul,IF(c=1,"",INDEX(f,r,c)),a,textsplit(XLOOKUP("*",INDEX(f,0,c+1),INDEX(f,0,1),"xx",2,-1),"."),s,{1,2,3,4},ac,TEXTJOIN(".",1,IFS(s<c,a,s=c,a+1,1,"")),IF(ul<>"",A2&".1",ac))

This should work even if you have blank lines, because I filtered out all blank lines. As far as extra heading lines, I'd just put them in the top row, then freeze it so that it's always visible.

I discovered that there are only 2 rules you need to follow to get your numbering to work. First, if there is something in the cell one row above and one row to the left, then just take the number from that line and add .1 to the end. If that's not true, then just find the first non-blank cell above the filled cell in the current row, and add 1 to the last division of that number. Maybe someone else can find a simpler way to accomplish that, I found it quite tricky.

Mate, what an observation. I love that you extracted just two rules from the pattern rather than looking at every use case in a nightmarish tree diagram of "IFs" like I did.

From a human perspective, the numbering scheme is pretty simple but I just couldn't get it to work in a way that my brain would do it!

I'll likely close this out at the end of the week but for now I'll leave it open in case anyone else wants to have a go.

 

[RoryA]

Just as an alternative:

=IF(B3<>"",TEXTBEFORE(A2&".",".")+1,LET(cc,XMATCH("*",B3:E3,2),parts,TEXTSPLIT(A2&".",".",,FALSE),IF(INDEX(parts,1,cc)="",A2&".1",TEXTBEFORE(A2,".",cc-1)&"."&INDEX(parts,cc)+1)))

 

[Peter_SSs]

Another variation

Tarkemelion.xlsm
	A	B	C	D	E
1		Title	Step 1	Step 2	Step 3
2	1	Toys
3	1.1		Plush
4	1.1.1			Teddy Bear
5	1.1.1.1				Brown
6	1.1.1.2				Black
7	1.1.1.3				Pink
8	1.1.2			Penguin
9	1.1.2.1				White
10	1.1.2.2				Black
11	1.1.3			Lion
12	1.1.3.1				Male
13	1.1.3.2				Female
14	1.2		Puzzle
15	1.2.1			Jigsaw
16	1.2.2			Cubes
17	1.2.3			Links
18	1.2.3.1				Metal
19	1.2.3.2				Plastic
20	1.3		Boardgame
21	1.3.1			Game 1
22	1.3.2			Game 2
23	1.3.3			Game 3
24	1.3.4			Game 4
25	2	Sports
26	2.1		Soccer
27	2.1.1			Shoes
28	2.1.1.1				Size 6
29	2.1.1.2				Size 7
30	2.1.1.3				Size 8
31	2.1.1.4				Size 9
32	2.1.2			Knee Pads
33	2.1.3			Gloves
34	2.2		Basketball
35	2.2.1			Shoes
36	2.2.1.1				Size 6
37	2.2.1.2				Size 7
38	2.2.1.3				Size 8
39	2.2.1.4				Size 9
40	2.2.2			Ball
41	2.3		Tennis
42	2.3.1			Racquet
43	2.3.1.1				Size 1
44	2.3.1.2				Size 2
45	2.3.1.3				Size 3
46	2.3.1.4				Size 4
47	2.3.2			Tennis ball
48	3	Delivery
Sheet2

Cell Formulas
Range	Formula
A2:A48	A2	=IF(B2="",LET(d,".",c,XMATCH("*",B2:E2,2),p,A1&d&0,TEXTBEFORE(p,d,c-1)&d&(INDEX(TEXTSPLIT(p,d),c)+1)),COUNTA(B$2:B2))

 

[Tarkemelion]

Thanks for all the suggestions. Something massive that I've learnt is the "=LET( )" function. My attempt really was a copy and paste of the same formula (with minor tweaks) and I've realised just how much of a benefit to readability and functionality this function provides. So thank you all.

Honestly chuffed at how collapsed down it has become.

 

[Peter_SSs]

You're welcome. Glad we were able to contribute. Thanks for the follow-up.