formula to generate multilevel list numbering

coffeenazi

New Member
Joined
Aug 17, 2010
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi all, I have a dataset that consists of multiple unique "Activities", that are grouped within a multilevel list like this:


Focus area (Column A)Action (Column B)Deliverable (Column C)Activity (Column D)
1​
1.1​
1.1.11.1.1.1
1​
1.2​
1.2.11.2.1.1
1​
1.2​
1.2.11.2.1.2
2​
2.1​
2.1.12.1.1.1
2​
2.1​
2.1.12.1.1.2
2​
2.1​
2.1.12.1.1.3

The values under each of these heading are very detailed text strings. for example, some of the Actions have over 380 characters in the text string.

I am trying to write a formula that will create a dynamic multilevel list number in Column D, unique for each Activity but nested under that Activity's Deliverable, Action, and Focus area.

All help would be greatly appreciated, and thank you in advance!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
is this what you need?
工作簿1.xlsx
ABCDEF
1Focus area (Column A)Action (Column B)Deliverable (Column C)Activity (Column D)
211.11.1.11.1.1.11.1.1.1TRUE
311.21.2.11.2.1.11.2.1.1TRUE
411.21.2.11.2.1.21.2.1.2TRUE
522.12.1.12.1.1.12.1.1.1TRUE
622.12.1.12.1.1.22.1.1.2TRUE
722.12.1.12.1.1.32.1.1.3TRUE
8
9
10
11
12
Sheet1
Cell Formulas
RangeFormula
E2:E7E2=C2&"."&COUNTIF(C$2:C2,C2)
F2:F7F2=E2=D2
 
Upvote 0
is this what you need?
工作簿1.xlsx
ABCDEF
1Focus area (Column A)Action (Column B)Deliverable (Column C)Activity (Column D)
211.11.1.11.1.1.11.1.1.1TRUE
311.21.2.11.2.1.11.2.1.1TRUE
411.21.2.11.2.1.21.2.1.2TRUE
522.12.1.12.1.1.12.1.1.1TRUE
622.12.1.12.1.1.22.1.1.2TRUE
722.12.1.12.1.1.32.1.1.3TRUE
8
9
10
11
12
Sheet1
Cell Formulas
RangeFormula
E2:E7E2=C2&"."&COUNTIF(C$2:C2,C2)
F2:F7F2=E2=D2
Thank you, no. In my example the numbering was manually created, I need a formula that generates it based on the different text strings at each level.

Here's what the text under each level for one Focus area looks like. I need a formula in Column E that generates a unique ID for each activity, depending on the Deliverable, Action, and Focus area it is linked to.


Focus area (Column A)Action (Column B)Deliverable (Column C)Activity (Column D)Unique Activity ID (Column E)
1. The quick brown fox jumps over the lazy dog.Tomorrow is a brand new day, full of possibilities.With great power comes great responsibility.Let's meet at the café down the street for coffee.1.1.1.1
1. The quick brown fox jumps over the lazy dog.Tomorrow is a brand new day, full of possibilities.With great power comes great responsibility.The majestic mountains stood tall against the clear blue sky.1.1.1.2
1. The quick brown fox jumps over the lazy dog.Tomorrow is a brand new day, full of possibilities.With great power comes great responsibility.Happiness can be found even in the darkest of times.1.1.1.3
1. The quick brown fox jumps over the lazy dog.Life is like a camera. Focus on what's important and capture the good times.In every walk with nature, one receives far more than he seeks.Dreams don't work unless you do.1.2.1.1
1. The quick brown fox jumps over the lazy dog.Life is like a camera. Focus on what's important and capture the good times.In every walk with nature, one receives far more than he seeks.Keep calm and carry on.1.2.1.2
 
Upvote 0
How about:

