Comparing multiple columns for list of items

Closed
protocoder Posts 1 Registration date Tuesday November 19, 2013 Status Member Last seen November 19, 2013 - Nov 19, 2013 at 04:23 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 21, 2013 at 10:20 AM
Dear experts

I have list of names with multiple words in column A

List of names with multiple words in column B

List of names with multiple words in column C

In Column D, I have a list of names which Needs to be compared if they exist in either A, B or C. If so highlight them with Yellow or Red.

Because the list in Column D have some names changed/spell changed/Deleted.. they should not exist any more.
Note: even a single letter difference like some names have added/deleted (s) in the end have to be filtered out. Please can you help me.
Note 2: In some searches I have only column to compare and some have 2 and some I have 3 columns .. Please appreciate your help.

3 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Nov 19, 2013 at 10:56 AM
Hi Protocoder,

The following code will loop through column D.
When a partial match is found in the pre set range the cell from column D will turn red.

Here is the code:
Sub RunMe()
Dim lRow As Integer
lRow = Range("D1").End(xlDown).Row

For Each cell In Range("D1:D" & lRow)
With Range("A1:C9") 'Make sure this range is conform yours
Set c = .Find(cell, LookIn:=xlValues)
If Not c Is Nothing Then cell.Interior.ColorIndex = 3
End With
Next cell

End Sub

Best regards,
Trowa
Thank you Trowa.

I have read and also executed. I must apologize for the confusion.
There must be exact match not a letter more or a letter less. The reason being in the new version there has been changes in the names and basically testing when I run the report, all historical and new reports point to the changed name not the old name. Thank you for your time.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Nov 21, 2013 at 10:20 AM
Hi Protocoder,

Then you will have to change the following line:
Set c = .Find(cell, LookIn:=xlValues)
into:
Set c = .Find(cell, LookIn:=xlValues, lookat:=xlWhole)

Best regards,
Trowa