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