I need a dynamic nested list based on items and subitems.
For example, if I have a list of items
| A | B |
|---|---|
| 1 | Item 1 |
| 2 | Item 2 |
| 3 | Item 3 |
And a list of subitems
| C | D |
|---|---|
| Subitem 1 | Item 1 |
| Subitem 2 | Item 1 |
| Subitem 3 | Item 2 |
| Subitem 4 | Item 3 |
| Subitem 5 | Item 3 |
My result should be:
| E |
|---|
| Item 1 |
| Subitem 1 |
| Subitem 2 |
| Item 2 |
| Subitem 3 |
| Item 3 |
| Subitem 4 |
| Subitem 5 |
So far, the only idea that I could came up with was
=TRANSPOSE(
SPLIT(JOIN(";",
MAP(
UNIQUE(FILTER($B2:$B,$B2:$B<>"")), LAMBDA(x, FLATTEN(x & ";" & JOIN(";",IFERROR(FILTER($C2:$C,$D2:$D=x),)))))
),";")
)
But I wonder if there is a better way of doing this. Any ideas?
You can use REDUCE. Enter the following formula in E1:
=REDUCE("E",TOCOL(B2:B,1),LAMBDA(a,c,{a;c;FILTER(C:C,D:D=c)}))
If you don't want the header, use:
=REDUCE(TOCOL(,1),TOCOL(B2:B,1),LAMBDA(a,c,{a;c;FILTER(C:C,D:D=c)}))