In a recent post, we looked at how to pull data from several sheets and combine it into vertically stacked data using the VSTACK function. Here's the tutorial: VSTACK to Combine Multiple Sheets.
You might have noticed that when we stacked the data, we only pulled data through row 9 from each tab because that's how long our data set was on the first tab.
But what if some of your tabs have longer data sets? The obvious answer is to pull as many rows as your longest data set. When you do that, however, you'll notice some zeros caused by the blank rows being pulled into the stack.
How can we remove these blank rows automatically?
By using the FILTER function.
How to Write the Formula
Currently, our existing formula looks like this:
=VSTACK(‘Division 1:Division 4'!A2:D12)
We're going to wrap this formula in FILTER, using the existing VSTACK formula as the array argument, and then also using it as the include argument, with one small change. We'll change D12 to A12.
To complete the FILTER formula, we'll end it by setting it not equal to zero (<>0).
The final formula looks like this:
=FILTER(VSTACK(‘Division 1:Division 4'!A2:D12),VSTACK(‘Division 1:Division 4'!A2:A12)<>0)
That removes all of the zero rows from the stacked data.
If you didn't catch my recent tutorial for adding headers to your stack, you can check it out here: Including Headers in VSTACK Data.
I hope these posts about VSTACK have been helpful to you. Next time, we'll take a look at HSTACK. If the V stands for Vertical, I bet you can figure out the H!
Leave a comment if you have a question about this post, or about VSTACK in general.