— 2017-04-03 I have new post for Excel 2016 (2013) —
It is very common in Excel to need to create a single table from the two or more other tables. For example, different users may edit different worksheets but you need to aggregate all the data for processing.
This can be done relatively simply using structured references to tables. I have my data in two tables “Table1” and “Table2” and I want the union in “Table3”. For convenience I create RowId column in Table3. To the right of this I enter,
= IF([RowId]<=ROWS(Table1)
, INDEX(Table1[column1],[RowId])
, INDEX(Table2[Column1],[RowId]-ROWS(Table1))
)
The IF statement determine whether we should be rendering a value from Table1 or Table2. The INDEX function returns the data from the appropriate column and row.
In practice there is a small problem with this formula; when you drag it right to create more columns Excel treats the [RowID] as a relative reference and changes it. To make it absolute we use the INDIRECT function. This does not change the simple logic of the above formula but makes it easier to use.
=IF( INDIRECT("Table3[RowId]")<=ROWS(Table1)
,INDEX(Table1[column1],INDIRECT("Table3[RowId]"))
,INDEX(Table2[Column1],INDIRECT("Table3[RowId]")-ROWS(Table1)))
Dr James Bayley 
Thanks James, very helpful. You could also replace
INDIRECT(“Table3[RowId]”)
by
ROW()-ROW(Table3[[#Headers],[Column1]])
this way you don’t have to worry about creating a RowID Column
Cheers,
Nic
A nice touch Nic which makes it simpler.
Pingback: How to create a union of n tables in Excel | Dr James Bayley
This is a nice technique. Thanks for sharing.
I recently started using excel 2013 with the free add-on called Power Query.. it can merge as many table as you want while keeping a link, from the same file or from different files. If you have excel 2013, you have to try power Query . This is a very powerful new feature from Microsoft.
Cheers,
Thanks, Nicolas. This is really good to know. There is a similar tool for Excel 2010 but I don’t know if this also has the ability to merge tables.
ExcelCampus (http://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/) takes care making [RowID] an absolute reference so the formula can be dragged to the right. Replace all occurrences of [RowID] in your formula with Table3[@[RowID]:[RowID]]. Since INDIRECT is a volatile function, I try to avoid it.
Is their any way of doing the above where the result table (union of the two sub-tables) dynamically adjusts in size based on the changes to the two sub-tables (as rows are added/deleted)?
Yes. I have blogged it here.
https://blog.jamesbayley.com/2017/04/03/create-the-union-of-two-tables-in-excel-revisited/
It works with Excel 2013 but you have to download and install Microsoft Power Query for Excel matching the architecture (x86 or x64) of your Office installation: https://www.microsoft.com/en-US/download/details.aspx?id=39379
Pingback: Create the Union of two tables in Excel revisited | Dr James Bayley
This formula is great, thank you. I’ve been unable to figure out what the formula would be if we’re using more than two tables.
It is possible but it is no longer a sensible way of doing it. With Excel 2016 you should follows these instructions.
https://blog.jamesbayley.com/2017/04/03/create-the-union-of-two-tables-in-excel-revisited/
If you have Excel 2013 then see Petr Polak’s comments above.