Generate a table from a "nested list" (do not use VBA)

[tomas saiz]

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		Structure					Structure	Wall	material 1
3			Wall				Structure	Wall	material 2
4				material 1			Structure	Wall	material 3
5				material 2			Structure	Wall	material 4
6				material 3			Structure	Floor	material 5
7				material 4			Structure	Floor	material 6
8							Structure	Ceiling	material 7
9			Floor				Electronics	Sensors	Sensor 1
10				material 5			Electronics	Sensors	Sensor 2
11				material 6			Electronics	Cables	Cable 1
12							Electronics	Cables	Cable 2
13			Ceiling				Electronics	Cables	Cable 3
14				material 7
15
16		Electronics
17			Sensors
18				Sensor 1
19				Sensor 2
20
21			Cables
22				Cable 1
23				Cable 2
24				Cable 3
25
Sheet1

I want to use the data on the "nested list" on columns B, C and D, and use some clever formulas to automatically generate the table on columns G, H, I. What do you suggest?

(I know that "nested list" is not right term, but I could not think anything better)

 

[KRice]

Here is one idea:

MrExcel_20240311.xlsx
	A	B	C	D	E	F	G	H
1
2		Structure				Structure	Wall	material 1
3			Wall			Structure	Wall	material 2
4				material 1		Structure	Wall	material 3
5				material 2		Structure	Wall	material 4
6				material 3		Structure	Floor	material 5
7				material 4		Structure	Floor	material 6
8						Structure	Ceiling	material 7
9			Floor			Electronics	Sensors	Sensor 1
10				material 5		Electronics	Sensors	Sensor 2
11				material 6		Electronics	Cables	Cable 1
12						Electronics	Cables	Cable 2
13			Ceiling			Electronics	Cables	Cable 3
14				material 7
15
16		Electronics
17			Sensors
18				Sensor 1
19				Sensor 2
20
21			Cables
22				Cable 1
23				Cable 2
24				Cable 3
Sheet8

Cell Formulas
Range	Formula
F2:H13	F2	=LET(b,B2:B24,c,C2:C24,d,D2:D24, bf,LOOKUP(SEQUENCE(ROWS(b)),FILTER(SEQUENCE(ROWS(b)),b<>""),FILTER(b,b<>"")), cf,LOOKUP(SEQUENCE(ROWS(c)),FILTER(SEQUENCE(ROWS(c)),c<>""),FILTER(c,c<>"")), fdwn,HSTACK(bf,cf,d), FILTER(fdwn,INDEX(fdwn,,3)<>0))
Dynamic array formulas.

 

[JEC]

Here one more

=LET(t,HSTACK(B2:B23,DROP(C2:C24,1),DROP(D2:D25,2)),TRANSPOSE(SCAN("",TRANSPOSE(FILTER(t,INDEX(t,,3)<>0)),LAMBDA(a,b,IF(b<>0,b,a)))))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2		Structure				Structure	Wall	material 1
3			Wall			Structure	Wall	material 2
4				material 1		Structure	Wall	material 3
5				material 2		Structure	Wall	material 4
6				material 3		Structure	Floor	material 5
7				material 4		Structure	Floor	material 6
8						Structure	Ceiling	material 7
9			Floor			Electronics	Sensors	Sensor 1
10				material 5		Electronics	Sensors	Sensor 2
11				material 6		Electronics	Cables	Cable 1
12						Electronics	Cables	Cable 2
13			Ceiling			Electronics	Cables	Cable 3
14				material 7
15
16		Electronics
17			Sensors
18				Sensor 1
19				Sensor 2
20
21			Cables
22				Cable 1
23				Cable 2
24				Cable 3
25
Sheet6

Cell Formulas
Range	Formula
F2:H13	F2	=LET(x,SCAN(,B2:B100,LAMBDA(a,b,IF(b="",a,b))),y,SCAN(,C2:C100,LAMBDA(a,b,IF(b="",a,b))),z,HSTACK(x,y,D2:D100),FILTER(z,INDEX(z,,3)<>""))
Dynamic array formulas.