0

I need to compare the following

Cell 1         John Peter henderson                          
Cell 2         peter John Henderson                
Result         Match

Cell 1         Anne jolie beuhler             
Cell 2         Jolie Anne
Result         NO MATCH

Cell 1         Kate spade lee
Cell 2         susan kate spade
Result         NO MATCH

I need a perfect match for the name in any order. This is the code so far:

function allIn(str1, str2) 
' check whether all elements of str1 occur in str2 
' and vice versa 
Dim l1, l2, ii As Integer 
Dim isfound As Boolean 
isfound = True 
l1 = Len(str1) 
l2 = Len(str2) 

If l1 < l2 Then ' look for all the elements of str1 in str2 

For ii = 1 To l1 

If InStr(1, str2, Mid(str1, ii, 1), vbTextCompare) <= 0 Then 
    isfound = False 
Exit For 
End If 
Next ii 
Else ' look for all the elements of str2 in str1 

For ii = 1 To l2

If InStr(1, str1, Mid(str2, ii, 1), vbTextCompare) <= 0 Then 
isfound = False 
Exit For 
End If 
Next ii 
End If 
allIn = isfound 
End Function
  • Please define "a perfect match" – Jan Doggen Aug 28 '13 at 11:10
  • Did you try anything yet? Possible duplicate of comparing two strings in excel?. Or does this have to be done using VBa, as per your tags? – Dave Aug 28 '13 at 11:29
  • I'm afraid VBA is needed, @Dave. Strangely enough, Excel is not too good with splitting strings, which is the start of any solution, I guess. – Arjan Aug 28 '13 at 11:50
  • Can you post the code you've done and then we can see where it is going wrong. In theory, you take the value of each cell, split it to an array and compare each string at a time! Then if any are false, break out of the loop and return false. – Dave Aug 28 '13 at 11:53
  • Function allIn(str1, str2) ' check whether all elements of str1 occur in str2 ' and vice versa Dim l1, l2, ii As Integer Dim isfound As Boolean isfound = True l1 = Len(str1) l2 = Len(str2) If l1 < l2 Then ' look for all the elements of str1 in str2 For ii = 1 To l1 If InStr(1, str2, Mid(str1, ii, 1), vbTextCompare) <= 0 Then isfound = False Exit For End If Next ii Else ' look for all the elements of str2 in str1 For ii = 1 To l2 – gwen Aug 29 '13 at 4:27
0

It has been a long time since I've done VBa and I can not test this, but, this should give you a good start if it doesn't compile or even work exactly as required.

' check whether all elements of str1 occur in str2
' and vice versa.
Function allIn(str1, str2) As Boolean

'first thing to check is if the 2 strings are the same length, if they're not then we know they are different
If Len(str1) <> Len(str2) Then
    allIn = False
End If

Dim isfound As Boolean
isfound = True

'Get the 1st string as array (split by white space)
Dim s1() As String
s1() = Split(str1)

'iterate through each array, word at a time
For Each element In s1

    If Not InStr(str2, element) Then
        isfound = False
    End If

'if it wasn't found, we can exit the loop immediately (no point finishing the test as it's already failed)
If (isfound = False) Then
    Exit For
End If

Next element

allIn = isfound
End Function

I have a few concerns about this code though, such as what happens if an extra piece of white space was at the start or tail of the str1 or str2... However, this should do what your question is asking (again, not tested)

You may need to ensure that the string is always trimmed and or lower case when doing the comparison etc, not sure how it works in VBa.

  • It has a syntax error – gwen Aug 29 '13 at 9:50
  • Function allIn(str1, str2) As Boolean on this line – gwen Aug 29 '13 at 9:52
  • @gwen, Updated, but still not tested – Dave Aug 29 '13 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.