comparing multiple columns for list of items Closed

Question

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.

TrowaD · Answer

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 IntegerlRow = Range("D1").End(xlDown).RowFor 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 WithNext cellEnd Sub
 
Best regards,
Trowa

Protocoder · Answer

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 · Answer

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

Comparing multiple columns for list of items

3 responses

Similar discussions

Subscribe To Our Newsletter!