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 |
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));;";"))
I think a different approach may be more efficient:
=LET(c,CHOOSECOLS,
f,FILTER(Macroatividades!B2:C1000,ISNUMBER(XMATCH(Macroatividades!C2:C1000,TO COL(Macroetapas!B2:B1000,1)))),
UNIQUE(
TOCOL(HSTACK(c(f,2,1),
IFS(TOROW(SCRUM!J2:J1000,1)=c(f,1),
TOROW(SCRUM!B2:B1000,1))),
2)))
This first filters the column B and C values from Macroatividades that match the items from Macroetapas, next it stacks that with an array of the sub-sub items matching the sub items from the filter result if matched or an #N/A error if no match.
If this stacked arrays are wrapped in TOCOL skipping error values you get your result, but the items are repeated prior to each sub item. Therefore this is wrapped in unique, to only list the first occurrence.
Caveat this requires the sub sub and sub items to be unique. Else unique filters out nth occurrences.