How to create a union of multiple tables in excel

Sooner or later, whoever is working heavily with excel had to face how to combine multiple data sources into one and use it as a master table for all calculations.

So today let's start simple and see how we can achieve it: download the example file and follow along.


Few clarifications before we start:

Ok, now let's get our hands dirty.

Helper table

The key is to use a helper table where we calculate/specify how many rows do the tables have and what is the starting row for each table.
Let's dissect the formulas.

you can trace calculations steps for every formula by going to
Formulas Evaluate formula (in Formula Auditing contextual menu)

TableName here we input manually the Tables names. One for each row.

Rows =ROWS(INDIRECT([@TableName]))
explanation: counts how many rows each table has.
Returns the reference (INDIRECT) specified by the text string taken from the table name (TableName) on the same row (@) and count the number of rows (ROWS). In this example TableName refers to "DepartmentA" which have a range of "B9:C11" which equals 3 rows

StartingRow =SUM(OFFSET([Rows];-1;0;ROW()-ROW(Helper[#Headers])))+1
explanation: calculate the starting row (progressive) at which each table starts. The formula seems complex but it simply calculates the sum of previous tables heights as number of rows and add +1

Master table

Now that we have our helper table set up, we can proceed in compiling the final master table with a couple of pre-calculations.

RowID =ROW(Master[@])-ROW(Master[[#Headers];[RowID]])
explanation: a simple method to number the rows progressively (1, 2, 3, 4, 5, etc.)

Table =VLOOKUP([@RowID];Helper;3;TRUE)
explanation: look for TableName in the Helper table based on the RowID just calculated.
It is interesting to notice the use of TRUE in a Vlookup formula (not very common as we normally set it to FALSE to get the exact match). For RowID 2 for example, it looks for a 2 in the first column of Helper table: with FALSE it would return an error N/A, with TRUE it return the approximate value which is 1 and since we are asking the 3rd column it returns "DepartmentA".

Row =[@RowID]-VLOOKUP([@RowID];Helper;1;TRUE)+1
explanation: calculate the row number for each table.
In this example "DepartmentA" table has 3 rows so in the master table you have 3 rows with 1, 2, 3.

Name and Surname =INDEX(INDIRECT([@Table]);[@Row];1) and =INDEX(INDIRECT([@Table]);[@Row];2)
explanation: it displays the value of the cell corresponding to the table and the row/column number.
Table name and row number we have just calculated, column number is that 1 (name) and 2 (surname) in the formula.

Done! We have now all the 3 tables combined into one and we can use it to run a pivot table or calculate further. Please check also this article which is my real true inspiration.

Let me know your thoughts and approaches.