Polyfill for Excel HSTACK and VSTACK array functions

Question

Microsoft has announced many new array functions (https://techcommunity.microsoft.com/t5/excel-blog/announcing-new-text-and-array-functions/ba-p/3186066), including HSTACK and VSTACK, which combine several arrays into one by (unsurprisingly!) "stacking" them horizontally or vertically. The new functions are currently only available for some users in the Beta channel.

I am building a spreadsheet where those functions would be incredibly useful, but it has to be used by other users in the Current channel. I have three "tables" (not Excel tables, but table=shaped grids of data), with the same columns, where each column is an array formula. I would like to consolidate them into a single table, and refer to the entire content as to a single array.

Is it possible to implement some version of HSTACK and VSTACK with other array formulas?

Notes:

I already have access to the other, slightly older, array formulas such as SEQUENCE, FILTER, MAP, SCAN, REDUCE, as well as LAMBDA.
I assume that any replacement formula would be very inefficient compared to a native implementation, but this is not an issue in my case.
In my case, I already know that the arrays will have compatible sizes (e.g. HSTACK a 3 rows x 2 columns and 3 rows x 4 columns arrays), but I don't know the size in advance.
A pure formula would be preferable to a VBA UDF, but the latter could be a good solution too.

here is a udf Vstack: https://stackoverflow.com/questions/68655098/replicate-google-sheets-array1-array2-function-in-microsoft-excel-as-user-def It uses ranges but should be able to be changed to arrays fairly simply. — Scott Craner, Mar 23 '22 at 16:47
`Stacking` and `Shelving` non-contiguous arrays: https://stackoverflow.com/a/71116003/3688861 — Tragamor, Mar 25 '22 at 15:38

score 5 · Answer 1 · answered Mar 23 '22 at 17:22

5

Here is a formula that does the vertical stacking, the ranges can be replaced with arrays, but it only does two and is not as robust as the new formula will be:

=LET(
 rngone,A1:D6,
 rngtwo,E1:I6,
rwone,ROWS(rngone),
mxseq,SEQUENCE(,MAX(COLUMNS(rngone),COLUMNS(rngtwo))),
seq,SEQUENCE(rwone+ROWS(rngtwo)),
IFERROR(CHOOSE((seq>rwone)+1,INDEX(rngone,seq,mxseq),INDEX(rngtwo,seq-rwone,mxseq)),""))

answered Mar 23 '22 at 17:22

Scott Craner

148,073
10
49
81

1

Only distantly related, but I'm guessing [this thread](https://stackoverflow.com/questions/62204826/excel-unique-across-columns) will need updated based on the rollout of new formulas... NVM, Jvdv already has. – BigBen Mar 23 '22 at 17:25
3

I plan on updating both with the new formula when I get home. I have them on my own excel, but not at work. – Scott Craner Mar 23 '22 at 17:26
2

Actually, on your link, jvdv already took care of adding to theirs. So I will skip that one. – Scott Craner Mar 23 '22 at 17:32
1

Yeah, I'm asking that Jvdv's answer be the accepted one. Mine does not deserve to be, as it's rather inferior. – BigBen Mar 23 '22 at 17:32

Polyfill for Excel HSTACK and VSTACK array functions

1 Answers1