Challenge Question: A Quest for Elegance

Tarkemelion

New Member
Joined
Jun 28, 2022
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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.

1708382470689.png
1708382507547.png


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":

Excel Formula:
=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!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Here's an idea:

Book1
ABCDE
1TitleStep 1Step 2Step 3
21Toys
31.1Plush
41.1.1Teddy Bear
51.1.1.1Brown
61.1.1.2Black
71.1.1.3Pink
81.1.2Penguin
91.1.2.1White
101.1.2.2Black
111.1.3Lion
121.1.3.1Male
131.1.3.2Female
141.2Puzzle
151.2.1Jigsaw
161.2.2Cubes
171.2.3Links
181.2.3.1Metal
191.2.3.2Plastic
201.3Boardgame
211.3.1Game 1
221.3.2Game 2
231.3.3Game 3
241.3.4Game 4
252Sports
262.1Soccer
272.1.1Shoes
282.1.1.1Size 6
292.1.1.2Size 7
302.1.1.3Size 8
312.1.1.4Size 9
322.1.2Knee Pads
332.1.3Gloves
342.2Basketball
352.2.1Shoes
362.2.1.1Size 6
372.2.1.2Size 7
382.2.1.3Size 8
392.2.1.4Size 9
402.2.2Ball
412.3Tennis
422.3.1Racquet
432.3.1.1Size 1
442.3.1.2Size 2
452.3.1.3Size 3
462.3.1.4Size 4
472.3.2Tennis ball
483Delivery
Sheet2
Cell Formulas
RangeFormula
A3:A48A3=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.
 
Upvote 0
Here's an idea:

Book1
ABCDE
1TitleStep 1Step 2Step 3
21Toys
31.1Plush
41.1.1Teddy Bear
51.1.1.1Brown
61.1.1.2Black
71.1.1.3Pink
81.1.2Penguin
91.1.2.1White
101.1.2.2Black
111.1.3Lion
121.1.3.1Male
131.1.3.2Female
141.2Puzzle
151.2.1Jigsaw
161.2.2Cubes
171.2.3Links
181.2.3.1Metal
191.2.3.2Plastic
201.3Boardgame
211.3.1Game 1
221.3.2Game 2
231.3.3Game 3
241.3.4Game 4
252Sports
262.1Soccer
272.1.1Shoes
282.1.1.1Size 6
292.1.1.2Size 7
302.1.1.3Size 8
312.1.1.4Size 9
322.1.2Knee Pads
332.1.3Gloves
342.2Basketball
352.2.1Shoes
362.2.1.1Size 6
372.2.1.2Size 7
382.2.1.3Size 8
392.2.1.4Size 9
402.2.2Ball
412.3Tennis
422.3.1Racquet
432.3.1.1Size 1
442.3.1.2Size 2
452.3.1.3Size 3
462.3.1.4Size 4
472.3.2Tennis ball
483Delivery
Sheet2
Cell Formulas
RangeFormula
A3:A48A3=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.
 
Upvote 0
Just as an alternative:

Excel Formula:
=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)))
 
Upvote 0
Another variation

Tarkemelion.xlsm
ABCDE
1TitleStep 1Step 2Step 3
21Toys
31.1Plush
41.1.1Teddy Bear
51.1.1.1Brown
61.1.1.2Black
71.1.1.3Pink
81.1.2Penguin
91.1.2.1White
101.1.2.2Black
111.1.3Lion
121.1.3.1Male
131.1.3.2Female
141.2Puzzle
151.2.1Jigsaw
161.2.2Cubes
171.2.3Links
181.2.3.1Metal
191.2.3.2Plastic
201.3Boardgame
211.3.1Game 1
221.3.2Game 2
231.3.3Game 3
241.3.4Game 4
252Sports
262.1Soccer
272.1.1Shoes
282.1.1.1Size 6
292.1.1.2Size 7
302.1.1.3Size 8
312.1.1.4Size 9
322.1.2Knee Pads
332.1.3Gloves
342.2Basketball
352.2.1Shoes
362.2.1.1Size 6
372.2.1.2Size 7
382.2.1.3Size 8
392.2.1.4Size 9
402.2.2Ball
412.3Tennis
422.3.1Racquet
432.3.1.1Size 1
442.3.1.2Size 2
452.3.1.3Size 3
462.3.1.4Size 4
472.3.2Tennis ball
483Delivery
Sheet2
Cell Formulas
RangeFormula
A2:A48A2=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))
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top