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
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

You must log in to answer this question.

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