While creating the solution for "How to Merge Rows values into a Column on a prior Row?" I composed the formula:
[G11] =LET(
c,COLUMNS(Table1),
at_l,INDEX(Table1,0,c),
n,ROWS(Table1),
af,FILTER(HSTACK(Table1,SEQUENCE(n)),Table1[Product]<>"null"),
aw,DROP(VSTACK(INDEX(af,0,c+1),n+1),1),
HSTACK(
TAKE(af,,c),
BYROW(
SEQUENCE(ROWS(af)),
LAMBDA(i,
TEXTJOIN(", ",FALSE,
DROP(TAKE(at_l,INDEX(aw,i)-1),INDEX(af,i,c+1))
)
)
)
)
)
Manually checking it has no error, but it doesn't work as depicted below:
The data table:
| # | QTY | Product | Option | Item Name |
|---|---|---|---|---|
| 1 | 3 | CHR124 | Chair | |
| null | null | null | .FOC | Fog |
| 2 | 1 | SFA478 | Sofa | |
| null | null | null | .A | A |
| null | null | null | .B | B |
| null | null | null | .C | C |
| null | null | null | .D | D |
| null | null | null | .E | E |
| null | null | null | .F | F |
| null | null | null | .G | G |
| null | null | null | .H | H |
| 3 | 2 | TBL8954 | Table | |
| null | null | null | .I | I |
| null | null | null | .J | J |
| null | null | null | .K | K |
| null | null | null | .L | L |
| null | null | null | .M | M |
| null | null | null | .N | N |
| null | null | null | .O | O |
| null | null | null | .P | P |
To debug the issue, I composed the intermediate formula:
[G15] =LET(
c,COLUMNS(Table1),
at_l,INDEX(Table1,0,c),
n,ROWS(Table1),
af,FILTER(HSTACK(Table1,SEQUENCE(n)),Table1[Product]<>"null"),
aw,DROP(VSTACK(INDEX(af,0,c+1),n+1),1),
HSTACK(
af,
aw
)
)
and then
[G19] =BYROW(
SEQUENCE(ROWS(G15:G17)),
LAMBDA(i,
TEXTJOIN(", ",FALSE,
DROP(TAKE(Table1[Item Name],INDEX(M15:M17,i)-1),INDEX(G15:L17,i,6))
)
)
)
which is also doesn't work.
But it can work with small modification:
[I19] =SCAN("",
SEQUENCE(ROWS(G15:G17)),
LAMBDA(a,i,
TEXTJOIN(", ",FALSE,
DROP(TAKE(Table1[Item Name],INDEX(M15:M17,i)-1),INDEX(G15:L17,i,6))
)
)
)
But the main formula with this modification still doesn't work:
=LET(
c,COLUMNS(Table1),
at_l,INDEX(Table1,0,c),
n,ROWS(Table1),
af,FILTER(HSTACK(Table1,SEQUENCE(n)),Table1[Product]<>"null"),
aw,DROP(VSTACK(INDEX(af,0,c+1),n+1),1),
HSTACK(
TAKE(af,,c),
SCAN("",
SEQUENCE(ROWS(af)),
LAMBDA(a,i,
TEXTJOIN(", ",FALSE,
DROP(TAKE(at_l,INDEX(aw,i)-1),INDEX(af,i,c+1))
)
)
)
)
)
I did more debug modifications but get nothing finally. Even Excel crashed when I replaced TEXTJOIN by REDUCE.
I found the solution for the question, but the issue described above still irritate me.
It smells as an Excel bug but can anybody comment it or provide an explanation?
=LET( c,COLUMNS(Table1), at_l,INDEX(Table1,0,c), n,ROWS(Table1), af,FILTER(HSTACK(Table1,SEQUENCE(n)),Table1[Product]<>"null"), aw,DROP(VSTACK(INDEX(af,0,c+1),n+1),1), HSTACK( TAKE(af,,c), BYROW( SEQUENCE(ROWS(af)), LAMBDA(i, TEXTJOIN(", ",FALSE, DROP(TAKE(at_l,@INDEX(aw,i)-1),@INDEX(af,i,c+1)))))))