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

chokeski

New Member
Joined
Jul 1, 2020
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,131
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 21, 2016
Messages
3,273
Office Version
  1. 2016
Platform
  1. Windows
Here's a function taken from here : Sort text in single cell alphabetically
VBA Code:
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.
 

Forum statistics

Threads
1,136,205
Messages
5,674,396
Members
419,506
Latest member
mpazr001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top