Combine Tables with Different Column Order
Combining data from multiple tables in Excel has historically been very manual … especially when the tables have different column orders. In this blog post, we’ll tackle this challenge by getting Excel to combine the tables with a formula that uses the VSTACK and CHOOSECOLS functions.
Video
Step by step
Let’s use a basic example. We have three tables: a January table, a February table, and a March table. The Jan and Feb tables have the identical column structure. Here is the Jan table:
And here is the Feb table:
However, let’s take a look at the Mar table, which not only has a different column order it also has an additional column:
So, the question is, how can we combine these three tables without manually rearranging the columns? One option is by using VSTACK and CHOOSECOLS. Let’s get to it.
VSTACK
Our first step is to use VSTACK, which combines tables. Since the tables are named Jan, Feb, and Mar, we’ll start with the following formula.
=VSTACK(Jan, Feb, Mar)
When we press enter, we do not get the results we want.
This is because the column orders are different and the March table has an additional column. To solve this issue, we’ll use a helper function called CHOOSECOLS.
CHOOSECOLS
The CHOOSECOLS function enables us to pick and choose which columns from a range we want, and in what order. So, we update our formula by using CHOOSECOLS to return only the columns we want from the Mar table and we also specify the order.
=VSTACK(Jan, Feb, CHOOSECOLS(Mar,3,1,2))
Basically, we’re asking the VSTACK function to combine the transactions from the January table, the February table, and the March table’s third column, first column, and second column.
When we press enter, we see the tables combined!
Using VSTACK and CHOOSECOLS, we’ve managed to combine the values from multiple tables even though the tables have a different column structure. With functions like VSTACK and CHOOSECOLS, it’s easy to combine the values of multiple tables with different column orders.
If you have any other ways to accomplish this, or have additional VSTACK or CHOOSECOLS examples or suggestions, please share by posting a comment below … thanks!
Sample file
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Our training programs start at $29 and will help you learn Excel quickly.
OK, this was rad! Thank you!
Thank you 🙂