Finding Duplicates

[Karen]

Excel 2003

I use Excel for a mailing database. Because there are so many "records"
(approx 6,000) it is likely that there will be different people who share a
name. I don't want to filter out John Hanks at Company XYZ just because the
advanced filter finds a John Hanks at ABC Company.

So question 1 is how does Excel determine which will be filtered out--in
what order does it work. I'd like to understand which John Hanks would be
determined to be the duplicate.

Question 2 is how do I tell Excel that I only want it to look at the company
name column when determining whether the record is a duplicate.

Thanks so much for any help!

 

[Billy Liddel]

Karen

Try Data, Filter, Advanced Filter, Click Copy to a new place and choose
Unique Records Only.

You could also copy in place, but if I was creating a mailing list I'd feel
safer with the extracted data, but someone will probably know that this is
unnecessary.

One point still examing your data. "John" and "John " with a space are
considered different records because they are not identical.

Peter

 

[Pete_UK]

If you sorted your records by name and by company (assume columns A
and B), then you could introduce a formula like this in a helper
column:

=IF(AND(A2=A1,B2=B1),"duplicate","")

and copy this down. This will only flag duplicate if both the name and
the company name in two adjacent rows are the same. If you wanted to
delete these duplicates, then you could apply autofilter to the helper
column to select "duplicate". Then highlight the visible records and
Edit | Delete Row, and then select "All" from the filter pull-down.

Hope this helps.

Pete

 

[Karen]

Brilliant, thanks Pete!

If you sorted your records by name and by company (assume columns A
and B), then you could introduce a formula like this in a helper
column:

=IF(AND(A2=A1,B2=B1),"duplicate","")

and copy this down. This will only flag duplicate if both the name and
the company name in two adjacent rows are the same. If you wanted to
delete these duplicates, then you could apply autofilter to the helper
column to select "duplicate". Then highlight the visible records and
Edit | Delete Row, and then select "All" from the filter pull-down.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome, Karen - thanks for taking the trouble to feed back.