Partial Cell Match to an Range of Cells

EddieW

New Member
Joined
Nov 9, 2010
Messages
3
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:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Column A[/TD]
[/TR]
[TR]
[TD]tsmith[/TD]
[/TR]
[TR]
[TD]mark.wilson[/TD]
[/TR]
[TR]
[TD]jones, teddy[/TD]
[/TR]
[TR]
[TD]hampton-james-w[/TD]
[/TR]
[TR]
[TD]franklin m. thomas[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Column A[/TD]
[/TR]
[TR]
[TD]Appleton[/TD]
[/TR]
[TR]
[TD]Hampton[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[/TR]
[TR]
[TD]Oppenheimer[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]Veras[/TD]
[/TR]
[TR]
[TD]Wilson[/TD]
[/TR]
</tbody>[/TABLE]

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=
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Column A[/TD]
[/TR]
[TR]
[TD]tsmith[/TD]
[/TR]
[TR]
[TD]mark.wilson[/TD]
[/TR]
[TR]
[TD]jones, teddy[/TD]
[/TR]
[TR]
[TD]hampton-james-w[/TD]
[/TR]
[TR]
[TD]franklin m. thomas[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Column A[/TD]
[/TR]
[TR]
[TD]Appleton[/TD]
[/TR]
[TR]
[TD]Hampton[/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[/TR]
[TR]
[TD]Oppenheimer[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]Veras[/TD]
[/TR]
[TR]
[TD]Wilson[/TD]
[/TR]
</tbody>[/TABLE]

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:

Rich (BB code):
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
 
Upvote 0
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:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]tsmith[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]mark.wilson[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]jones, teddy[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]hampton-james-w[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]franklin m. thomas[/TD]
[TD]false[/TD]
[/TR]
</tbody>[/TABLE]


Is something like that doable?

Thank you again!

=EddieW=
 
Upvote 0
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:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]tsmith[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]mark.wilson[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]jones, teddy[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]hampton-james-w[/TD]
[TD]true[/TD]
[/TR]
[TR]
[TD]franklin m. thomas[/TD]
[TD]false[/TD]
[/TR]
</tbody>[/TABLE]


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:

Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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