4.26 Aggregate files - insert aggregation sheet
A shopping mall complies a purchase summary table of key customers for 12 months of the year in the format shown below:
Jan.xlsx：
Feb.xlsx：
Files of other months are omitted.
Now we want to aggregate these Excel files to different sheets of one Excel file with file name as the sheet name, and insert an aggregation sheet named Total on the home page.
The aggregated Excel is as follows:
Script:
| A | B | |
|---|---|---|
| 1 | [Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec] | |
| 2 | =A1.(T(~+“.xlsx”)) | |
| 3 | =A2.conj().groups(CustomerName;sum(Apple):Apple,sum(Banana):Banana,sum(Peach):Peach,sum(Strawberry):Strawberry) | |
| 4 | =T(“Total.xlsx”,A3;“Total”) | |
| 5 | for A2 | =file(“Total.xlsx”).xlsexport@at(A5;A1(#A5)) |
A3: Aggregate data
A4: Export A3 to the first sheet of Excel, and name it as Total
B5: Append the original data to the subsequent sheets of Excel and name them with file name; @a option means appending the data
esProc Desktop and Excel Processing
4.25 Aggregate files - cumulate and aggregate
5.1 Split by row - by number of rows
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/
