Count Exact Match using Excel VBA

Question

I am trying to find the exact matching word using Excel VBA, but failed to do so as either due to case sensitivity or partial match.

Here is my data

Experience Column contains certain keywords and I am extracting those keywords based on master list

The problems in Result are It is showing UI2 means, UI 2 times, but as we can see in experience it is only 1 time

Same with GO, it shows 2 : One from Go and other from Google

NoSQL has been extracted into NoSQL and SQL, however there were two different skill set: NoSQL and SQL and since the experience doesn't have SQL, it shouldn't be extracted

There is a skill set called "R" in master file, it was difficult to extract particular R as it accounts for every R

Here is my code snip

I have read so many articles, but didn't find appropriate solution. Kindly help.

Thanks

Please do not post pictures of your code. Instead, post the code itself so people can copy/paste it if they want to. — braX, Mar 15 '21 at 00:04

score 0 · Answer 1 · answered Mar 15 '21 at 01:36

0

You need to account for word boundaries if you don't want "Go" to (eg) match "Google". You might be better off using a regex approach to find matches.

Note: to avoid matching "Go" with "go" you need the match to be case-sensitive, but to avoid misses on other terms you might need to pass all possible case variants like (eg) "mySQL|MySQL".

Sub matchTester()
    
    Dim s As String, skill
    
    s = "I go to school now and have used R, MySQL, noSQL and " & _
        "SQL and Go in my Ratings job at Google. I like R"
    
    For Each skill In Array("SQL", "noSQL|NoSQL", "mySQL|MySQL", "R", "Go", "VBA", "C#")
        Debug.Print skill, CountMatches(s, skill)
    Next skill

End Sub

Function CountMatches(sIn As String, countThis) As Long
    Dim regEx As Object, matches As Object
    With CreateObject("vbscript.regexp")
        .Global = True
        .IgnoreCase = False
        .Pattern = "\b(" & countThis & ")\b" 'add word boundaries
        Set matches = .Execute(sIn)
    End With
    CountMatches = matches.Count
End Function

output:

SQL            1 
noSQL|NoSQL    1 
mySQL|MySQL    1 
R              2 
Go             1 
VBA            0 
C#             0

answered Mar 15 '21 at 01:36

Tim Williams

154,628
8
97
125

If you want to match "Go" but not "go" then the regex needs to be case-sensitive. If you don't want to make it case-sensitive then you need to (eg) filter out "go" from the results. What's best really depends on your exact setup and how you maintain your list of search terms. – Tim Williams Mar 15 '21 at 22:53
Thank you Tim for you response. I am using Lcase or Ucase for case sensitivity But this code throughs error if the skill is C++. It is saying Application/object defined error – Ankit Gupta Mar 15 '21 at 23:00
Can you please explain what basically happning at Set matches = .Execute(sIn). Everything is clear. Much helps Thanks! – Ankit Gupta Mar 15 '21 at 23:03
`Execute` is where you pass in the string to be analysed - you've already set up the regex object with a pattern to search for (and other settings such as ignore case), so this is the point where it tries to match the pattern and generate 0 or more "matches" which are returned to the `matches` collection. – Tim Williams Mar 16 '21 at 00:00

Count Exact Match using Excel VBA

1 Answers1