google-sheetslambdagoogle-sheets-formula

Creating Nested List with Google Sheet Formulas from Pre-Made Tables

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?

Solution

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