excelexcel-formula

Tiling an Excel array in 2 dimensions, ideally not using VBA


I'm looking to tile an Excel array in 2 dimensions, as defined by another array, ideally not using VBA.

To explain better, what I would like is an excel formula for f(A, B)

Where A is boolean and =

[[1, 0],
 [0, 1]

Where B =

[[1, 2],
 [3, 4]

And f(A, B) tiles B according to bool values in A, in this case, f(A, B) =

[[1, 2, 0, 0],
 [3, 4, 0, 0],
 [0, 0, 1, 2],
 [0, 0, 3, 4]]

My initial thoughts were of =IF(MUNIT(2), {1, 2;3, 4}, {0, 0;0, 0}) but the output does not expand well.


Solution

  • This is my effort so far - hopefully it's fairly general:

    =LET(A,A1:B2,B,D1:E2,DROP(REDUCE(SEQUENCE(1,COLUMNS(A)*COLUMNS(B)),SEQUENCE(ROWS(A)),LAMBDA(rowAcc,row,VSTACK(rowAcc,
       DROP(REDUCE(SEQUENCE(ROWS(B)),SEQUENCE(1,COLUMNS(A)),LAMBDA(colAcc,col,HSTACK(colAcc,B*INDEX(A,row,col)))),,1)))),1))
    

    enter image description here