Find duplicates in Excel 2000

H

Harriet

I am going to put two spreadsheets together--one from my database and one
from another company's database. We both have a the same unique ID for
members. I want to be able to find all of the duplicate ID's and I don't
know how to write a formula to do that. My goal is to have all of the
duplicate records say 1 or true and all none duplicate records to say 2 or
false; or if you have a better idea, I'm up for suggestions. I want to be
able to sort all of the 1's or true and the 2's and false so that I only need
to look at the records that are NOT duplicates.
 
P

PJFry

The easiest way is to combine your two lists and sort them the unique number.
Then use this formula:
=IF(B1=A1,1,2)

Duplicates will return a 1, non-duplicates will return a 2.

If you cannot combine the records, you can do a VLOOKUP of one list against
the other.

Post back and let me know how that works or if you need clarification.
 
G

Gord Dibben

Copy the data from book2 into book1 so's all data is same sheet.

Select it all.

Data>Filter>Advanced.

Tick "unique records only" and "copy to another range"

Delete the original data after copying the unique records.


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top