#38 — Group Columns of An Excel Table And Perform Aggregation

#productivity #tutorial #programming #beginners

Problem description & analysis:

In the Excel table below, there are multiple duplicate columns.

We need to group the table by columns and sum values in each group.

Solution:

Use SPL XLL to get this done:

=spl("=E@2bp(E@bp(?).groups(#1;${(?.len()-1).(eval@s($[sum(#?)],#+1)).concat@c()}))",B1:H7)

As shown in the picture below:

Explanation:

E()function converts an Excel table to a two-layer sequence; @ p option enables a transposition, @ b means not converting the titles, and @ 2 represents a two-layer sequence. groups() performs grouping and sum; ${} treats a string as an expression to execute. eval@s() loops each string in a sequence to replace them and takes them as an expression to execute. concat@c concatenates members of the sequence using the comma.