Stacking (Appending) Arrays/Ranges in Excel: Formulas versus Functions Shootout

This post is a follow up to my last post Using Constant Arrays and Expressions in Excel Formulas.
I will explore how some of the general purpose array-handling functions in FastExcel SpeedTools Extras compare with formulas using native Excel functions.
I asked Sam (sgbhide@gmail.com who frequently comments on these blog posts) to collaborate by creating the formulas using native Excel functions. He has done a great job!

Why would you want to stack/append Arrays/Ranges anyway?

A few functions allow you to input multiple arguments that effectively get stacked together. For example this formula works well:

=SUM(Sheet1!$A$1:$B$10, Sheet2!$Y$43:$Z$99)

But if you need to do a Lookup across multiple sheets this formula does not work:

=VLOOKUP($C$32,(Sheet1!$A$1:$B$10, Sheet2!$Y$43:$Z$99),2,False)

And you cannot use SUMIF like this:

=SUMIF((A2:A5,D7:D10),”<3″,(B2:B5,E7:E10))

Similarly if you are using a separate worksheet for each time period (week, month …) you often want to stack the time periods alongside one another.

In many cases there are ways of handling these situations using standard Excel Formulas, but often these methods are slow or complex.

Creating arrays containing a mixture of Array Constants and Ranges, stacking row-wise or column-wise.

As the last post showed you cannot put range references directly between the { … } of an array constant.
But Sam has some cunning formulas using the array version of CHOOSE:

Stacking Rows:

Stacking Columns:

The first argument for CHOOSE indicates which of the following arguments to return. But if you give an array as the first argument all the value choices are returned.
If you want to stack rows above one another you use a semi-colon (;) separator in the first CHOOSE argument, and if you want to stack columns alongside one another you use a comma (,), (if you use the wrong separator CHOOSE returns weird answers).

Note: you have to enter these formulas as multi-cell array formulas by selecting multiple cells, typing the formula and then pressing Control+Shift+Enter.

Unfortunately this method only works with either single columns or single rows: it does not handle 2 dimensional ranges.

So Sam came up with this method of handling 2-D ranges instead (but I have not got space to explain it!):

Suppose we have 2 ranges with defined names of Arry1 and Arry2:

And we have some more defined names containing formulas like this:

Arry1	=Sheet1!$D$6:$E$7
Arry2	=Sheet1!$D$10:$F$13
PadArry1	=(–(ROW(INDIRECT(“1:”&TotRows))*IF(COLUMN(INDIRECT(“RC1:RC”&TotCols,0)),1)<=rRows))
PadArry2	=(–(ROW(INDIRECT(“1:”&TotRows))*IF(COLUMN(INDIRECT(“RC1:RC”&TotCols)),1)>rRows))
rRows	=ROWS(Arry1)
TotCols	=MAX(COLUMNS(Arry1),COLUMNS(Arry2))
TotRows	=ROWS(Arry1)+ROWS(Arry2)

Then this formula, entered as a multi-cell array formula into 6 rows by 3 columns:

{=PadArry1*OFFSET(Arry1,,,TotRows,TotCols)+PadArry2*OFFSET(Arry2,-rRows,0,TotRows)}

Produces this:

Notice that Sam’s cunning set of formulas has padded the gaps in the top 2 rows of column 3 with zeros, rather than leaving them as #N/A.
This approach only works when all the cells in the ranges are numeric because Sam has used + to add the numbers together.

If some of the columns in the ranges are alphabetic and some are numeric life gets a lot more difficult.

Using FastExcel SpeedTools ROW.ARRAY and COL.ARRAY Functions

Well Sam’s formulas are difficult for many users so I developed some FastExcel SpeedTools array functions to make life easier.
To stack ranges below one another and pad out the gaps use ROW.ARRAY. The equivalent to Sam’s set of formulas is

{=ROW.ARRAY(0,Arry1,Arry2)}

The first argument (0 in this case) dictates what to use to pad out missing values.
To do a lookup on both these arrays use

=VLOOKUP(95,ROW.ARRAY(0,Arry1,Arry2),3,False) returns 7 (this does not need to be array entered).

You can use COL.ARRAY in a similar way to stack ranges alongside one another.

Note: ROW.ARRAY and COL.ARRAY are limited in total to the Excel row and column limits.

More FastExcel V3 Array functions

The FastExcel Arrays functions include:

• ROW.ARRAY
• COL.ARRAY
• VECTOR
• PAD.ARRAY
• REVERSE.ARRAY

Conclusion

It is useful to have a set of efficient functions to stack/append ranges and arrays.

Challenge: can you provide more efficient methods using standard Excel formulas?

Advertisement