Forum Discussion

Copper Contributor

Dec 19, 2022

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

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 pa...

excel

Formulas and Functions

Macros and VBA

PeterBartholomew1

Silver Contributor

Dec 19, 2022

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.