Book1
ABCDE
1Focus area (Column A)Action (Column B)Deliverable (Column C)Activity (Column D)Unique Activity ID (Column E)
21. The quick brown fox jumps over the lazy dog.Tomorrow is a brand new day, full of possibilities.With great power comes great responsibility.Let's meet at the café down the street for coffee.1.1.1.1
31. The quick brown fox jumps over the lazy dog.Tomorrow is a brand new day, full of possibilities.With great power comes great responsibility.The majestic mountains stood tall against the clear blue sky.1.1.1.2
41. The quick brown fox jumps over the lazy dog.Tomorrow is a brand new day, full of possibilities.With great power comes great responsibility.Happiness can be found even in the darkest of times.1.1.1.3
51. The quick brown fox jumps over the lazy dog.Life is like a camera. Focus on what's important and capture the good times.In every walk with nature, one receives far more than he seeks.Dreams don't work unless you do.1.2.1.1
61. The quick brown fox jumps over the lazy dog.Life is like a camera. Focus on what's important and capture the good times.In every walk with nature, one receives far more than he seeks.Keep calm and carry on.1.2.1.2
Sheet10
Cell Formulas
RangeFormula
E3:E6E3=LET(a,TEXTSPLIT(E2,"."),b,MATCH(TRUE,A2:D2<>A3:D3,0),s,{1,2,3,4},TEXTJOIN(".",1,IFS(s<b,INDEX(a,s),s=b,INDEX(a,s)+1,1,1)))


Also, please update your profile to show what version of Excel you're using. The best answer often depends on what functions you have available.
 
Upvote 0
T
How about:

Book1
ABCDE
1Focus area (Column A)Action (Column B)Deliverable (Column C)Activity (Column D)Unique Activity ID (Column E)
21. The quick brown fox jumps over the lazy dog.Tomorrow is a brand new day, full of possibilities.With great power comes great responsibility.Let's meet at the café down the street for coffee.1.1.1.1
31. The quick brown fox jumps over the lazy dog.Tomorrow is a brand new day, full of possibilities.With great power comes great responsibility.The majestic mountains stood tall against the clear blue sky.1.1.1.2
41. The quick brown fox jumps over the lazy dog.Tomorrow is a brand new day, full of possibilities.With great power comes great responsibility.Happiness can be found even in the darkest of times.1.1.1.3
51. The quick brown fox jumps over the lazy dog.Life is like a camera. Focus on what's important and capture the good times.In every walk with nature, one receives far more than he seeks.Dreams don't work unless you do.1.2.1.1
61. The quick brown fox jumps over the lazy dog.Life is like a camera. Focus on what's important and capture the good times.In every walk with nature, one receives far more than he seeks.Keep calm and carry on.1.2.1.2
Sheet10
Cell Formulas
RangeFormula
E3:E6E3=LET(a,TEXTSPLIT(E2,"."),b,MATCH(TRUE,A2:D2<>A3:D3,0),s,{1,2,3,4},TEXTJOIN(".",1,IFS(s<b,INDEX(a,s),s=b,INDEX(a,s)+1,1,1)))


Also, please update your profile to show what version of Excel you're using. The best answer often depends on what functions you have available.Tha

How about:

Book1
ABCDE
1Focus area (Column A)Action (Column B)Deliverable (Column C)Activity (Column D)Unique Activity ID (Column E)
21. The quick brown fox jumps over the lazy dog.Tomorrow is a brand new day, full of possibilities.With great power comes great responsibility.Let's meet at the café down the street for coffee.1.1.1.1
31. The quick brown fox jumps over the lazy dog.Tomorrow is a brand new day, full of possibilities.With great power comes great responsibility.The majestic mountains stood tall against the clear blue sky.1.1.1.2
41. The quick brown fox jumps over the lazy dog.Tomorrow is a brand new day, full of possibilities.With great power comes great responsibility.Happiness can be found even in the darkest of times.1.1.1.3
51. The quick brown fox jumps over the lazy dog.Life is like a camera. Focus on what's important and capture the good times.In every walk with nature, one receives far more than he seeks.Dreams don't work unless you do.1.2.1.1
61. The quick brown fox jumps over the lazy dog.Life is like a camera. Focus on what's important and capture the good times.In every walk with nature, one receives far more than he seeks.Keep calm and carry on.1.2.1.2
Sheet10
Cell Formulas
RangeFormula
E3:E6E3=LET(a,TEXTSPLIT(E2,"."),b,MATCH(TRUE,A2:D2<>A3:D3,0),s,{1,2,3,4},TEXTJOIN(".",1,IFS(s<b,INDEX(a,s),s=b,INDEX(a,s)+1,1,1)))


