Maximum value as well as Sum

[Raj]

Hi,

I have the following data in a list:

Col A Col B Col C Col D
B 9 1 Red
C 12 3 Green
A 13 1 Red
B 14 2 Violet
C 15 4 Yellow
B 16 2 Yellow
A 18 2 Green
B 19 2 Green
C 21 5 Red


I want to retain only those rows where:
1. For each unique value in Column A, the value in Column B is maximum
and Column C in that row has the sum of col c for that unique value.
The output from the above list should be:

Col A Col B Col C Col D
A 18 3 Green
B 19 11 Green
C 21 8 Red


Thanks in advance for all the help.

Regards,
Raj

 

[Sheeloo]

1. Select Col A
Choose Data|Filter|Advanced Filter
Choose Copy to H1
Unique Records only

You will get the unique values from Col A into Col H
in I2 enter
=MAX(IF($A$2:$A$10=A2,$B$2:$B$10,0))
and press CTRL-SHIFT-ENTER to get the MAX value for the value in H2
and copy down

In J2 enter
=SUMPRODUCT(--($A$2:$A$10=H2),$C$2:$C$10)
and copy down to get the Sum for the unique value in H

In K2 enter
=INDIRECT("D"&MATCH(I2,(IF($A$2:$A$10=H2,$B$2:$B$10,"")),0)+1)

btw I got the following;
ColA Max Sum Color
B 19 7 Green
C 21 12 Red
A 18 3 Green