Changing alphabetizing entries

[puzzlesnok]

I have a list of complete names in a single column alphabetized by first
name. Examples: A. C. Johnson, Adrian Smith, Alex J. Anderson, Allan Del
Boca, Andrew Paul Lutz, Jr.
PRIMARY OBJECTIVE:
How can I alphabetize this list by last name to get: Anderson, Del Boca,
Johnson, Lutz (or Lutz, Jr.), Smith?
SECONDARY OBJECTIVE:
Also, can I do it and "keep" the rest of the name in the entry?
Thanks for your help.

 

[JP Ronse]

Hi Puzzlesnok,

I'm afraid that you cannot easily do this because you cannot determine if a
name is part of the first name or part of the last name.

Allan Del Boca, to what belongs Del?
Andrew Paul Lutz, to what belongs Paul?

What you can do:

Data, Text to Columns, space as delimiter to spread all the parts to a
singel cell.
With COUNTIF(A1:A10, "<>") you have the number of cells per row containing
some data.

If you have only 2 cells woith data, then you can recreate the name with B2
& " " & A2

If the result is 3 and the middle cell contains a point => C & A & B
=IF(ISERROR(FIND(".",B2,1)),"",C2 & " " & A2 & " " & B2)

The rest of the names will need a manual verification. Hope you get a better
response then this.

Wkr,

JP

 

[Peter T]

Copy your list to a new location
Visually look for any instances where two space separated words should be
regarded as a single surname, manually replace the space with "##" (without
quotes)
Select all the list and run the macro

Sub SurnamesFirst()
Dim rng As Range, cel As Range
Dim s As String
Dim arr

Set rng = Selection.Columns(1)
For Each cel In rng.Cells
s = cel.Value
s = Replace(s, " Jr", "##Jr")
' other two word names here

arr = Split(s, " ")
If UBound(arr) >= 0 Then
cel = Replace(arr(UBound(arr)), "##", " ")
If UBound(arr) > 0 Then
ReDim Preserve arr(1 To UBound(arr))
cel.Offset(0, 1) = Join(arr, " ")
End If
End If
Next

rng.Resize(, 2).Sort rng(1)

End Sub


If you have several names where the first part is "Del", add lines like the
following below the Replace line in the macro
s = Replace(s, "Del ", "Del##")
Note too how Jr is handled in the macro, maybe can include other similar

As written, the macro splits first names and the rest into two cells per
name

Regards,
Peter T

 

[puzzlesnok]

Thanks for your help. In the list I was trying to work with, there were no
middle names (I had that wrong). And, the ones with the initials (A.J. Smith)
had no space between the initials, sooooooooooo, the space delimiter
suggestion solved the problem. The two-part last names and the names with
"Jr.", separated the last names into two columns, but the first part was in
the second column, and therefore still allowed me to alphabetize by the
second column. The few entries in the third column went along for the ride.