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
esProc Desktop and Excel Processing
4.21 Aggregate files - same rows and columns
4.23 Aggregate files - aggregate by cell positions - unfixed number of files
SPL Official Website 👉 https://www.esproc.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.esproc.com
Discord 👉 https://discord.gg/sxd59A8F2W
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
