Conditional Formatting Query

[TriciaW]

I need help comparing two columns for duplicates and unique values. I know I can do this using conditional formatting however my issue is that for example in column 1 I have duplicates so when I apply the conditional formatting it is picking the duplicates up in the same column however I only want to pick up the duplicates against the two columns. E.g.

Column 1 could have:
Red
Yellow
Red
Green

Column 2 could have:
Yellow
Blue
Purple
Green

When I apply the conditional formatting it picks up Red as a duplicate as it appears twice in column one but I only want to find duplicates comparing the two columns so I would like it to pick up: Yellow and Green.

How can I achieve this?

[Richard Buttrey]

Hi, and welcome to the forum.

This seems to be a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length, or additional columns/rows and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see.

[6StringJazzer]

Welcome to the Forum TriciaW!

Let's say column 1 is column A and column 2 is column B.

These rules will show if a value in one column is found in the other column:

Use a conditional formatting rule applied to column A that says:
=COUNTIF(B:B,A1)>0

Use a conditional formatting rule applied to column B that says:
=COUNTIF(A:A,B1)>0