How to merge data from different tables into one table?

Question

excel

Greetings.
Excel has two sheets, each with a table. The tables have a different set of fields and there is some data that I want to combine into one table on a separate sheet. Here is an example of what is and how to do it:

I looked on the net for different methods, but either I can’t do it, or the methods are not the same. Prompt, please, implementation. I would appreciate an example in the file, but an explanation will do.

Answer 1

Hello. I solved such tasks at work using Excel 2007. The file with a sample solution is at the link below. For clarity, I did everything on one sheet. If the tables are on different sheets, the principle is the same. In short:
1. All tables must be converted to Excel smart tables. So you don't have to keep track of the size of these tables.
2. Considering that the tables are on different sheets, the question arises whether all rows are included in the join table. To know this for sure, I make myself an "indicator" near the join table.
3. Filling in data occurs through control of the number of rows in the source tables and the row number in the combined table. That is, "if the number of the current row in the combined table is less than or equal to the number of rows in the first table, then we take from the first table,
4. The number of the current line is the formula LINE() minus the number of lines to this line from the top of the sheet. In the sample, it is "ROW()-1" because the contents of the table start on the second line. You need to follow this and adjust the formulas depending on the position of the table on the sheet.
Actually, the sample file .
Just in case, the same in the zip archive .
I hope it helped. Good luck!

Answer 2

Use a pivot table

Answer 3

if without SQL and third-party utilities, then most likely Extended pivot tables: combining data from ... and / or Pivot table over several data ranges