Leggi questo articolo in Italiano

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.

### 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.