Once every now and then you might ask yourself: “Is this going to be a good day?”
Then you arrive at work, fire up Excel and … *Poof!*
…and that was that.
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.
Sometimes you want to highlight a row in a table depending on say, a category in the B-column.
What the formula does is use the INDEX-function (the arguments are: array, row, column) to lookup the value on the current row number (as returned by the ROW-function) in the B-column (using number 1, because the array is just 1 column wide). The IF-function just makes sure TRUE or FALSE are returned so the rule knows when to apply the color.
Then you just add more rules to the table for the different categories you need to color.