Summarize Data in Every Two Columns under Each Category

Judith-Excel-Sharing - Jul 10 - - Dev Community

Problem description & analysis:

In the Excel table below, column A contains categories and there are 2N key-value formatted columns after it:

original table

We need to group rows by the category and the key and perform sum on detail data. The expected result set will have 3 columns. Note that the result set should be arranged according to the original order of the category column.

desired table

Solution:

Use SPL XLL to enter the following formula and drag it down::

=spl("=E(?).groupc@r(Country;;Label,Count).groups@u(Country,Label;sum(Count):Total)",A1:G11)
Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

desired result table with code entered

Explanation:

E()function reads data in its original table format. groupc@r performs column-to-row transposition by putting every n column in one group. groups() function performs grouping & aggregation.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .