Here, we will learn how to use the Excel dynamic array function: VSTACK. Later, we will also learn how to combine and sort unique arrays vertically using VSTACK and the IFNA, SORT and UNIQUE functions.
VSTACK function
The Excel VSTACK function combines arrays vertically into a single array, appending each array to the bottom of the previous array. The result of the function is always a single vertical array that spills into the multiple cells automatically.
VSTACK takes array arguments: array1, [array2],... The first required argument array1 will take the range to combine. Thereafter, it will take the subsequent optional additional ranges to be combined vertically.
Let's understand a basic example. You have three sets of data (shown in the image) as "Data1", "Data2" and "Data3". You create a helper column "Result" in column D to combine all these three sets of data vertically. Use VSTACK like this: =VSTACK(A3:A4,B3:B4,C3).
Combining and sorting unique arrays vertically using VSTACK with IFNA, SORT and UNIQUE
To combine and sort unique arrays vertically, use the formula based on VSTACK, IFNA, SORT and UNIQUE functions.
The dataset (shown in the image) contains the data about customer orders that is separated into two ranges A13:D19 and A22:C27. The goal is to combine these two ranges vertically using VSTACK. However, note that both the ranges are of different size and combining both of them will force VSTACK to expand the smaller array to match the size with the larger array and will fill the new cells with the #N/A error. To remove this error, nest the VSTACK formula inside the IFNA function. Furthermore, we want the combined range to be sorted by the second column "Order No." in ascending order and want only unique records.
The formula in F13 is:
=UNIQUE(SORT(IFNA(VSTACK(A13:D19,A22:C27),"-"),2,1))
How this formula works
VSTACK combines both the ranges A13:D19 and A22:C27 vertically into a single array, spilling the results into the multiple cells automatically in the range F13:I24. VSTACK expands the smaller range to match the size with the larger range and fills the new cells with the #N/A error. IFNA is configured to replace the #N/A error with the dash "-", like this: IFNA(VSTACK(...),"-"). SORT is configured to sort the result range by the second column "Order No." in ascending order, like this: SORT((VSTACK(...)),2,1). Finally, UNIQUE returns unique records in the result range.
You have successfully learnt how to use the Excel dynamic array function: VSTACK, and how to combine and sort unique arrays vertically using VSTACK and the IFNA, SORT and UNIQUE functions! I hope this post helped you.
