# Thread: Compare lists by partial match Thanks: 0 Likes: 0

1. ## Compare lists by partial match

Hello,

I have a list of names in 1 table and a list of names in a 2nd table. I want to highlight every line in the second table where the person found in the first table, however there could be extra words added to the names in the second table. Example:

Table 1:
John Doe
Jane Doe
John Smith

Table 2:
Doug Jones
John Doe
Jane Doe iPhone DNS
Rachel Watkins
John Doe 2
James Peterson
Julia Washington Suspended

In this I would want to highlight both John Doe and both Jane Doe lines. Any ideas?

2. ## Re: Compare lists by partial match

Is all the information in Table 2 located in a single column? Cannot determine this from your presentation.

3. ## Re: Compare lists by partial match

Originally Posted by alansidman
Is all the information in Table 2 located in a single column? Cannot determine this from your presentation.

Yes it is

4. ## Re: Compare lists by partial match

Hi,

Select the range in Table 2 you want CF applied.
CF, use Formula, enter:

=ISNUMBER(LOOKUP(2,1/SEARCH(A\$2:A\$4,A7)))

Select Format fill/font as desired.

5. ## Re: Compare lists by partial match

Assume table1 in sheet1, table2 in sheet2. All data in Column A. Here is a VBA solution.

Code:
```Option Explicit

Sub FindDupe()
Dim s1 As Worksheet, s2 As Worksheet
Dim i As Long, j As Long
Dim lr As Long, lr2 As Long
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
lr = s1.Range("A" & Rows.Count).End(xlUp).Row
lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
For j = 2 To lr2
If InStr(s2.Range("A" & j), s1.Range("A" & i)) > 0 Then
s2.Range("A" & j).Interior.ColorIndex = 4
End If
Next j
Next i

End Sub```
Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

6. ## Re: Compare lists by partial match

If you have table1 in sheet1 and table2 in sheet2.

Select sheet1
Select the names of the sheet1 and in the Name box type: Names

See the following example:

Now select sheet2
Select the list of names.

Then selet Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
Under Format values where this formula is true, type the formula:

Code:
`=SUM(--ISNUMBER(SEARCH(IF(Names<>"",Names),A2)))`
Click Format.
In the Color box, select the desired color.
Click OK
Click OK

The formatting is applied to column A.

 Sheet2

 A 2 Doug Jones 3 John Doe 4 Jane Doe iPhone DNS 5 Rachel Watkins 6 John Doe 2 7 James Peterson 8 Julia Washington Suspended 9 Jane Doe iPad DNS

------

Let me know if you have any doubt.

7. ## Re: Compare lists by partial match

Thanks! This worked perfectly

8. ## Re: Compare lists by partial match

Originally Posted by jpencek3
Thanks! This worked perfectly