Compare two columns, highlight matches

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

Hello,

try this:

Sub DoublonsRapide2col()
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
Set plage1 = Range("A1", [a65000].End(xlUp))
Set plage2 = Range("B1", [B65000].End(xlUp))
[A:B].Interior.ColorIndex = xlNone
For Each c In plage1
If c <> "" Then d1(c.Value) = ""
Next c
For Each c In plage2
If d1.exists(c.Value) Then c.Interior.ColorIndex = 3
If c <> "" Then d2(c.Value) = ""
Next c
For Each c In plage1
If d2.exists(c.Value) Then c.Interior.ColorIndex = 4
Next c
End Sub

patrick
 
Upvote 0
Assuming your data starts in A2:

Select A2:A4>>>>CF>>Use formula to determine which cell to format>> =COUNTIF($A$2:$A$5;B2)
 
Upvote 0
Hello,

try this:

Sub DoublonsRapide2col()
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
Set plage1 = Range("A1", [a65000].End(xlUp))
Set plage2 = Range("B1", [B65000].End(xlUp))
[A:B].Interior.ColorIndex = xlNone
For Each c In plage1
If c <> "" Then d1(c.Value) = ""
Next c
For Each c In plage2
If d1.exists(c.Value) Then c.Interior.ColorIndex = 3
If c <> "" Then d2(c.Value) = ""
Next c
For Each c In plage1
If d2.exists(c.Value) Then c.Interior.ColorIndex = 4
Next c
End Sub

patrick

Thanks Patrick! The macro works like a charm!

EDIT: Whoops, might have spoken a bit too soon. Looks like it didn't catch one of the emails in Column B for some reason when I did a small test.
 
Last edited:
Upvote 0
assuming your data starts in a2:

Select a2:a4>>>>cf>>use formula to determine which cell to format>> =countif($a$2:$a$5;b2)

Sorry, I'm a newbie at this. What do I need to click on in Conditional Formatting to put in the formula?
 
Upvote 0
Sorry a small edit on my formula to:

=NOT(COUNTIF($A$2:$A$5;B2))

Conditional Format>>>New Rule>>>USe formula to determine which cells to format>>> and paste the formula in on the bar under where it says:"Format values where this formula is true"
 
Last edited:
Upvote 0
Sorry a small edit on my formula to:

=NOT(COUNTIF($A$2:$A$5;B2))

Conditional Format>>>New Rule>>>USe formula to determine which cells to format and paste the formula..

Please disregard my previous post, I found how to enter in the formula. However, when I did, it said "The formula you typed contains an error"
 
Upvote 0
Sorry change

=NOT(COUNTIF($A$2:$A$5;B2)) by a ,(comma) like this:

=NOT(COUNTIF($A$2:$A$5,B2))
 
Upvote 0
Sorry change

=NOT(COUNTIF($A$2:$A$5;B2)) by a ,(comma) like this:

=NOT(COUNTIF($A$2:$A$5,B2))

Thank you, the formula now runs, but it doesn't seem to highlight anything. I even tried putting in the Fill color, but all it did was fill all the cells with the color selected.
 
Upvote 0
Sorry don't know what you may be doing wrong, since this works just fine with me..


Book1
AB
1
2abc@gmail.comyst@gmail.com
3ghs@gmail.comyur@gmail.com
4yag@gmail.comabc@gmail.com
5yur@gmail.comtri@gmail.com
Sheet1


 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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
Back
Top