Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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)
share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .