Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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