Problem description

The Excel file book1.xlsx contains data as shown below. There are 4 tables of 5 rows and 5 columns. The first rows in columns 2-4 are numbers representing the vertical axis. Rows 2-4 in the first column in each table contains numbers, too, which represent horizontal axis.

We are trying to summarize data in the workbook by adding up numbers with same coordinates. And then list all vertical coordinates and horizontal coordinates and their corresponding aggregate values. Below is the expected result:

The problem involves retrieving data from a certain area in an Excel file, grouping & aggregation, and data transposition.

Directions

1. Write the following script:

A1   Define file book1.xlsx.

A2   Loop through the four tables to read data from them.

B2   Get the number (r) of start row for the current table.

B3   Get the number (c) of start column for the current table.

B4    Get the string of all column numbers of the current table. For example, the string for table 2 is #8,#9,#10,#11,#12.

B5   Import rows from r to r+4 in columns specified by numbers in B4 from book1.xlsx. @t option enables reading the first row as column headers.

B6   Perform row-to-column transposition on the table read by B5; @r enables a column-to-row transposition. Data is grouped by column 1 and new column numbers are set as y and value.

B7   Concatenate each transposed table sequence to B1.

A8   Group B1’s data by column 1 and y, sum values in each group, and name the totals column value.

A9   Perform row-to-column transposition on A8’s grouped and summarized data. Group the data by column 1, and make y values new column names and value values new column values.

A10   Export A9’s result to book2.xlsx. @t option enables outputting the first row as column headers.

2. Run program and then we can view the newly-generated book2.xlsx in work directly.

Q & A Collection  

https://stackoverflow.com/questions/63803374/how-can-i-sum-a-series-of-matrices-based-on-the-values-in-the-first-row-and-colu