Merging different tables with same and different values into one summary-table

[Spyral96]

Hello, everyone!

I am currently working on a folder with several tables. Suppose I have 4 tables. In these tables I have 6 columns which take the same values ​​in different tables on different pages (example: name, age, location). Then I have other columns, which are always different after the first 6 identical columns (example: a table with columns on money, a table with columns on family, etc.). Can I merge all these tables into one, so that with the first 6 columns that are the same, I can have the rest of the different columns linked together in a new table?

Thank you for your help and best regards

[#2]

You may VSTACK() to merge table and use other functions to perform data analysis. Share (attach) a sample workbook and show your desired output.

[#3]

@Harun24HR Agreed.

@Spyral96 

This is what it might look like.

= LET(
    combined,     VSTACK(moneyData, familyData),
    first6fields, TAKE(combined, , 3),
    distinct,     UNIQUE(first6fields),
    distinctName, TAKE(distinct, , 1),
    familyName,   TAKE(familyData, , 1),
    familyMember, TAKE(familyData, , -1),
    family,       XLOOKUP(distinctName, familyName, familyMember, ""),
    moneyName,    TAKE(moneyData, , 1),
    moneyAmount,  TAKE(moneyData, , -1),
    money,        XLOOKUP(distinctName, moneyName, moneyAmount, ""),
    summary,      HSTACK(distinct, family, money),
    summary

Alternatively you might choose to use PowerQuery.  Whereas the formula requires Excel 365, PQ is included from 2013 and can even be used with Office 2010.