How to delete rows with repeating values and leaving only one with highest value on the next column?

[Justin]

Hi,

I really need a macro that will delete all the rows with repeating
values leaving only the last row that has the highest value on the
column next to it. Basically my data has 2 columns, the one on the
right has repeating values while the one on the left is increasing in
values. So I need a macro that would delete both rows right up to the
pair where the column on the left has the highest value.



Please help me!

Thanks heaps!

 

[Frank Kabel]

Hi
lets assume your repeating value is in column A and the oncreasing
value in column B
Enter the following array formula ain C1 (entered with
CTRL+sHIFT+ENTER):
=IF(B1<>MAX(IF($A$1:$A$100=A1,$B$1:$B$100)),"X","")
and copy this down for all rows. Now filter with column C and delete
the filtered rows