Find all Partial matches in Sheet 1 Column D that match names in Sheet 2 Column A

westc4

Board Regular
Joined
Aug 1, 2012
Messages
89
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am trying to tweak the following VBA code. What I need to happen is for excel to look at the list of names in Column "A" Sheet "Name" and highlight all cells in Column "D" on Sheet "Match" that match the "A" list, a partial name match should be highlighted.

Both the sheets ("Name" and "Match") can have a different number of rows that can vary, so I also need to make sure it searches all names and reviews for all possible matches.

It currently is not finding anything, though there are a number or direct matches and more than 1,000 partial matches.

Code:
Sub CheckNames()
Dim Cell As Range, cRange As Range, sRange As Range, Rng As Range, FindString As String

LastRow1 = Sheets("Match").Cells(Rows.Count, "D").End(xlUp).Row
LastRow2 = Sheets("Name").Cells(Rows.Count, "A").End(xlUp).Row

Set cRange = Sheets("Match").Range("D3:D" & LastRow1)
Set sRange = Sheets("Name").Range("A1:A" & LastRow2)

    For Each Cell In cRange
        FindString = Cell.Value
    With sRange
         Set Rng = .Find(What:=FindString, _
                    After:=.Cells(1), _
                    LookIn:=xlValues, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
            If Not Rng Is Nothing Then
                Rng.Interior.ColorIndex = 6
                    Cell.Interior.ColorIndex = 6
            End If
    End With
    Next
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
If you're willing to look at other solutions, these sorts of tasks are much simpler in Parabola (https://parabola.io/). It would take you just a couple minutes to import your two datasets and tell it what columns to look at, then it can do direct or fuzzy matching to give you rows that are, or are not, contained in the other list. You can also set up any other steps and automate everything going forward without needing to write VBA code.
 
Upvote 0
Your code appears to be looking to match each cell on the sheet "Match" to the first cell, and only the first cell, from the bottom of the list on sheet "Names" it can find, and if it finds a cell to color it. That seems to be the reverse of the process you describe in the text of your post.

Can you clarify which sheet is to be matched and colored?
Can there be more than one match for any specific cell?
Can you post some sample data from both sheets so we can see what types of partial matches you are dealing with?
 
Upvote 0
No data to test against... but how about this?

Code:
Sub CheckNamesV2()
Dim rn As Range, rs As Range
Dim rName As Range, rSearch As Range
Dim LastRow1&, LastRow2&


LastRow1 = Sheets("Match").Cells(Rows.Count, "D").End(xlUp).Row
LastRow2 = Sheets("Name").Cells(Rows.Count, "A").End(xlUp).Row


Set rSearch = Sheets("Match").Range("D3:D" & LastRow1)
Set rName = Sheets("Name").Range("A1:A" & LastRow2)


For Each rn In rName
    For Each rs In rSearch
        If rn.Value Like "*" & rs.Value & "*" Then
            rs.Interior.ColorIndex = 6
            rn.Interior.ColorIndex = 6
        End If
    Next rs
Next rn
End Sub
 
Last edited:
Upvote 0
Your code appears to be looking to match each cell on the sheet "Match" to the first cell, and only the first cell, from the bottom of the list on sheet "Names" it can find, and if it finds a cell to color it. That seems to be the reverse of the process you describe in the text of your post.

Can you clarify which sheet is to be matched and colored?
Can there be more than one match for any specific cell?
Can you post some sample data from both sheets so we can see what types of partial matches you are dealing with?

That would explain the issues I am having :eek:

I would like the all cells in "Match" to be highlighted when any "Name" cells are found on "Match". There are over 100,000+ lines of data in "Match"

Data from "Name"
VAMC
VA
Veterans Health Administration
Veterans Admin
Veteran Administration

<colgroup><col></colgroup><tbody>
</tbody>


Data from "Match"
589 Vamc Wichita
Blocked- VAMC 501 Alburquerque
Blocked- Vamc 662
Blocked- VAMC 663 Seattle
Blocked- VAMC 691 Los Angeles
Department of Veteran Affairs
Department Of Veteran Affairs
Department of Veteran Affairs
Grand Rapids Home For Veterans
Shilog (VAMC 506 ANN ARBOR)
Shilog (VAMC 523 Boston)
VAMC 160 Dental Services
VAMC 437 Fargo
Veterans Affairs

<colgroup><col></colgroup><tbody>
</tbody>
 
Upvote 0
I have to go offline now. Try the solution in post #4 after you remove this line: "rn.Interior.ColorIndex = 6" from it. If it is painfully slow or doesn't work, post back and I will try to give you a faster solution when I return. Be aware that accepting partial matches can result in some surprises. For example, "Shilog Advanced Operations", if present, would be colored unless you specify that the search for "VA" should be case sensitive. Do you want the search to be case sensitive?
 
Upvote 0
Here's a fix to post 4

Code:
[COLOR=#ff0000][B]Option Compare Text[/B][/COLOR]


Sub CheckNamesV2()
Dim rn As Range, rs As Range
Dim rName As Range, rSearch As Range
Dim LastRow1&, LastRow2&

[COLOR=#ff0000][B]Application.ScreenUpdating = False[/B][/COLOR]
LastRow1 = Sheets("Match").Cells(Rows.Count, "D").End(xlUp).Row
LastRow2 = Sheets("Name").Cells(Rows.Count, "A").End(xlUp).Row


Set rSearch = Sheets("Match").Range("D3:D" & LastRow1)
Set rName = Sheets("Name").Range("A1:A" & LastRow2)


For Each rn In rName.Cells
    For Each rs In rSearch.Cells
        If [B][COLOR=#ff0000]UCase(rs.Value) Like "*" & UCase(rn.Value) & "*"[/COLOR][/B] Then
            rs.Interior.ColorIndex = 6
            rn.Interior.ColorIndex = 6
        End If
    Next rs
Next rn
[COLOR=#ff0000][B]Application.ScreenUpdating = True[/B][/COLOR]
End Sub
 
Last edited:
Upvote 0
Post #7 works :)

It is a little slow, but not bad! I can make adjustments to my name list also, but if you are able to provide a case sensitive search that would be wonderful and a great thing to learn!

Thank you so very much to everyone that helped me out!
 
Upvote 0
@westc4

To make it Case Sensitive, take the Option Compare Text out and the UCase parts out.

You may be able to speed it up by using a UNION function to compile a range to be highlighted (Instead of doing so within the loop) and highlight the non-contiguous range in one shot.
 
Upvote 0
@westc4

To make it Case Sensitive, take the Option Compare Text out and the UCase parts out.

You may be able to speed it up by using a UNION function to compile a range to be highlighted (Instead of doing so within the loop) and highlight the non-contiguous range in one shot.
In my experience creating the union takes about as long as color-filling a cell, so this may not be very effective. If I wanted to speed things up, I would use Application.Match to screen for any match first. If there is a match then use the Find and FindNext methods to color fill specific cells that match w/o having to loop through all 100,000+ cells on the "Match" sheet.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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