...
4.22 Aggregate files - merge by row and column simultaneously - aggregate duplicate records
Before merging:
CustomerFruits1.xlsx
CustomerMeats1.xlsx
The final result after aggregating duplicate records and merging is:
Script:
| A | |
|---|---|
| 1 | =file(“CustomerFruits1.xlsx”).xlsimport@t() |
| 2 | =file(“CustomerMeats1.xlsx”).xlsimport@t() |
| 3 | =A1.pivot@r(Name;col,val) |
| 4 | =A2.pivot@r(Name;col,val) |
| 5 | =(A3|A4).groups(Name,col;sum(val):val) |
| 6 | =A5.pivot(Name;col,val) |
| 7 | =file(“CustomerFoods2.xlsx”).xlsexport@t(A6) |
A3: Transpose the data of original cross layout to a list
A5: Grouping and aggregating
A6: Transpose back to cross layout
