# Highlight cells in 2 columns that have the same values

#### hungledink

##### Board Regular
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?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Eric W

##### MrExcel MVP

Book1
ABC
113
224
331
445
556
652
778
8811
997
10109
1110
12
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:A,C:CExpression=COUNTIF(\$A:\$A,A1)*COUNTIF(\$C:\$C,A1)=1textYES
A:A,C:CExpression=COUNTIF(\$A:\$A,A1)*COUNTIF(\$C:\$C,A1)textYES
A:A,C:CExpression=A1<>""textNO

The order of the rules is important.

#### hungledink

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
That is superb. Thanks a lot for your help Eric W

Replies
1
Views
22
Replies
6
Views
99
Replies
7
Views
334
Replies
2
Views
44
Replies
1
Views
42

1,132,979
Messages
5,656,195
Members
418,289
Latest member
Kjohno

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back