Partial Cell Match to an Range of Cells

[EddieW]

This is totally backward from the usual way of doing things. I'm trying to find who is in a list and their names are in all different formats. In workbook 1, I have a list of full names that looks like this:

Column A

tsmith

mark.wilson

jones, teddy

hampton-james-w

franklin m. thomas

<tbody>
</tbody>

In workbook 2, I have a list of last last names:

Column A

Appleton

Hampton

Jones

Oppenheimer

Smith

Veras

Wilson

<tbody>
</tbody>

Not all names in workbook 1 will be in workbook 2 and vice versa.

How can I compare the partial contents of the cells in workbook 1 with the range of cells containing the last names in workbook 2 so that I can get a true if workbook 1 has a match in workbook 2?

It's a real head scratcher, for sure!

Thank you for any insight!

=EddieW=

 

[Fishboy]

This is totally backward from the usual way of doing things. I'm trying to find who is in a list and their names are in all different formats. In workbook 1, I have a list of full names that looks like this:

Column A

tsmith

mark.wilson

jones, teddy

hampton-james-w

franklin m. thomas

<tbody>
</tbody>

In workbook 2, I have a list of last last names:

Column A

Appleton

Hampton

Jones

Oppenheimer

Smith

Veras

Wilson

<tbody>
</tbody>

Not all names in workbook 1 will be in workbook 2 and vice versa.

How can I compare the partial contents of the cells in workbook 1 with the range of cells containing the last names in workbook 2 so that I can get a true if workbook 1 has a match in workbook 2?

It's a real head scratcher, for sure!

Thank you for any insight!

=EddieW=

Hi EddieW,

Try out the following code in a COPY of your workbook. This assumes that the 2 lists are on separate sheets within the same workbook (sheets 1 and 2), although it could easily be tweaked to either use pre-defined sheet names, or to compare 2 different workbooks if needed. We would just need to know the workbook and sheet names you are using:

Sub CheckNames()
' Defines variables
Dim Cell As Range, cRange As Range, sRange As Range, Rng As Range, FindString As String
' Defines LastRow of Sheet1 based on column A
LastRow1 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
' Defines LastRow of Sheet2 based on column A
LastRow2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
' Set the range to check from as Sheet2, A1 to the last row of A
Set cRange = Sheets("Sheet2").Range("A1:A" & LastRow2)
' Set the range to search in as Sheet1, A1 to the last row of A
Set sRange = Sheets("Sheet1").Range("A1:A" & LastRow1)
' For each cell in the check range
    For Each Cell In cRange
' String to find equals cell value
        FindString = Cell.Value
' With the search range
                With sRange
' Set Rng as the cell where the value is found
                    Set Rng = .Find(What:=FindString, _
                                    After:=.Cells(1), _
                                    LookIn:=xlValues, _
                                    LookAt:=xlPart, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlPrevious, _
                                    MatchCase:=False)
' If Rng exists then
                        If Not Rng Is Nothing Then
' Highlight the matching cells in yellow on both sheets
                            Rng.Interior.ColorIndex = 6
                                Cell.Interior.ColorIndex = 6
                        End If
                End With
' Move to next cell in check range
    Next Cell
End Sub

 

[EddieW]

Thank you Fishboy -- very nice! It worked to highlight the cells nicely and I learned some code to boot!

My apologies, but I should have added that I would like the true to be in column B of workbook 1 if the lastname is found in workbook 2 and my table would look like this:

Column A	Column B
tsmith	true
mark.wilson	true
jones, teddy	true
hampton-james-w	true
franklin m. thomas	false

<tbody>
</tbody>

Is something like that doable?

Thank you again!

=EddieW=

 

[Fishboy]

Thank you Fishboy -- very nice! It worked to highlight the cells nicely and I learned some code to boot!

My apologies, but I should have added that I would like the true to be in column B of workbook 1 if the lastname is found in workbook 2 and my table would look like this:

Column A	Column B
tsmith	true
mark.wilson	true
jones, teddy	true
hampton-james-w	true
franklin m. thomas	false

<tbody>
</tbody>

Is something like that doable?

Thank you again!

=EddieW=

Hi again Eddie,

Glad to hear the basic premise of this worked. It is easy enough to stop the yellow highlighting and add "True" to the adjacent cells on workbook 1 like so:

Sub CheckNames()
' Defines variables
Dim Cell As Range, cRange As Range, sRange As Range, Rng As Range, FindString As String
' Defines LastRow of Sheet1 based on column A
LastRow1 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
' Defines LastRow of Sheet2 based on column A
LastRow2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
' Set the range to check from as Sheet2, A1 to the last row of A
Set cRange = Sheets("Sheet2").Range("A1:A" & LastRow2)
' Set the range to search in as Sheet1, A1 to the last row of A
Set sRange = Sheets("Sheet1").Range("A1:A" & LastRow1)
' For each cell in the check range
    For Each Cell In cRange
' String to find equals cell value
        FindString = Cell.Value
' With the search range
                With sRange
' Set Rng as the cell where the value is found
                    Set Rng = .Find(What:=FindString, _
                                    After:=.Cells(1), _
                                    LookIn:=xlValues, _
                                    LookAt:=xlPart, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlPrevious, _
                                    MatchCase:=False)
' If Rng exists then
                        If Not Rng Is Nothing Then
' Update the adjacent cell with "True"
                            Rng.Offset(0,1).Value = "True"
                        End If
                End With
' Move to next cell in check range
    Next Cell
End Sub