Compare lists by partial match

[jpencek3]

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
Jane Doe iPad DNS

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

 

[alansidman]

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

 

[jpencek3]

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

Yes it is

 

[jtakw]

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.

Change/adjust A$2:A$4 according to your data in Table 1.
Change/adjust A7 according to your First cell in Table 2.

 

[alansidman]

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

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

 

[DanteAmor]

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:

=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.

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet2</b></td></tr></table>
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:187.25px;" /></colgroup><tr style="background-color:#ACC5EF; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#ACC5EF; text-align:center; " >2</td><td >Doug Jones</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#ACC5EF; text-align:center; " >3</td><td style="background-color:#ffff00; ">John Doe</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#ACC5EF; text-align:center; " >4</td><td style="background-color:#ffff00; ">Jane Doe iPhone DNS</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#ACC5EF; text-align:center; " >5</td><td >Rachel Watkins</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#ACC5EF; text-align:center; " >6</td><td style="background-color:#ffff00; ">John Doe 2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#ACC5EF; text-align:center; " >7</td><td >James Peterson</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#ACC5EF; text-align:center; " >8</td><td >Julia Washington Suspended</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#ACC5EF; text-align:center; " >9</td><td style="background-color:#ffff00; ">Jane Doe iPad DNS</td></tr></table>


------

Let me know if you have any doubt.

 

[jpencek3]

Thanks! This worked perfectly

 

[DanteAmor]

Thanks! This worked perfectly

I'm glad to help you. Thanks for the feedback.