Compare lists by partial match
Results 1 to 8 of 8

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

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,860
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare lists by partial match

    Is all the information in Table 2 located in a single column? Cannot determine this from your presentation.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    New Member
    Join Date
    Feb 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare lists by partial match

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

    Yes it is

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

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

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

  5. #5
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,860
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,263
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default 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
    2Doug Jones
    3John Doe
    4Jane Doe iPhone DNS
    5Rachel Watkins
    6John Doe 2
    7James Peterson
    8Julia Washington Suspended
    9Jane Doe iPad DNS



    ------

    Let me know if you have any doubt.
    Regards Dante Amor

  7. #7
    New Member
    Join Date
    Feb 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare lists by partial match

    Thanks! This worked perfectly

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,263
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Compare lists by partial match

    Quote Originally Posted by jpencek3 View Post
    Thanks! This worked perfectly
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •