# Excel - compare names in two cells in different order

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

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

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