RaqForum 43 No.
375 View  • 
     ...

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.

Q & A Collection

https://stackoverflow.com/questions/63673362/sum-by-unique-id-and-auto-deletion


 
 •  375 View  •