Formula to identify person where the first name, middle name and last name are not in the correct order

[chokeski]

Hi fellow excel experts,

I have to identify names from a excel spreadsheet where the first names, middle names and last names are not in the correct sequence. The names are in 1 column.

Is there a formula i can use to identify a unique person even though the sequence is incorrect?

E.g.
Scenario 1: First name, Last Name
John Smith vs (First Name, Last Name)
Smith John (Last Name, First Name)
- result would show 1 unique person

Scenario 2: First Name, Middle Name, Last Name
John Andrew Smith
Smith John Andrew
Andrew John Smith
- result would show 1 unique person

If anyone could please help it would be great! I'm going to have hundreds of names to comb through

Thanks in advance

 

[jasonb75]

There is not a way to do what you want 'accurately', taking this part of your post

Scenario 2: First Name, Middle Name, Last Name
John Andrew Smith
Smith John Andrew
Andrew John Smith
- result would show 1 unique person

The 2 names in bold could be the same person, or 2 different people, there is no way that excel can determine which is correct without additional (unique) identifiers.
This is just an example of one pitfall, there are many more.

John Smith
John Smith

Is one unique name but could still be 2 different people.

 

[footoo]

Here's a function taken from here : Sort text in single cell alphabetically

Function SortMe(mycell)
Dim mycode() As String, myinput$, j&, k&, temp$, mysort$
myinput = mycell.Value
mycode = Split(myinput, " ")
For j = LBound(mycode) To UBound(mycode) - 1
  For k = j To UBound(mycode)
    If mycode(k) < mycode(j) Then
      temp = mycode(k)
      mycode(k) = mycode(j)
      mycode(j) = temp
    End If
  Next k
Next j
mysort = ""
For j = LBound(mycode) To UBound(mycode)
  mysort = mysort & mycode(j) & " "
Next j
SortMe = mysort
End Function

Enter in B1 =SortMe(A1) and fill down.
Select columns A:B and sort by column B.
This groups all the "same" names together to make it easy to review.