Comparing Strings

[towely321]

How can I write a macro so that when I compare two cells that are like this
BB ; AA+
and
AA+ ; BB

I can say they are equal. The format of the cells are always the same. Some letters, 1 space, a semicolon, 1 space and some more letters.

[SamT]

"Split" will turn the strings into an array
Dim strCelOne As Variant
'Or ,shorter but same result
Dim strCelTwo

strCelOne = Split(Range("whatever"), " ; ")

Then you need to loop thru each Array Variable,(strCel One & Two) to compare all items with all other items,

Dim i As Long, j As long
For i = lBound(strCelOne) to Ubound(strCelOne)
For j = i to Ubound(strCelTwo)
If strCelOne(i) = strCelTwo(j) THen
'Do something
End If
Next j
Next i

"Do Something" is the confusing part. First we have to "remember" if an item in strCelTwo matched any item in strCelOne, but if it didn't match any item, we immediately know that the two Ranges aren't "equal."
Dim Found As Boolean
Dim Failed As Boolean

"Do Something"
Found = False ' Need to reset this for each loop thru strCelOne
'For j blah, blah
If strCelOne(i) = strCelTwo(j) Then
Found = True
Exit For 'Found a match, don't need to check any more items in strCelTwo.
End If
'If we are still in this loop, then no match was found
Failed = True
Exit Sub 'We're done. At least one item in strCelTwo did not match
Next j
Next i
End Sub

That is really all the code you need to determine if the two cells are NOT "equal." If they're are Not unequal then they are ? However it is Best Practice to put all this code into a FUnction, that can be used by the sub that actually loops thru the Rows and does to the cells, what ever you need doing.


Function VBAX_SamT_CellsMatch(rngCelOne As Range, rngCelTwo As Range) As Boolean
'Returns True if Cells have matching substrings in them
'For Help See: http://www.vbaexpress.com/forum/showthread.php?54118

Dim CelOne, CelTwo
Dim i As Long, j As long

'Check if Same number of Sub Strings
If Ubound(srCelOne) <> Ubound(strCelTwo) Then
VBAX_SamT_CellsMatch = False
Exit Function
End If

For i = lBound(strCelOne) to Ubound(strCelOne)
For j = i to Ubound(strCelTwo)
If strCelTwo(j) = strCelOne(i) Then
GoTo iNext 'Found a match, don't need to check any more items in strCelTwo
Else
'If we are here, then a no match sub-String was found
VBAX_SamT_CellsMatch = False
Exit Function
End If
Next j
iNext:
Next i 'Compare next item in strCelOne

'If we are still here, then everything matched up
VBAX_SamT_CellsMatch = True
End Function


Sub LoopingthruRows()
Dim rw As Long
For rw = 1 to Cells(RowsCount, "A").End(xlUp).Row
If Not VBAX_SamT_CellsMatch(Cells(rw, "A"), Cells(rw, "B")) Then
'Do Something
End If
Next rw
End Sub

[towely321]

Thank you for your reply. I'm sure it works wonderful, but I found another, shorter solution.

[snb]

Shorter than ?

Sub M_snb()
c00="BB ; AA+"
c01="AA+ ; BB"
msgbox instr(c01 & " ; " & c01,c00)>0
End Sub

And please, be polite and post that solution here....

[towely321]

Thank you! That's quite clever and it worked for about 90% of the data. However, when it compared A+ ; A with A ; A it results to "True". But I'm sure that's just due to you not knowing what the entire database looks like.


If Mid(z, 1, InStr(1, z, ";") - 2) = Mid(x, InStr(1, x, ";") + 2, InStr(1, z, ";") - 2) _
And Mid(x, 1, InStr(1, x, ";") - 2) = Mid(z, InStr(1, z, ";") + 2, InStr(1, x, ";") - 2) _
Then

This is what I came up with.

[snb]

or:


c00 = "A ; A"
c01 = "A ; A+"
MsgBox Split(c00, " ; ")(1) & " ; " & Split(c00, " ; ")(0) = c01

[towely321]

Works brilliantly, thanks a lot!

[snb]

Based on the original approach:


MsgBox InStr(c01 & " ; " & c01, " ; " & c00 & " ; ") > 0