excelexcel-formulalambda

Creating Dynamic Nested List with Excel Formula from Pre-Made Tables (on 3 levels)

I'm trying to adapt a three-level-nested-list formula from Google Sheet to Excel.

For some reason, this is harder than expected.

No matter what I do, I get an #CALC! error. One thing I had to adapt was the empty TOCOL(;1), since that yielded #VALUE!. I also had to include an error message for the IFNA() function.

Currently, this is how the formula looks like:

=TOCOL(REDUCE(TOCOL("";1);TOCOL(Macroetapas!B2:B1000;1); LAMBDA(a;item;VSTACK(a;item; REDUCE(TOCOL("";1); IFNA(FILTER(Macroatividades!B2:B1000;Macroatividades!C2:C1000=item);"N/A error"); LAMBDA(b;subitem; VSTACK(b;subitem;IFNA(FILTER(SCRUM!B2:B1000;SCRUM!J2:J1000=subitem);"N/A error")))))));1)

For context, here is how my Spreadsheet is organized:

I have a list of items

Macroetapas!A2:A1000 Macroetapas!B2:B1000
1 Item 1
2 Item 2
3 Item 3

A list of subitems

Macroatividades!B2:B1000 Macroatividades!C2:C1000
Subitem 1 Item 1
Subitem 2 Item 1
Subitem 3 Item 2
Subitem 4 Item 3
Subitem 5 Item 3

And a list of sub-subitems

SCRUM!B2:B1000 SCRUM!J2:J1000
Sub-subitem a Subitem 1
Sub-subitem b Subitem 1
Sub-subitem c Subitem 3
Sub-subitem d Subitem 3
Sub-subitem e Subitem 5

My result should be:

Result
Item 1
Subitem 1
Sub-subitem a
Sub-subitem b
Subitem 2
Item 2
Subitem 3
Sub-subitem c
Sub-subitem d
Item 3
Subitem 4
Subitem 5
Sub-subitem e
Solution

  • Here is the formula which works in Excel:

    =LET(arr;REDUCE("";TOCOL(B2:B1000;1);LAMBDA(a;item;VSTACK(a;item;
    REDUCE("";FILTER(C2:C1000;D2:D1000=item;"");LAMBDA(b;subitem;
      VSTACK(b;subitem;FILTER(E2:E1000;F2:F1000=subitem;"")))))));FILTER(arr;arr<>""))

    The shorter formula

    =LET(lam;LAMBDA(x;y;z;BYROW(x;LAMBDA(w;TEXTJOIN(";";TRUE;w;FILTER(y;z=w;"")))));
TEXTSPLIT(TEXTJOIN(";";TRUE;lam(B2:B100;lam(C2:C100;E2:E100;F2:F100);D2:D100));;";"))