4.18 Merge by row and column simultaneously - keep data that firstly appear
Before merging:
CustomerFruits1.xlsx
CustomerMeats1.xlsx
According to the order of CustomerFruits1.xlsx first and CustomerMeats1.xlsx later, the duplicate records that appear in CustomerFruits1.xlsx first are kept. The result after 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).group@1(Name,col) |
| 6 | =A5.pivot(Name;col,val) |
| 7 | =file(“CustomerFoods1.xlsx”).xlsexport@t(A6) |
A3: Transpose the data of original cross layout to a list
A5: Select the record that appears firstly after grouping
A6: Transpose the data back to cross layout
esProc Desktop and Excel Processing
4.17 Merge and de-duplicate by column - duplicate column names - keep data in columns that appear later
4.19 Format conversion - merge multiple card-style files to form one row-based table
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
