Excel: Use VSTACK to Combine Lists Stored in Multiple Sheets but Exclude Blank Rows
The VSTACK function (available to Excel 365 users) allows you to quickly and easily combine multiple lists into a single list. However, if the source lists contain blank rows, those blank rows will also appear in the combined/consolidated list. This video focuses on how to remove the blank rows. I show three ways to do this.
The first is a quick workaround for hiding zeros. But for a more robust solution that aligns with best practices for working with Dynamic Arrays, a powerful combination of the FILTER and VSTACK functions will ensure a clean, consolidated list without the blanks.
🗒 Notes and Resources
Download a copy of the file used in this video: https://app.box.com/s/ul8u2ufqvkv5mfm...
The formulas that I used in the demos:
=VSTACK(tblJan,tblFeb,tblMar,tblApr,tblMay,tblJun)
=FILTER(VSTACK(tblJan,tblFeb,tblMar,tblApr,tblMay,tblJun),VSTACK(tblJan[OrderID],tblFeb[OrderID],tblMar[OrderID],tblApr[OrderID],tblMay[OrderID],tblJun[OrderID]) less_than greater_than "")
=FILTER(VSTACK(Jan:Jun!A6:G50),VSTACK(Jan:Jun!A6:A50)less_than greater_than "")
NOTE: YouTube doesn't allow angle brackets in the description. Please replace less_than greater_than with the "less than" and "greater than" symbols
✅ Want more FREE training?
Why not check out my other tutorials and/or subscribe to my newsletter, both of which can be done at https://theexceltrainer.co.uk
🙋♂️ Let's connect on social
Twitter: / theexceltrainer
Facebook: / theexceltrainer
LinkedIn: / thomasmik
#excel #filter #vstack #consolidate