Also, please update your profile to show what version of Excel you're using. The best answer often depends on what functions you have available.


Thanks Eric, but I'm getting "0" value with that formula. I've posted a screenshot below, and I'm Office 365. Thank you!!!
1713302172515.png
 
Upvote 0
In the E2 cell, put 1.1.1.1

In the E3 cell, put the formula and drag down. I needed a starting value, although I can incorporate it into the formula if you want.
 
Upvote 0
Four levels:
BOOK1.xlsx
ABCDE
1Lv1Lv2Lv3Lv4
2ABCZ1.1.1.1
3ABCD1.1.1.2
4ABCE1.1.1.3
5ABCF1.1.1.4
6AHIJ1.2.1.1
7AHIK1.2.1.2
8AHUV1.2.2.1
9LMOP2.1.1.1
10LNQS2.2.1.1
11LNRT2.2.2.1
12LNRY2.2.2.2
Sheet1
Cell Formulas
RangeFormula
E2:E12E2=CONCAT(ROWS(UNIQUE(A$2:A2)),"."&MAP(B2:D2,B$2:D$2,A2:C2,LAMBDA(x,y,z,ROWS(UNIQUE(FILTER(y:x,BYROW(A$2:z,CONCAT)=CONCAT(A2:z)))))))


Just need to change the reference to fit more levels, maybe 6 levels, 10 levels:
BOOK1.xlsx
ABCDEF
1Lv1Lv2Lv3Lv4Lv5
2ABCDZ11.1.1.1.1
3ABCDZ21.1.1.1.2
4ABCEZ31.1.1.2.1
5ABCFZ41.1.1.3.1
6AHIJZ51.2.1.1.1
7AHIKZ61.2.1.2.1
8AHUVW1.2.2.1.1
9LMOPZ72.1.1.1.1
10LNQSZ82.2.1.1.1
11LNRTZ92.2.2.1.1
12LNRTZ102.2.2.1.2
Sheet2
Cell Formulas
RangeFormula
F2:F12F2=CONCAT(ROWS(UNIQUE(A$2:A2)),"."&MAP(B2:E2,B$2:E$2,A2:D2,LAMBDA(x,y,z,ROWS(UNIQUE(FILTER(y:x,BYROW(A$2:z,CONCAT)=CONCAT(A2:z)))))))
 
Upvote 0
Thank you, that looks right in the forum example but the formula doesn't work in my sample spreadheet, I am getting the #NAME? error, see screenshot for example. I've tried as an array formula, that doesn't work either. Am I missing something? Thank you so much!

1713395001289.png
 
Upvote 0
i used eta reduced lambda in the formula. try below one:
=CONCAT(ROWS(UNIQUE(A$2:A2)),"."&MAP(B2:E2,B$2:E$2,A2:D2,LAMBDA(x,y,z,ROWS(UNIQUE(FILTER(y:x,BYROW(A$2:z,lambda(m,CONCAT(m)))=CONCAT(A2:z)))))))
 
Upvote 0
i used eta reduced lambda in the formula. try below one:
=CONCAT(ROWS(UNIQUE(A$2:A2)),"."&MAP(B2:E2,B$2:E$2,A2:D2,LAMBDA(x,y,z,ROWS(UNIQUE(FILTER(y:x,BYROW(A$2:z,lambda(m,CONCAT(m)))=CONCAT(A2:z)))))))

ok, I'm trying that new concat formula but I'm getting the below error message. Is there an add-in that is needed for this formula?

1713475463487.png
 
Upvote 0

Forum statistics

Threads
1,223,881
Messages
6,175,159
Members
452,615
Latest member
bogeys2birdies

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