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.
Video Tutorial
Watch on YouTube & Subscribe to our Channel
Downloads
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.
Related Post
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.
Hi Jon
I used VStack for the first time this week to consolidate data from other workbooks into one single book. It worked perfectly, however, when I open the consolidated data workbook, I don’t see my data, it show a REF error. I have to go and open the other books it’s linked to in order to see my data. I obviously don’t want to have them all open – just the consolidation. Why is this happening? Thanks, Louise
Another suggestion I would throw in is for users to manage their data using Excel tables. By nature tables create a dynamic named range.
In the example if the data on the sheets were in tables, you would simply stack the tables. =VSTACK(Table1, Table2, Table3, Table4).
Many roads to get there, thanks for the tip!
Readers could also explore using other criteria to get a filtered data stack from multiple source data sets (like sales over $100,000) using the AND (to keep the 0) and VSTACK functions nested inside a filter function.
Hi, great tip.
Wouldn’t it also be good to use the LET function for running VSTACK once, and clarity. So, instead of (just a dummy range)
=FILTER(VSTACK(D1:D24), VSTACK(D1:D24)0)
it could be
=LET(data, VSTACK(D1:D24), FILTER(data, data0))
if data range is big, does it help, improves performance?
A better approach is to put each worksheet’s data into a Table. As each Table changes size, its contribution to the VSTACK will also change size. The VSTACK formula remains simpler, and you don’t risk filtering improperly, for example, removing zero values that should be there.
Great point, Jon! Tables are another good solution here.
I should have mentioned that in this scenario, they wanted to be able to drop in additional sheets and use a 3D reference to automatically include the new sheets.
I don’t know if there is a way to do that with Tables. I believe all of the Table names would have to be referenced in the VSTACK, which would get long and challenging to manage if there were 20+ sheets with Tables.
Re:VSTACK
I use marcos for some. In my macro, I select area and clear contents to make sure that vstack area has no problem cells.
Hi,
Can HSTACK/VSTACK functions be used in VBA
Good, excluding blank rows works like a charm!
However, the link to the including of headers is dead from your email and when I got to the right video, I failed to get the filter function addition to work. I checked and checked and in the end I wonder if there is something I am missing that is not so obvious!
Best wishes
Duncan
sorry i think my comment might not have been so clear
what is not working is combining the exclusion of blank rows … that works
the inclusion of headers … that works
but the combination of the two does not work
I tried this in my own file and in the file you shared, too
Duncan
I CAN NOT UNDERDTAND DIVISION1:DIVISION4
After getting the data copy and paste values