Excel HSTACK multiple arrays where an array may be empty

Question

I want to abut multiple dynamically sized tables to each other where any one of the tables may be empty.

For example, say I have the following:

where A3 has the following formula:

=LET(
  a, MAKEARRAY(5, A1, LAMBDA(x,y, "hello")),
  b, MAKEARRAY(5, B1, LAMBDA(x,y, "world")),
  c, MAKEARRAY(5, C1, LAMBDA(x,y, "!")),
  final, HSTACK(a,b,c),
  final
)

What I want is to produce the following, if say, B1 is set to 0:

My attempt starts here

If any of A1, B1, or C1 are set to 0, the MAKEARRAY function results in an #VALUE! error instead of elegantly producing a 0x0 array that HSTACK could ignore...

I tried to make up for this by using IFERROR and creating an empty column when a zero was used. Like so:

=LET(
  a, MAKEARRAY(5, A1, LAMBDA(x,y, "hello")),
  b, MAKEARRAY(5, B1, LAMBDA(x,y, "world")),
  c, MAKEARRAY(5, C1, LAMBDA(x,y, "!")),
  err, MAKEARRAY(5, 1, LAMBDA(x,y, "")),
  final, HSTACK(IFERROR(a, err), IFERROR(b, err), IFERROR(c, err)),
  final
)

which produces produces this:

To get rid of the empty column, I tried this using a BYCOL to create a mask and then FILTER:

=LET(
  a, MAKEARRAY(5, A1, LAMBDA(x,y, "hello")),
  b, MAKEARRAY(5, B1, LAMBDA(x,y, "world")),
  c, MAKEARRAY(5, C1, LAMBDA(x,y, "!")),
  err, MAKEARRAY(5, 1, LAMBDA(x,y, "")),
  stack, HSTACK(IFERROR(a, err), IFERROR(b, err), IFERROR(c, err)),
  col_mask, BYCOL(stack, LAMBDA(col, COUNTBLANK(col)<>5)),
  final, FILTER(stack, col_mask)
  final)

which produces #CALC!. I think this is due to the BYCOL(stack,...) function call.

I think Excel can't handle array of arrays because if I split the BYCOL and FILTER into separate cells, this actually works. For example,

where A10 looks like:

=LET(
  col_mask, BYCOL(A3#, LAMBDA(col, COUNTBLANK(col)<>5)),
  final, FILTER(A3#, col_mask),
  final
)

Is it possible to do this in a single cell without VBA?

Answer 1

This is the quick fix to your existing formula:

---

• Formula used in cell A3

=LET(
     a, MAKEARRAY(5, A1, LAMBDA(x,y, "hello")),
     b, MAKEARRAY(5, B1, LAMBDA(x,y, "world")),
     c, MAKEARRAY(5, C1, LAMBDA(x,y, "!")),
     err, MAKEARRAY(5, 1, LAMBDA(x,y, "")),
     stack, HSTACK(IFERROR(a, err), IFERROR(b, err), IFERROR(c, err)),
     col_mask, BYCOL(stack,LAMBDA(α, AND(N(α<>"")))),
     final, FILTER(stack,col_mask),
     final)

---

Alternatively, could use the following as well:

=LET(
     ƒx, LAMBDA(α,δ,φ, IFERROR(MAKEARRAY(α, δ, LAMBDA(x,y, φ)),EXPAND("",α,,""))),
     stack, HSTACK(ƒx(5, A1, "Hello"),ƒx(5, B1, "World"),ƒx(5, C1, "!")),
     col_mask, BYCOL(stack, LAMBDA(ε, AND(N(ε<>"")))),
     final, FILTER(stack,col_mask),
     final)

---

The alternative usage of BYCOL() with MMULT()

MMULT(SEQUENCE(,ROWS(stack)),N(stack<>""))

---

MAKEARRAY() can be excluded as well:

=LET(
     ƒx, LAMBDA(α,δ,φ, IFERROR(IF(SEQUENCE(α,δ)^0,φ),EXPAND("",α,,""))),
     stack, HSTACK(ƒx(5,A1,"Hellow"),ƒx(5,B1,"World"),ƒx(5,C1,"!")),
     col_max, BYCOL(N(stack<>""),AND),
     final, FILTER(stack,col_max),
     final)

Answer 2

My proposal is to use a formula like this:

=CHOOSEROWS(CHOOSECOLS($A$1:$C$1,
--TEXTSPLIT(CONCAT(REPT(SEQUENCE(,COLUMNS($A$2:$C$2))&",",
$A$2:$C$2)),",",,TRUE)), SEQUENCE($E$2,,1,0))