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]

is this what you need?

工作簿1.xlsx
	A	B	C	D	E	F
1	Focus area (Column A)	Action (Column B)	Deliverable (Column C)	Activity (Column D)
2	1	1.1	1.1.1	1.1.1.1	1.1.1.1	TRUE
3	1	1.2	1.2.1	1.2.1.1	1.2.1.1	TRUE
4	1	1.2	1.2.1	1.2.1.2	1.2.1.2	TRUE
5	2	2.1	2.1.1	2.1.1.1	2.1.1.1	TRUE
6	2	2.1	2.1.1	2.1.1.2	2.1.1.2	TRUE
7	2	2.1	2.1.1	2.1.1.3	2.1.1.3	TRUE
8
9
10
11
12
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=C2&"."&COUNTIF(C$2:C2,C2)
F2:F7	F2	=E2=D2

 

[coffeenazi]

is this what you need?

工作簿1.xlsx
	A	B	C	D	E	F
1	Focus area (Column A)	Action (Column B)	Deliverable (Column C)	Activity (Column D)
2	1	1.1	1.1.1	1.1.1.1	1.1.1.1	TRUE
3	1	1.2	1.2.1	1.2.1.1	1.2.1.1	TRUE
4	1	1.2	1.2.1	1.2.1.2	1.2.1.2	TRUE
5	2	2.1	2.1.1	2.1.1.1	2.1.1.1	TRUE
6	2	2.1	2.1.1	2.1.1.2	2.1.1.2	TRUE
7	2	2.1	2.1.1	2.1.1.3	2.1.1.3	TRUE
8
9
10
11
12
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=C2&"."&COUNTIF(C$2:C2,C2)
F2:F7	F2	=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

 

[Eric W]

How about:

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
Sheet10

Cell Formulas
Range	Formula
E3:E6	E3	=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.

 

[coffeenazi]

T

How about:

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
Sheet10

Cell Formulas
Range	Formula
E3:E6	E3	=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
	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
Sheet10

Cell Formulas
Range	Formula
E3:E6	E3	=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!!!

 

[Eric W]

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.

 

[shaowu459]

Four levels:

BOOK1.xlsx
	A	B	C	D	E
1	Lv1	Lv2	Lv3	Lv4
2	A	B	C	Z	1.1.1.1
3	A	B	C	D	1.1.1.2
4	A	B	C	E	1.1.1.3
5	A	B	C	F	1.1.1.4
6	A	H	I	J	1.2.1.1
7	A	H	I	K	1.2.1.2
8	A	H	U	V	1.2.2.1
9	L	M	O	P	2.1.1.1
10	L	N	Q	S	2.2.1.1
11	L	N	R	T	2.2.2.1
12	L	N	R	Y	2.2.2.2
Sheet1

Cell Formulas
Range	Formula
E2:E12	E2	=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
	A	B	C	D	E	F
1	Lv1	Lv2	Lv3	Lv4	Lv5
2	A	B	C	D	Z1	1.1.1.1.1
3	A	B	C	D	Z2	1.1.1.1.2
4	A	B	C	E	Z3	1.1.1.2.1
5	A	B	C	F	Z4	1.1.1.3.1
6	A	H	I	J	Z5	1.2.1.1.1
7	A	H	I	K	Z6	1.2.1.2.1
8	A	H	U	V	W	1.2.2.1.1
9	L	M	O	P	Z7	2.1.1.1.1
10	L	N	Q	S	Z8	2.2.1.1.1
11	L	N	R	T	Z9	2.2.2.1.1
12	L	N	R	T	Z10	2.2.2.1.2
Sheet2

Cell Formulas
Range	Formula
F2:F12	F2	=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)))))))

 

[coffeenazi]

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!

 

[shaowu459]

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)))))))

 

[coffeenazi]

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?