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?
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,690
Office Version
2019
Platform
Windows
Is all the information in Table 2 located in a single column? Cannot determine this from your presentation.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,690
Office Version
2019
Platform
Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,207
Office Version
2007
Platform
Windows
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.

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

Watch MrExcel Video

Forum statistics

Threads
1,102,643
Messages
5,488,074
Members
407,622
Latest member
plantaddict

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top