1

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.

Improve this question
5
  • So show an example so we can see what you are doing. Commented Mar 23, 2022 at 16:34
  • 1
    Sample data will help people helping you =) Commented Mar 23, 2022 at 16:37
  • 1
    here is a udf Vstack: stackoverflow.com/questions/68655098/… It uses ranges but should be able to be changed to arrays fairly simply. Commented Mar 23, 2022 at 16:47
  • I have explained the problem better Commented Mar 23, 2022 at 21:41
  • 1
    Stacking and Shelving non-contiguous arrays: stackoverflow.com/a/71116003/3688861 Commented Mar 25, 2022 at 15:38

1 Answer 1

Reset to default
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)),""))

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Only distantly related, but I'm guessing this thread will need updated based on the rollout of new formulas... NVM, Jvdv already has.
I plan on updating both with the new formula when I get home. I have them on my own excel, but not at work.
Actually, on your link, jvdv already took care of adding to theirs. So I will skip that one.
Yeah, I'm asking that Jvdv's answer be the accepted one. Mine does not deserve to be, as it's rather inferior.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.