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.
Archive for the ‘Excel’ Category
Excel VBA: Function to compare words in two cells
27
Mar
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.
Excel how-to: Color an entire line with conditional formatting
09
Mar
Sometimes you want to highlight a row in a table depending on say, a category in the B-column.
Here’s how to do it:
- Add a new conditional formatting rule to the table
- Select “Use a formula to determine which cells to format”
- Enter the formula =IF(INDEX($B:$B;ROW();1)=”Misc”;TRUE;FALSE)
Note: You might have to use a colon instead of a semi-colon as a separator, depending on your Excel version. - Click Format and select your color of choice
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.
