formula to generate multilevel list numbering

[coffeenazi]

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.1	1.1.1.1
1​	1.2​	1.2.1	1.2.1.1
1​	1.2​	1.2.1	1.2.1.2
2​	2.1​	2.1.1	2.1.1.1
2​	2.1​	2.1.1	2.1.1.2
2​	2.1​	2.1.1	2.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!

 

[shaowu459]

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?

View attachment 110171

sorry, that is for five levels. try this one.

=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,lambda(m,CONCAT(m)))=CONCAT(A2:z)))))))

 

[coffeenazi]

getting closer, now the result from that formula is TRUE

 

[shaowu459]

View attachment 110176
getting closer, now the result from that formula is TRUE

copy below formula and paste in E2, don't just update part of exsiting formula.

=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,lambda(m,CONCAT(m)))=CONCAT(A2:z)))))))

 

[Eric W]

I incorporated the starting row value in the formula. You can put this formula in E2 and drag down. Just make sure that you don't have a period in the E1 heading.

Book1
	A	B	C	D	E
1	Focus area (Column A)	Action (Column B)	Deliverable (Column C)	Activity (Column D)	Unique Activity ID (Column E)
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.	Let's meet at the café down the street for coffee.	1.1.1.1
3	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
4	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
5	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
6	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
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=IF(COUNTIF(E1,"*.*")=0,"1.1.1.1",LET(a,TEXTSPLIT(E1,"."),b,MATCH(TRUE,A1:D1<>A2:D2,0),s,{1,2,3,4},TEXTJOIN(".",1,IFS(s<b,INDEX(a,s),s=b,INDEX(a,s)+1,1,1))))

 

[coffeenazi]

T

I incorporated the starting row value in the formula. You can put this formula in E2 and drag down. Just make sure that you don't have a period in the E1 heading.

Book1
	A	B	C	D	E
1	Focus area (Column A)	Action (Column B)	Deliverable (Column C)	Activity (Column D)	Unique Activity ID (Column E)
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.	Let's meet at the café down the street for coffee.	1.1.1.1
3	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
4	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
5	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
6	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
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=IF(COUNTIF(E1,"*.*")=0,"1.1.1.1",LET(a,TEXTSPLIT(E1,"."),b,MATCH(TRUE,A1:D1<>A2:D2,0),s,{1,2,3,4},TEXTJOIN(".",1,IFS(s<b,INDEX(a,s),s=b,INDEX(a,s)+1,1,1))))

That's got it! Thank you!