Compare lists by partial match

jpencek3

New Member
Joined
Feb 22, 2018
Messages
16
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Is all the information in Table 2 located in a single column? Cannot determine this from your presentation.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:

9457fba7adb54f75ed29843c4566397f.jpg


Now select sheet2
Select the list of names.

2a68d43250438e9f1535bd57f4a6ba0f.jpg


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.

<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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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