Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing alphabetizing entries
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing alphabetizing entries
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 "puzzlesnok" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing alphabetizing entries
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing alphabetizing entries
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. "JP Ronse" wrote: 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 "puzzlesnok" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing the Duplicate entries | Excel Worksheet Functions | |||
Changing Entries to Proper and Upper Case Q | Excel Worksheet Functions | |||
changing data entries | Excel Discussion (Misc queries) | |||
Alphabetizing | Excel Discussion (Misc queries) | |||
MRU list - entries keep changing | Excel Discussion (Misc queries) |