Remove duplicate rows based on 1 specific criterion

S

Sweepea

Question:

A B
Apple 1/1/2004
Apple 2/1/2005 (to be deleted)
Orange 1/1/2004
Orange 2/1/2005(to be deleted)
Orange 3/1/2006(to be deleted)

I want to delete the duplicate rows based on column A. The rows to keep will
be the one with earliest date in column B.

Appreciate any help. Thank you.
 
P

Peo Sjoblom

data>filter>advanced filter, you need headers for both columns, then use a
formula as criteria

Assuming that the dates are in order first to last, assume the first fruit
is in A4 and the criteria range is F1:F2, leave F1 blank and put

=COUNTIF($A$4:A4,A4)=1

in F2

then apply the filter



--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)
 
S

Sweepea

Hi Peo,

I am sorry I can't quite get it. Please advise further.

I set it up this way and apply the filter but nothing happened.

Fruit Date
apple 1/1/04 TRUE (=COUNTIF($A$4:A4,A4)=1)
orange 1/1/04
apple 2/1/05
orange 2/1/05
orange 3/1/06

To reiterate, I want my final list to have

apple 1/1/04
orange 1/1/04

Thank you again.



Swee.
 
S

Sweepea

I couldn't eliminate one of the lines for apple. This is the result set I get:

Fruit Date
apple 1/1/04 TRUE
orange 1/1/04
apple 2/1/05
 
S

Sweepea

Thank you so much, Dave! This works!!



Dave Peterson said:
How about just selecting column A and using data|filter|Advanced filter and
checking the box for unique records only.

Then copy the visible data to a new sheet.

This would depend on column B being in nice order so the top most "Apple" is
kept.

I'd sort the data by column A (ascending), then column B (ascending).

Debra Dalgleish explains it in more detail:
http://www.contextures.com/xladvfilter01.html#FilterUR
Click to expand...
 
Top