Combine Excel Ranges (without VBA)
I’ve been finishing off a project modifying a client’s existing spreadsheets. The spreadsheet allows a courier company to log various aspects of its delivery business each day with one worksheet for each day.
One aspect they log is failed deliveries.
One each day’s tab, there’s a table for delivery issues and another for failed deliveries.
So far, so good.
The client then advises they want the failed deliveries repeated and combined in another tab.
“Why is that?”, I ask. “The failed deliveries are displayed for each day.”
“Oh, so the manager can view the failed deliveries”, comes the response, “and they don’t want to click each tab.”
Now, those of you who follow me know I’m a VBA aficionado. I confess my first and immediate inclination was to fire up the VBE and write some VBA code.
Shouldn’t take long, combine 7 tables into a single table and filter out any blank rows.
But I resisted. Surely there’s a way which didn’t require VBA to be triggered each time a daily failed deliveries table was updated.
And there was.
The Solution (Version 1)
Enter VSTACK.
VSTACK is an Excel formula which lets you combine ranges vertically (for the curious amongst you, there’s also a HSTACK).
Simply list each range you want to combine as an argument with the VSTACK formula, like this:=VSTACK(FailedDels_01, FailedDels_02, FailedDels_03, FailedDels_04, FailedDels_05, FailedDels_06, FailedDels_07)
Job done.
Well, almost.
VSTACK combines *everything* in the range, including the blank rows which we don’t want. With VSTACK, blank rows display as zeros.The Solution (Version 2)
VSTACK combined with FILTER lets you combine multiple ranges vertically AND exclude blank rows.
Note this time, the FILTER is applied to a column in each table we want to filter by:=FILTER( VSTACK(FailedDels_01, FailedDels_02, FailedDels_03, FailedDels_04, FailedDels_05, FailedDels_06, FailedDels_07), (VSTACK(FailedDels_01[Store No], FailedDels_02[Store No], FailedDels_03[Store No], FailedDels_04[Store No], FailedDels_05[Store No], FailedDels_06[Store No], FailedDels_07[Store No]) <> "") )
Effectively we’re saying, for each table filter where the column called [Store No] is not blank.
Note how much easier the formula is to read using Tables rather than cell references.