Flag Duplicate Cells

Hello,

I am trying to flag duplicate values between two columns, I do not want to flag a column if it has duplicates within itself, only if it has a duplicate from another column that I specified. I've been struggling with this for a while. Here's an example -


A B
9 9
12 9
16 5
18 18


What's happening are the the cells in Column be are getting flagged when I use conditional formatting --> highlight cell rules --> duplicates, because technically they are duplicates. But I only want to flag cells that have a duplicate from column A and column B, not if column A or column B have duplicates within its own column.

Any help is much appreciated !!!

Edit: I should have specified better. Here's a better example -

I'm going to try to attach a picture.. :
Duplicates.png

As you can see, it's flagging number 32 because its in column B twice, but 32 is not in column A. I do not want to flag 32, just unique duplicates between Column A and Column B.

For column A vals found in Col B:
Select Column A, then CF with.

Please Login or Register  to view this content.

You need a second (similar) formula to highlight col B vals found in Col A

Ben I think you left out the test part of your countif (it will flag all counts >= 1)

=COUNTIF($B$1:$B$4,$A1)>1

=COUNTIF($B$1:$B$4,$A1) returns zero/False when A1 val not in B, otherwise: True

Originally Posted by protonLeah

=COUNTIF($B$1:$B$4,$A1) returns zero/False when A1 val not in B, otherwise: True

I stand corrected, I mis-read the Q, thought they wanted all dups flagged