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.

exercise

Few clarifications before we start:

  • In excel, I like using tables. A lot! This method can be adapted of course to work with named ranges or simple ranges, but I find tables to be cleaner and more structured.
  • This example is the basis, therefore I am considering 3 tables which have the same two columns (name and surname) and we want to list them one after the other.
  • In this example we list each entry of each table, without any check on repeated names, etc.

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.

HINT: 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.