Partial Cell Match to an Range of Cells

E

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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
EddieW said:
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=
Click to expand...
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
EddieW said:
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=
Click to expand...
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
You must log in or register to reply here.

Similar threads

Y
  • Question Question
Macro to find & replace from source table - need to match whole source table string to partial string in workbook
Replies
1
Views
361
Kaper
Kaper
O
  • Solved
MS Excel VBA Code to Delete a Row if the Cells Contains a Partial Match for Values Listed in An Array
Replies
6
Views
341
OilEconomist
O
A
  • Question Question
SUMIFS exclude value if partial match in list/named range
2
Replies
18
Views
1K
Alex Blakenburg
A
Dugzilla
  • Solved
Index Match not matching
Replies
8
Views
662
Dugzilla
Dugzilla
M
  • Question Question
Find Match
Replies
1
Views
178
Fluff
Fluff

Forum statistics

Threads
1,223,099
Messages
6,170,114
Members
452,302
Latest member
TaMere

Share this page

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