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

Question

Hello, everyone!&nbsp;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?&nbsp;Thank you for your help and best regards

harun24hr · Answer

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

peterbartholomew1 · Answer

Harun24HR&nbsp;Agreed.Spyral96&nbsp;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),
    summaryAlternatively you might choose to use PowerQuery.&nbsp; Whereas the formula requires Excel 365, PQ is included from 2013 and can even be used with Office 2010.&nbsp;

Forum Discussion

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

Share

Resources