In the workplace, it is often necessary to summarize and sum data from multiple tables. Do you know how to do this?

Today, we will teach you an efficient data aggregation method for several common situations, which can be completed in one minute.

Type 1: Data comes from multiple Excel files and is summarized into a new Excel file.

Type 2: Data comes from multiple sheets in the same Excel, insert a summary sheet.

Firstly, we need to use a tool called esProc SPL, which is a software specifically designed to handle structured table data. It has powerful computing capabilities and is easy to use. After downloading, double-click to install. The examples in this article provide source code that can be copied, pasted, and used.

Download address: esProc Desktop Download

Type 1: Data comes from multiple Excel files and is summarized into a new Excel file

1）Group and summarize on a single condition

There are multiple Excel files, each containing daily sales details of several fruit products. Now, we need to summarize the data for several months to calculate the total sales of each fruit.

Before summary:

After summary:

Firstly, place all the Excel files that need to be summarized in the same folder, for example, in the folder named test on drive D. Then run the code:

      A
1    =directory@p(“D:/test/*.xlsx”)
2    =A1.conj(T(~))
3    =A2.groups(Product;sum(DailySales):TotalSales)
4    =file(“D:/test/Total.xlsx”).xlsexport@t(A3)

Summary is done!!!

Let’s explain the code:

A1 directory is a function here, meaning to list all .xlsx file names and directories in the test folder.

A2 Open the files in the A1 path in a loop and merge them.

The T() function represents opening a specified path file.

A.()is a loop function, where A1.(T(~)) represents looping through each file path in A1 and opening the file using the T() function. The ~ symbol represents the object for each loop.

After opening all the files in a loop, we also need to merge them, so we add a conj() function, meaning conjunction.

Therefore, the code A1.conj(T(~)) in A2 represents opening the files in A1 and merging them into one table.

A3 Group by product, and get the sum of each product sales as TotalSales.

A4 Save summary data as a Total.xlsx file.

2）Group and summarize on multiple conditions:

The sales details files mentioned above still needs to summarize the sales amount of each product on a monthly basis and save it in a new Excel file.

Effect after summary:

Implementation code:

      A
1    =directory@p(“D:/test/*.xlsx”)
2    =A1.conj((fn=filename@n(~),T(~).derive(fn:Month)))
3    =A2.groups(Month,Product;sum(DailySales):MonthSales)
4    =file(“D:/test/MonthTotal.xlsx”).xlsexport@t(A3)

Summary is done!!!

Code interpretation:

A2 opens each Excel file in a loop, adds the variable Month, with the value of the month from which the data comes, and then merges multiple files.

A3 groups and summarizes by Month and Product to get the monthly sales amount of each fruit product.

Type 2: Data comes from multiple sheets in the same Excel file, insert a summary sheet

There are multiple sheets in the same Excel file for the sales details of each product, and a summary sheet needs to be generated to obtain the total sales of each product.

Before summary:

After summary:

Implementation code:

      A
1    12
2    =A1.conj(T(“D:/DailySales_2023.xlsx”;~))
3    =A2.groups(Product;sum(DailySales):TotalSales)
4    =file(“D:/DailySales_2023.xlsx”).xlsexport@kt(A3;“Total”)

Summary is done!!!

Code interpretation:

A1 Enter the number of sheets to be summarized, such as 12(sheet 1 to 12), which are the sales data for the past 12 months

A2 opens sheets 1 to 12 in a loop and merges them into one table

A3 groups and summarizes by Product to obtain the total sales amount of each product

With the help of SPL tool, data summary from multiple tables can be implemented instantly.

And the syntax of SPL functions is simple, in line with natural logical thinking, and it is not difficult to understand.

Of course, the functions of SPL are not limited to this, and it is not a problem to SPL for Excel operations in various complex scenarios.

For those in need, you can refer to Desktop and Excel Data Processing Cases. Ninety per cent of Excel problems in the workplace can find answers in this book. The code in the book can basically be copied and used with slight modifications.

If you ever encounter an Excel problem, please always feel free to search answers at esProc Desktop: http://www.scudata.com/esproc-desktop/. It’s absolutely FREE & EASY to download and apply.