Highlight cells in 2 columns that have the same values

[hungledink]

I have 2 columns of data in an excel workbook. The columns both caontain values although the number of rows in each column woud be different.#

I want to compare the 2 rows for identical data which I've been able to do using the conditional formatting and highlighting duplicates.

I have an issue, however, in that the same value may appear more than once in either column. If this happens then I wanted that highlighted in another colour.

I've attached an image with the result I'm after, where cells highlighted yellow, appear in both columns but only once. Cells highlighted in blue appear multiple times in either column, but not the same number of times in the other column. And then the green cell is a value that only appears in one of the 2 columns.

Can anyone help?

 

[Eric W]

How about:

Book1
	A	B	C
1	1		3
2	2		4
3	3		1
4	4		5
5	5		6
6	5		2
7	7		8
8	8		11
9	9		7
10	10		9
11			10
12
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A,C:C	Expression	=COUNTIF($A:$A,A1)*COUNTIF($C:$C,A1)=1	text	YES
A:A,C:C	Expression	=COUNTIF($A:$A,A1)*COUNTIF($C:$C,A1)	text	YES
A:A,C:C	Expression	=A1<>""	text	NO

The order of the rules is important.

 

[hungledink]

That's 99% of what I need.

Theres one scenario that doesn't work as I'd intended though.

If there are for example 2 instances of the value 1 in column a, and 2 instances of the same value in column c, I'd like those to show as yellow as well. In this solution they show as blue.

Is that something you can allow for?

 

[Eric W]

Sure, just change the first formula to:

=AND(COUNTIF($A:$A,A1)=COUNTIF($C:$C,A1),A1<>"")

If the number of instances for a given value is the same in both columns, it will show as yellow.

 

[hungledink]

That is superb. Thanks a lot for your help Eric W ?

 

[Eric W]

Glad to help! ?