Find duplicates in Excel 2000

[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.

 

[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.

 

[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