sorting by last name

  • Thread starter Thread starter matthew
  • Start date Start date
M

matthew

In my excel worksheet I have a name column with names like

nancy bell
tod dingman

only I want to sort by their last names and the full name
is all in one cloumn any way to sort by the name after
space. any help appreciated
 
Since there's a space between the names you can use "Text To Columns" to
separate the names and place just the last names in the next column.
Then select your original column *and* the new one containing the last names
only, and perform your sort keyed to the new column.
Then you can delete this last name column if you wish.

<Data> <TextToColumns>
Make sure "Delimited" is checked, then <Next>,
Place a check in "Space", then <Next>,
Click on "Do Not Import Column",
In "Destinations", enter the cell address where you would like this new
column to start, opposite the beginning of your list of names,
Then <Finish>.

You should now have a column of last names only, next to your original list,
which can now be sorted *together with* the original list.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


In my excel worksheet I have a name column with names like

nancy bell
tod dingman

only I want to sort by their last names and the full name
is all in one cloumn any way to sort by the name after
space. any help appreciated
 
Hi Matthew!

Stripping out just the family name looks OK until you get Smith, John
and Smith, Mary etc. So...

Set up a helper columns for given and family names.

Then use Date > Text to columns with space as the delimiter.

You can then sort on the new columns.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Matthew,

Here is a code solution.

Sub SortByLastName()
Dim rng As Range
Dim i As Long
Dim istart As Long
Dim iTarget As Long
Dim iExtra As Long

Set rng = Range("A1:J14")
iExtra = rng.Column + rng.Columns.Count
iTarget = iExtra
istart = 2 'change to 1 if no header row
Columns(iExtra).Insert
For i = istart To rng.Rows.Count
rng(i, iTarget).Value = LastName(rng(i, 1).Value)
Next

rng.Sort key1:=Cells(iExtra, istart), header:=IIf(istart = 1, 2, 1)

Columns(iExtra).EntireColumn.Delete
End Sub

'---------------------------------------------------------------------
Private Function LastName(nme As String)
'---------------------------------------------------------------------
' Function: Return just the last name from supplied name
'---------------------------------------------------------------------
Dim sREgExp As String

sREgExp = "\b([a-z]+
+)*(O'|Mc|Mac)?[A-Z](\w+\S?)*(-[A-Z](\w+\S?)*)?\b(?=((
+)(Sr\.?|Jr\.?|[IVX][IVX]*))|,|\s*$)"
LastName = GetSubString(nme, sREgExp)

End Function

'---------------------------------------------------------------------
Private Function GetSubString(str As String, sReg As String) As String
'---------------------------------------------------------------------
' Function: Use Reg Exp to get a substring
'---------------------------------------------------------------------
Dim oRegExp As Object, M As Object
Set oRegExp = CreateObject("VBScript.RegExp")
oRegExp.Pattern = sReg
oRegExp.Global = True
Set M = oRegExp.Execute(str)
GetSubString = IIf(M.Count > 0, M(0).Value, "")
End Function

ot could be done by just adding the last 2 functions, and adding a helper
column, in put =LastNamed(A1) in that coilumn, and sort by that column



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
matthew

You can insert a column to the right of the name column then Data>Text to
Columns>Delimited>Next>Space>Finish.

Now select both columns and Data>Sort on the last names.

If you want the first/last in one column, copy this column before you split it
up into two columns.

e.g.

Col A first/last
Col B first/last

Insert a column C

Select Column B and Data>T to T.

Select B and C to sort. Leave A alone if desired.

Gord Dibben Excel MVP
 
=RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND(",",A1)-1))-2)&"
"&LEFT(A1,FIND(",",A1)-1)
I have this formula in B1 just put a comer after last name in AI if only one
name double comer after

--
Thanks in advance for your help....Bob Vance
..
..
..
..
 
Back
Top