Merging arrays

If you work with a table in which there are several arrays, such as city names or sales values in them, until now you could only combine them by copying and pasting to the desired position in the worksheet. As of a few weeks ago, if you are a Microsoft 365 subscriber, you can also use two functions for working with dynamic arrays: VSTACK and HSTACK. They allow merging arrays which contributes to easier preparation of data for reporting.

The VSTACK function has the following syntax:

VSTACK(<array 1>,[<array 2>],…)

It is used when we want to combine strings by placing them in the same column, i.e. vertically. For example, if you have a sales report in which, by column, the sales values for three different countries are given separately, using this function you can combine all the values in one column. For example. if we want to combine all the names of the cities in one column, we need to write the formula:

=VSTACK(A3:A8,C3:C7,E3:E6)

The HSTACK function is used to combine data by rows – horizontally. Its syntax is:

HSTACK(<array 1>;[<array 2>],…)

If you enter the formula:

=HSTACK(A3,C3,E3)

You will combine the names of the first three cities from the table in order.