I just had to invent something quickly for a co-worker today.
He needed to check a long list of phrases in two different columns to see if there were any matches.
e.g. “pasta carbonara” vs “Spaghetti Carbonara” , this should be a match.
So without further ado, here’s the VBA function to do it.
Function PartialMatch(Cell1 As Range, Cell2 As Range) ' Convert both cells' contents to lowercase Dim sC1 As String: sC1 = LCase(Cell1.Value) Dim sC2 As String: sC2 = LCase(Cell2.Value) ' Split the words by space into an array Dim C1() As String: C1 = Split(sC1, Chr(32)) Dim C2() As String: C2 = Split(sC2, Chr(32)) ' Assume that there's no match Dim sResult As String: sResult = "No match" ' Loop through the words in both cells For Each sWord1 In C1 For Each sWord2 In C2 ' Compare the words to each other If (sWord1 = sWord2) Then sResult = "Match" End If Next sWord2 Next sWord1 ' Return the result PartialMatch = sResult End Function
Say you have A1 = “pasta carbonara” and B1 = “Spaghetti Carbonara”.
Insert =PartialMatch(A1,B1) in C1 and the function will return “Match”.
Apply some conditional formatting for =”Match” and =”No match” to highlight.