## DEV Community

Judith-Excel-Sharing

Posted on • Updated on

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

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.

Judith-Excel-Sharing

For anyone passionate about streamlining Excel tasks or diving into data analysis, join the communities to communicate with more experts:

🔸Discord: discord.gg/hgbKEvJ4
🔸Reddit: reddit.com/r/esProc_Desktop/

Judith-Excel-Sharing