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.

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.
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"Harriet" wrote:

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.

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

Select it all.

DataFilterAdvanced.

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

Delete the original data after copying the unique records.


Gord Dibben MS Excel MVP

On Wed, 20 May 2009 08:43:02 -0700, Harriet
wrote:

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.