— 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)))

* *

### Like this:

Like Loading...

*Related*

Nicolas Cantin

said: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

Dr James Bayley

said:A nice touch Nic which makes it simpler.

Pingback: How to create a union of n tables in Excel | Dr James Bayley

Steve Whisenhant

said:This is a nice technique. Thanks for sharing.

Nicolas

said: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,

Dr James Bayley

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

Lee Townsend

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

Sam

said: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)?

Dr James Bayley

said:Yes. I have blogged it here.

https://blog.jamesbayley.com/2017/04/03/create-the-union-of-two-tables-in-excel-revisited/

Pingback: Create the Union of two tables in Excel revisited | Dr James Bayley