No duplicates in 2 columns on 2 different worksheets.

[clayton]

I have a column on each of two different worksheets in the same workbook that I want to make sure there are no duplicates. Meaning that if I enter the number 332 it will look to see if that number exists anywhere in both of the sheets and return an error if there is a duplicate. Is there an easy way to do something like this?

[robert111]

use countif(range1,773) + countif(range2,773)

if no match it will return zero.

Or is it more complicated?

[robert111]

=if(countif(range1,773)>0,if(countif(range2,773>0,"duplicate exists","no duplicate exists"))

[clayton]

Thanks for your response

What you have posted will work for showing text "Duplicate exists" in a cell I think, but I want to show a message and disallow the ability to let this happen. Is there any way to show a popup if the condition is true?

[Carim]

Hi Clayton,

With this objective in mind, you have to use Data Validation
Custom Formula

Please Login or Register  to view this content.

Adjust formula and ranges to your needs ...

HTH
Carim

[clayton]

Sorry for the long delay in my reply... I am working on several projects at the moment. I will definitely try your suggestion soon.

Im not entirely sure what this is doing though...

Please Login or Register  to view this content.

It looks to me as though it is comparing only one cell in each page C2 and D2. If that is the case then I don't see how this could work. Even if I copy this all the way down the page it will still only be comparing C2 to the range C2:C200 and D2 to the D2:D200 range. How will it compare C2 to the D2:D200 range and vice versa?
SUMPRODUCT is a very cool thing and I am trying to understand all it can do. It's just the logic I see that doesn't make sense to me. Can you help me understand?

One other this I failed to mention is that I already have formula in the C2:C200 cells so can I still use your code?