Filter rows with unique values on Microsoft Excel

Question

I have got a set of rows in an microsoft-excel

   column_a      column_b       column_c       column_d      column_e

r1      200           300            350            400             3
r2      200           300            350            400             1
r3      200           300            350            400             2
r4      100           150            200            300             1
r5      100           150            200            300             2
r6      100           150            200            300             4

Rows r1, r2, r3 values are the same except column_e and r4, r5, r6 values are the same except column_e.

I would like to see only the unique rows with maximum column_e value. i.e

r1, which has a maximum column_e value '3' and

r6, which has a maximum column_e value '4'.

I have got hundreds of such values in an excel sheet. It would be great if any of you can provide me a script or macro which will filter the unique rows based max column_e value. Thanks in advance.

Answer 1

If you just want the maximum values and you don't care which row it's from, you could:

1. create a pivot table with column_a, column_b, column_c, and column_d in the Row Labels section and column_e in the Values section (change it to show Max rather than Count), or
2. use Subtotal (selecting Max as your function)