Excel function to count the number of matching words in two cells (‘fuzzy matching’)


I had a requirement to do a ‘fuzzy match’ of two columns of data, based on the number of matching words in each.

Add the following code to your Visual Basic module, and then in your Excel spreadsheet, do something like

=FuzzyMatch(A1,B1)

Enjoy!

Public Function FuzzyMatch(name1 As String, name2 As String) As Integer

    Dim strings1() As String
    Dim strings2() As String

    Dim string1 As Variant
    Dim string2 As Variant

    Dim matches As Integer

    name1 = StrippedName(name1)
    name2 = StrippedName(name2)

    strings1 = Split(name1, " ")
    strings2 = Split(name2, " ")

    matches = 0

    For Each string1 In strings1
        For Each string2 In strings2
            If LCase(string1) = LCase(string2) Then
                matches = matches + 1
            End If
        Next
    Next

    StoreMatch = matches
End Function

Public Function StrippedName(name As String)
    name = Replace(name, ",", " ")
    name = Replace(name, ".", " ")
    name = Replace(name, "  ", " ")
    name = Replace(name, "  ", " ")

    StrippedName = name
End Function
Advertisements

About saasmd

I am an experienced software-as-a-service entrepreneur, based in London, UK. I love building interesting software businesses. My current venture is StorIQ, a platform to help bricks-and-mortar retailers manage their operations more effectively. This blog is a space to share low-level techie stuff that I think other people will find useful.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s