Excel - compare names in two cells in different order

Question

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

Dave · Answer 1 · 2013-08-29T09:53:32.550

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.

Excel - compare names in two cells in different order

1 Answers1