~ Creating successful projects

How to create a union of n tables in Excel

I have seen your blog post about combining two tables, which I have working for me, however I would like to combine an ever increasing number of tables …, into a common “list”. How to do it without ending up with a nest IF statement beyond all control?!

We can avoid writing a deeply nested IF statement by creating a table that does some of the work for us.

The logic of the solution is the same as for the two table problem. A cell in the output table must ask,

“Which table should I get my value from?” and

“Which row should I get my value from?”

We create a helper table with this information in it.

First, let’s look at the work book, this has three tables to union, a helper table and an output table.

I think that this solution is quite elegant because it is easy to add additional tables and columns to the union and is reasonably readable. However when time allows I might write a custom function in VBA that uses the same logic.