Highlight cells in 2 columns that have the same values

hungledink

Board Regular
Joined
Feb 20, 2012
Messages
79
Office Version
  1. 365
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?

1583360012886.png
 

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
Joined
Aug 18, 2015
Messages
10,959
How about:

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
Joined
Feb 20, 2012
Messages
79
Office Version
  1. 365
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
Joined
Aug 18, 2015
Messages
10,959
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top