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:
- 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.
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.
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
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
Now that we have our helper table set up, we can proceed in compiling the final master table with a couple of pre-calculations.
explanation: a simple method to number the rows progressively (1, 2, 3, 4, 5, etc.)
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".
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
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.