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

Comments

[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]

Please feel free to download SPL XLL and boost your Excel productivity ⬇️

♦️SPL download address: scudata.com/download-Desktop

✨Plugin Installation Method: c.scudata.com/article/1652061135502

♦️References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524

✨SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...