Search code examples
excelexcel-365

Excel-365 functions don't work as expected

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:

enter image description here

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?

Solution 
  • =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)))))))

    Adding intersection @ to the INDEX tells Excel the array is a single value (even if it is).