How to combine two or more tables into one master table in Excel?-CodePudding

Suppose we have two tables with the same headings on different sheets:

Sheet 1:

Subject
Biology
History

Sheet 2:

Subject
Maths
Biology

However, I have Sheet 3, which I'd like to be a combination of those two tables. I.e.

Sheet 3:

Subject
Biology
History
Maths
Biology

Also, if I update e.g. Sheet 1 with a new row to contain Physics, I'd like the table in Sheet 3 to reflect this, so that it becomes:

Sheet 3:

Subject
Biology
History
Physics
Maths
Biology

Does anyone have an idea on how to do this? I thought that a simple = in the cells of Sheet 3 to reference Sheet 1 and Sheet 2 could work but this doesn't take into account how Sheet 1 and Sheet 2 can grow in size.

CodePudding user response：

Assuming the Sheet1 and Sheet2 tables are Tables named Table1 and Table2:

=FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,T(IFNA(IF({0,1},Table1[Subject],Table2[Subject]),"")))&"</b></a>","//b")

This being a dynamic array formula it will not be possible to enter it within a Table. Note also that character limitations on TEXTJOIN mean that this set-up might not be suitable for large datasets.

CodePudding user response：

Try something like this:

=IF(ROW()<MATCH(TRUE;INDEX(ISBLANK(Sheet1!C1);0);0);Sheet1!RC1;INDEX(Sheet2!C1;ROW()-MATCH(TRUE;INDEX(ISBLANK(Sheet1!C1);0);0) 1;0))

Where:

MATCH(TRUE;INDEX(ISBLANK(Sheet1!C1);0);0) is the position of first blank cell in Sheet1

ROW()<MATCH(TRUE;INDEX(ISBLANK(Sheet1!C1);0);0) is true when Sheet1 has a value in the current row

Sheet1!RC1 is the value in Sheet1 at the current row

INDEX(Sheet2!C1;ROW()-MATCH(TRUE;INDEX(ISBLANK(Sheet1!C1);0);0) 1;0) is the value in Sheet2, shifted to ensure the values of Sheet2 are below the values of Sheet1