Excel Auto-delete Empty Detailed Rows after Grouping
Problem description & analysis
Below is Excel data:
ID |
Criteria1 |
Criteria2 |
Criteria3 |
Criteria4 |
101 |
2 |
|||
102 |
7 |
|||
123 |
93 |
|||
123 |
8 |
|||
123 |
6 |
|||
123 |
17 |
|||
182 |
6 |
|||
182 |
9 |
|||
182 |
39 |
|||
182 |
120 |
|||
182 |
147 |
We are trying to group rows by ID and sum values in the other columns. Below is the desired result:
ID |
Criteria1 |
Criteria2 |
Criteria3 |
Criteria4 |
101 |
2 |
|||
102 |
7 |
|||
123 |
93 |
25 |
6 |
|
182 |
6 |
9 |
39 |
267 |
Solution
Use SPL XLL plug-in
Write the following formula in a blank cell:
=spl("=E(?).groups(ID;sum(Criteria1):Criterial1,sum(Criteria2):Criterial2,sum(Criteria3):Criterial3,sum(Criteria4):Criterial4)",A1:E12)
As shown:
Return:
Explanation: Group rows by ID and sum each Criterial column.
https://stackoverflow.com/questions/63673362/sum-by-unique-id-and-auto-deletion
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
