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:
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.