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?**

Hi Charles,

Another way to stack/append the ranges.

Ranges with numeric cells and/or text cells

Suppoose in A1:C3 named Range1

Name Test1 Test2

Anthony 2 3

Mary 5 6

in F4:G6 named Range2

John 70

Mike 80

Peter 90

In I2 (identical to TotRows)

=ROWS(Range1)+ROWS(Range2)

in K2 (identical to TotCols)

=MAX(COLUMNS(Range1),COLUMNS(Range2))

Select, say, A10:A15 and enter this array formula

=IFERROR(IF(ROW(INDIRECT(“1:”&$I$2))>ROWS(Range1),INDEX(Range2,ROW(INDIRECT(“1:”&$I$2))-ROWS(Range1),COLUMN(INDIRECT(“1:”&$K$2))),INDEX(Range1,ROW(INDIRECT(“1:”&$I$2)),COLUMN(INDIRECT(“1:”&$K$2)))),””)

Ctrl+Shift+Enter

Greetings

Marcelo Branco

oops….

Select A10:C15 (not A10:A15)

and enter the array formula

The result is

Name Test1 Test2

Anthony 2 3

Mary 5 6

John 70

Mike 80

Peter 90

Not sure if the table above is properly formatted

Regards

Marcelo Branco

That’s a cool trick! (But gets a bit messy the more things you want to stack, and its volatile …)

‘Unfortunately this method only works with either single columns or single rows: it does not handle 2 dimensional ranges.’ Good tip, thanks a lot for sharing this. I needed it

Is there a way to merge two array constants into a single row… so for example i have {1,2,3,4,5}, and i want an array constant which is {1,2,3,4,5,10,20,30}… is there a way to do?

sorry i meant if i have {1,2,3,4,5} and i have {10,20,30} and i want to combine it to {1,2,3,4,5,10,20,30} is there a way to do it?