Compare two columns, highlight matches

[privatemoon]

Is there a way to compare two columns and highlight all the items in column A that can be found in column B?


For example:


Col A
abc@gmail.com <-highlight
ghs@gmail.com
yag@gmail.com
yur@gmail.com <-highlight


Col B
yst@gmail.com
yur@gmail.com
abc@gmail.com
tri@gmail.com

 

[Caribeiro77]

Does your data starts in A2 as in my screenshot?

 

[privatemoon]

Sorry don't know what you may be doing wrong, since this works just fine with me..

	A	B
1
2	abc@gmail.com	yst@gmail.com
3	ghs@gmail.com	yur@gmail.com
4	yag@gmail.com	abc@gmail.com
5	yur@gmail.com	tri@gmail.com

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1


​

The steps I'm taking are:

Select a cell (A1) (also tried with all cells)
Conditional Formatting
New Rule...
Use a formula to determine which cells to format
Put in: =NOT(COUNTIF($A$2:$A$5,B2))
Press 'OK'

Is there anything that I'm doing wrong?

 

[privatemoon]

Yes, it does, I just put in the headers 'EMAIL1' and 'EMAIL2' for testing purposes. I also tried it with the sample data in my first post.

 

[Caribeiro77]

Select a cell (A1) (also tried with all cells) select the range i said, in this case from A2:A5, then
Conditional Formatting
New Rule...
Use a formula to determine which cells to format
Put in: =NOT(COUNTIF($A$2:$A$5,B2))
Format
Fill
Press 'OK'

Please note that if your data is from A1:A4 select A1:A4 and just have to adapt formula to:
=NOT(COUNTIF($A$1:$A$4,B1))

 

[Caribeiro77]

See the file bellow:

https://www.dropbox.com/home?preview=Conditional+Format.xlsx

 

[privatemoon]

I can't access the file for some reason, it just redirects me to my home folder on Dropbox.

 

[Caribeiro77]

What about this one:

https://www.dropbox.com/s/g99z76lt2z0el0x/Conditional Format.xlsx?dl=0

 

[privatemoon]

Okay, that one works.

EDIT:
I wonder what I'm doing wrong, I've tried doing this following the previous steps I outlined before on two different computers. I might just have to do this manually if nothing else works.

 

[Caribeiro77]

but have you checked out on how's the conditional format was already set it up?

 

[privatemoon]

Yes, for some reason even though it's the same rule, I'm getting a different result on mine.

This is yours:

This is mine: