#41 — Group And Summarize Rows While Retaining Columns Whose Values Are Unchanged

Problem description & analysis:

In the Excel table below, the ID column is the category; Value1 and Value2 contain numbers; both Descr 1 and Descr 2 remain unchanged under the same ID value.

This is an older version of Excel (which does not support groupby function). Task: Group rows by ID and sum the Value1 field and Value2 field while retaining the Descr 1 field and Descr 2 field.

Solution:

Use SPL XLL to get this done:

=spl("=E(?).groups(ID;sum('Value 1'):'Value 1',sum('Value 2'):'Value 2','Descr 1','Descr 2')",A1:E10)

As shown in the picture below:

Explanation:

E()function converts a data arrangement to a table. groups() function groups and summarizes the table, during which a newly generated column can be renamed through the semicolon.

Comments

[Judith-Excel-Sharing]

Please feel free to download esProc Desktop and boost our Excel productivity with SPL XLL! ⬇️

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

[Judith-Excel-Sharing]

Feel free to join our Excel productivity conversation:

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