Welcome to Excel Jet Consult blog post. In this blog post, I will show us how to combine the VSTACK and the TAKE Functions to stack data together. Let’s get started.
SAMPLE DATA
The screenshot below shows the transaction data with a handful of columns
VSTACK FUNCTION
The VSTACK Function appends arrays vertically and in sequence to return a larger array.
Syntax
=VSTACK(array1,[array2],…)
The VSTACK function syntax has the following argument:
array The arrays to append.
TAKE FUNCTION
The new Excel TAKE FUNCTION returns a specified number of contiguous rows or columns from the start or end of an array.
Syntax
=TAKE(array, rows,[columns])
The TAKE function syntax has the following arguments:
- array The array from which to take rows or columns.
- rows The number of rows to take. A negative value takes from the end of the array.
- columns The number of columns to take. A negative value takes from the end of the array.
COMBINE THE VSTACK & TAKE FUNCTIONS
We want to return the top3 and bottom3 rows from the dataset and append them using the VSTACK. In addition, we want to include the row headers in the VSTACK array operation
To do that, in cell H1, execute the formula: =VSTACK(A1:F1,TAKE(A2:F22,3),TAKE(A2:F22,3))
Click Enter. The formula will spill to the columns to the right and the cells below without the need to use the fill handle
Based on the above formula, A1:F1 is the cell reference for the VSTACK array1 which returns the the row headers TAKE(A2:F22,3) is the cell reference that returnsa the first 3 rows from the dataset while TAKE(A2:F22,3) is the cell reference that returns the last 3 rows from the dataset
From the screenshot below, it can be seen that we have the headers as well as the top 3 and bottom 3 rows returned
See you in the next video