How do I put in alphabetical order by Last Name in Excel?

Question

I need to put the full names of the people in alphabetical order by last name in excel

For example:

John Smith
Andy Anderson
Lisa Johnson
Kathy Crawford

It should be after alphabetical order by Last Name

Andy Anderson
Kathy Crawford
Lisa Johnson
John Smith

How do I this?

Answer 1

Here is a method to sort the data by last name simply double clicking any cell in the Name column.



Copy this event handler to the clipboard (highlight the entire event handler, right click inside the highlighted area, and 'Copy'):



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _

Cancel As Boolean)

Dim i, LastRow

LastRow = Cells. SpecialCells(xlCellTypeLastCell).Row

tCol = Split(Cells(1, Target.Column).Address, "$")(1)

On Error GoTo errhandler

For i = 1 To LastRow

Cells(i, "IV").Value = Right(Cells(i, Target.Column), _

Len(Cells(i, Target.Column)) - _

Application.Find(" ", Cells(i, Target.Column)))

Next

Range(tCol & "1:IV" & LastRow).Select

Selection.Sort Key1:=Range("IV1"), Order1:=xlAscending, _

Header:=xlGuess

Range("IV:IV").ClearContents

errhandler:

Target.Offset(1).Select

End Sub



Select the worksheet containing the data to sort and right click the sheet tab at the bottom.



Select 'View Code'.



Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').



Close the VBE (red button - top right).



Double click any 'Name' cell to sort the data by Last Name.



Note: this example assumes that your data columns may include column A through column IU. Thus, columns A:IU will be sorted based on the Last Name in the Name column.



Edit: this example assumes your data begins in row 1. If you have column headers, i.e. 'Name', in the first row, change this line before copying the event handler:



For i = 1 To LastRow



to



For i = 2 To LastRow

Answer 2

Excel Sort By Last Name

Answer 3

If you use kingsoft office, there is a function in menu. Kingsoft Office is free, by the way.

Answer 4

just as an alternative you can also instead store last and first names in separate cells. just in case you ever find yourself needing to sort by one or the other then at least this way you're prepped to do so either way.

Answer 5

Hi,



The cell can be sorted by alphabetical order, but You're having a problem because You're placing the name with first names first ... instead of the way most larger organizations do ... last name first, seperated by a comma.



So the program sorts it with all the people named JohnTHEN by the last name's first letter, etc ...



You need to modify the cell's "format" options to accept Your names list like this :



Smith, John

Anderson, Andy

Johnson, Lisa

Crawford, Kathy





And the program will sort it alphabetically by the LAST names, so if You added another Crawford with the first James it woule be placed in the sorted list ABOVE Kathy, etc..







G'Luck!!