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:

imagepng

Feb.xlsx:

imagepng

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:

imagepng

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