Sorting by last name

  • Thread starter Thread starter gschimek
  • Start date Start date
G

gschimek

I have some cells with people's first and last names in them. For space
reasons (so that everything fits on a page) I don't want to have
separate columns for first and last names, so they need to stay
together in one cell.

Is there a way to sort by last name when the names are listed like
this:

John Doe
Jane Doe
Larry Johnson
Gina Smith

Is there possibly a way to tell Excel to sort using the first character
after the space, for example?

Any help is greatly appreciated.
 
XL can not sortusing the first character after the space.
(You probably could with a macro. Though the easiest
thing to do would be to add a column outside your printed
range with a formula (that assumes your name is in A1)

=MID(A1,FIND(" ",A1)+1,99)

That formula will extract the last name and you could sort
on that column.


PC
 
I know it would be easier to list them by last name, but there's a good
reason I don't want to do that, although it's complicated to explain.
As for separating first and last names into different columns, I know
that would be easier as well, but I have too much information that I
want to fit on a page when I print it to be able to do that.

So, even though I know I'll have some issues with names like Susan Van
Schlaak, I think I can deal with it.

Does anyone know a good way to sort like I want to?
 
Hi g.,

I expect that someone is going to ask you to put what is sorted on
at the left side of a column rather than the first name, but that's up
to you.

Regardless of how you do it you will have to use something along
the lines of previous suggestions. The macro below will
create a helper column, sort on the helper column
and then delete the helper column. This macro is based on your
names being in column A and that you have exactly one header
row at the top.

Sub Sort_2nd_Name_in_Column_A()
Dim cell As Range, I As Long, var As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Columns("A:A").Insert Shift:=xlToRight
For Each cell In Columns("B").SpecialCells(xlConstants, xlTextValues)
var = Trim(cell.Value)
I = InStr(1, var, " ")
If I > 1 Then
cell.Offset(0, -1) = Mid(var, I + 1)
Else
cell.Offset(0, -1) = var
End If
Next cell
Cells.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("A").Delete
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Directions to install the above macro can be found in
http://www.mvps.org/dmcritchie/excel/getstarted.htm


HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
gschimek said:
I know it would be easier to list them by last name, but there's a good
reason I don't want to do that, although it's complicated to explain.
As for separating first and last names into different columns, I know
that would be easier as well, but I have too much information that I
want to fit on a page when I print it to be able to do that.
Click to expand...

Unless you're using all 256 columns, you have no excuses for not extending
your data range with additional columns to the right for separate last name
and first name fields, then either hiding those columns or restricting your
print ranges to the columns to the left of them.

But the short answer is, NO, Excel can't & won't sort the way you want it
to. You need to give it names in 'LastName, FirstName Other' format. If you
can't, then you'll have to sort manually, swapping rows with cut & paste.
 
Hi,
novice user hoping to assist
Are you aware that you can concatenate two columns into a third
or three if you want to separate the middle initial
col A1 contains firstname
col B1 contains middleinitial
col C1 contains lastname
col D1 contains =A1 & " " & B1 & " " & C1
What appears in col D is:
firstname middleinitial lastname
Then when you sort, select col C
When you print, DON'T select col's ABC

Hope it helps
BP
 
Thanks David McRitchie, your suggestion worked perfectly! I've now got
a nice macro that sorts by last name, then first name, and the names
are in the same field.

And to Harlan, I guess this means there is in fact a way that excel can
do what I want. And I do have a good excuse for not using extra
columns and extending my data to the right. As I already stated above,
I have a lot of data that I want to fit on one printed page, and if I
were to use two separate columns for the name, It would extend my data
off the page.

Sorry to rant. I'll stop now. Thanks again.
 
You're welcome, I thought it would be an acceptable solution even
though it does break your rule about not inserting a column, but then
it deletes that column after sorting. so everything looks as if nothing
was inserted.
 
To be a bit picky, no, you haven't gotten Excel to sort for you. You've
altered the data, using VBA, and sorted that altered data. You solved your
problem, but Excel didn't solve it: David and VBA solved it.

Like Harlan, I don't understand what the big problem is with using four
columns: last, first, middle names, and a formula that concatenates them. If
you use columns A:D for that, then it's quite simple to define your print
area to begin with column D rather than A. No macros required.

If, on the printout, the name isn't in the left-most column, you could still
set up the 4 columns as described, then use a simple Before_Print event
macro that hides the 3 columns with the last, first, and middle names, does
the printout, then unhides them.
 
I didn't mean to imply that you couldn't also put the columns on the right. They can be
anywhere. If to the left or right, you can solve the printing issue via the Print Area. If
embedded in the middle, hide them.

But I think it would be a bit awkward, if there is data other than the names, to have them way
over in IA. You'd have to scroll over there to enter them (or be sure the formulas that split
the name are entered into each of those cells).
 
...
...
And to Harlan, I guess this means there is in fact a way that excel can
do what I want. And I do have a good excuse for not using extra
columns and extending my data to the right. As I already stated above,
I have a lot of data that I want to fit on one printed page, and if I
were to use two separate columns for the name, It would extend my data
off the page.
Click to expand...
...

Getting picky, it's semantics whether it's Excel or VBA doing this for you with
David's macro, which bashing the data into the required form, adding columns to
split out last names, which you *say* you can't do but obviously could, sorting
on the added column of last names, then deleting the column after sorting.

And to repeat, unless you're using all 256 columns, you *could* use additional
columns to parse out last names, then set your print area to exclude this
additional column. If you *are* using all 256 columns, I don't see how David's
macro could be working.
 
Another point is that VBA is almost always slower than worksheet functions, and, IMO, should be
avoided whenever a pure "worksheet" solution is available. It is certainly available in this
case.

The OP has "an excuse", but I would not agree that it's a "good excuse".
 
Unfortunately, I think the OP has fled the scene and isn't here to read
our lessons on how to use Excel most effectively <g>.
Click to expand...
...

Who says we do this for the OP's benefit?
 
Back
Top