Duplicates in comparing two columns

I am have a great deal of trouble. I have to compare over 10000 numbers between two columns. I cannot find a formula that will compare the two rows instead of comparing the information in just that one row. My point is say i have 10,000 numbers in one column and 10,000 in another column. I need to compare the two columns and make sure there are no duplicates between them. Can you help?? I have beensearching through threads and i cant find anything that works!

ex:

Col:A Col:B
1 ---------7
2 ---------8
3 ---------9
4 --------12
5 --------2 *
2 --------3 *
3 --------15
4 --------1 *
5 ---------3 *
6 ---------13

how can i find these duplicates without looking through each one?

hi,

Assuming your first row of data is in row 10 & the data is in columns A & B try entering the following formula in C10 & then copying it across to D10 & down to the end of your data:

=if((COUNTIF($A$9:$B9,A10)+COUNTIF($A11:$B$1000,A10))>0,"duplicate exists","unique")

The formula works due to the mix of absolute & relative references by checking if the number/text appears in either the rows above or below the cells own row.

The above seemed to work in a quick test but if it doesn't work for you have a look at Chip's page:

HTML Code: 

hth
Rob

thanks Dean,

I didn't even realise that I was adding extra unnecessary complexity by using the 2 countifs & ">0" until I saw your post - must be my bed time!

Sammy, I'm not sure which is the answer you are after so...
*to count duplicates of a specific value appearing in both columns use (in c1 & copy to D1 & down) :
=IF(COUNTIF($A$1:$B$5578,A1)>1,"duplicate","ok")

*to count dup's of specific values listed in column A that appear in col B:
=IF(COUNTIF($A$1:$a$5578,b1)>0,"duplicate","ok") - as Dean suggested.
or vice versa:
=IF(COUNTIF($b$1:$B$5578,A1)>0,"duplicate","ok")


I put the "ok" in so that you can see that the formulae are working but if you want to prevent visual clutter, just use Dean's empty double quotes "".


Goodluck & goodnight :-)
hth
Rob