In Excel, I have hundreds of items like these:

   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, and r3 have the same values in every column except column_e as do r4, r5, and r6.

I would like to see only the unique rows with maximum column_e value. For example, from the above, I would only see r1, which has a maximum column_e value of 3 and r6, which has a maximum column_e value of 4.


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)
  • 2
    If the row number is required, that could be achieved by adding an ID row (to the data table) and using the index functions on the pivot table. – Pynner Dec 20 '11 at 1:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.