4.22 Aggregate files - merge by row and column simultaneously - aggregate duplicate records

 

Before merging:

CustomerFruits1.xlsx
imagepng

CustomerMeats1.xlsx
imagepng

The final result after aggregating duplicate records and merging is:

imagepng

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