Results 1 to 15 of 15
  1. #1
    risman
    Guest

    Sorting lists in Word or Excel

    I have a long list of names that are arranged first name first, that I want to sort last name first, but they are not consistent in the number of "words" used in each name, i.e., some have middle initials, some are hyphenated last names. An example of the variety is shown below. Is there a way in either Word or Excel to sort these the way I want. I'm interested in 2 possible results. One would be sorted with the last name, followed by a comma, and then the first name(s) and middle initials (e.g., Jone, Cathy L). The second way would be sorted alphabetically by last name, but the list would still show the first name first (e.g., Cathy L. Jones would precede Bill Justin).

    Thanks for any help.
    Bob
    --------------------
    Laurie Vessey
    Joyce Volmut
    Claudia Vousden
    Nancy Waddell
    Eileen Wagner
    Mark L. Wagner
    H. Barry Waldman
    Deborah Klein Walker
    Harvey A. Wallace
    Magda A. de la Torre
    Dennis A. Mitchell-Lewis
    G. M. Nana Lopez
    Lee Ann Mijelde-Mossey
    Jane E. M. Steffensen
    Ruth E. Klein Stein
    Rena G. Boss-Victoria
    Jill B. Fernandez-Wilson

  2. #2
    ileacy
    Guest

    Re: Sorting lists in Word or Excel

    There is no built in function, however, I would start with
    <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q213/2/68.ASP>XL2000: How to Programmatically Find the Nth Word in a Text String</A>

    and modify the supplied function to extract the last word in the string.

    Create a new column for the last words and then sort on that column.

  3. #3
    risman
    Guest

    Re: Sorting lists in Word or Excel

    I don't know how to "modify the supplied function to extract the last word in the string."

  4. #4
    ileacy
    Guest

    Re: Sorting lists in Word or Excel

    Bob:
    Are you asking how to work with VBA? Or just asking what change I would make to the code in the article.?

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting lists in Word or Excel

    Put this function into your VBA area:

    <pre>Function LastName(strName)
    Dim i As Long

    strName = Trim$(strName)
    If Len(strName) = 0 Then
    LastName = ""
    Else
    For i = Len(strName) To 1 Step -1
    If Mid$(strName, i, 1) = " " Then
    LastName = Mid$(strName, i + 1)
    Exit Function
    End If
    Next
    End If

    ' If there's no spaces we're left with the name

    LastName = strName
    End Function
    </pre>


    Then, in column B of your worksheet (assuming the names are in column 1) enter the formula:
    =LastName(A1)

    for each cell for which there's a name in column 1.

    You can then sort on column B.

    This does assume that only the last name is valid- so in your example, the last name for "Magda A. de la Torre" comes out as "Torre"; also double-barrelled surnames (without a hyphen) will come out incorrectly. Spanish speakers my abbreviate there second surname as well- so you may find a name such as "Maria Aguilera B."

    Just some cautions.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Tallahassee, Florida, USA
    Posts
    901
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting lists in Word or Excel

    Hi, Bob.

    I copied your list to a Word document, and did the following:

    <font color=blue>
    Karen

  7. #7
    ileacy
    Guest

    Re: Sorting lists in Word or Excel

    Much better solution. I started modifying the function from the KB and realized it was overly complicated and limited to 4 words.

    Your solution also provides for a good starting point for adding in other features, eg. Handling hyphenated names.

  8. #8
    risman
    Guest

    Re: Sorting lists in Word or Excel

    The latter.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting lists in Word or Excel

    Hi Karen:
    Another brilliant solution! Question: how do you get those blue & red circles in your posts?

    Thanks,

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Tallahassee, Florida, USA
    Posts
    901
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting lists in Word or Excel

    [img]/w3timages/icons/blush.gif[/img]Thanks!

    Alt+167 on the num pad... release and the bullet's there... then just color them. [ blue ]
    Karen

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Sorting lists in Word or Excel

    This is a fantastic and nearly impossible programming challenge. Take for example Deborah Klein Walker, G. M. Nana Lopez and Magda A. de la Torre. How do you know which part comprises the "last name"? I think you can construct some pretty imaginative tests, and I will leave that part to you, Bob. The parts you might consider to set up your tests, and to do the sort, are as follows:

    (1) Separating the names. This recent article on the Split command got me really excited, and I think you have the perfect application.

    <A target="_blank" HREF=http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=94>http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=94</A>

    Introduced in VB6/Office 2000, Split will chop up a string into separate array elements at a delimiter you specify. In other words,

    Dim strArray(0) 'not dynamic, for reasons only MS knows
    strArray = Split("Magda A. de la Torre", " ")

    yields

    strArray(0) = Magda
    strArray(1) = A.
    strArray(2) = de
    strArray(3) = la
    strArray(4) = Torre

    I think you can see all the possibilities: you know how many words comprise the name, you can easily test for a period at the end of the "middle initial," etc.

    (2) Setting up the sort. Being rather unimaginative, and knowing this works, what I probably would do in Word is the following (pseudocode):

    - select name, parse, calculate new string
    - return to beginning of line, set font to hidden 6 point
    - type new string; move to next line
    - loop to completion
    - sort
    - replace all hidden text with nothing

    This is rather slow for a long list; I only use it when absolutely necessary. But it's all that comes to mind if you want to preserve the appearance of the list.

  12. #12
    ileacy
    Guest

    Re: Sorting lists in Word or Excel

    I had to write conversion software once to convert over a large number of raw Invoice/AR records into real customer records. The exceptions and special cases got so big that I ended up with 500+ lines of rules as FoxPro code plus data driven tables plus a manual clean up editor.

    Talked to a friend later who manages software projects for a mailing house. They have over 8 man years invested in their purifying code, and they are still tweaking it for new cases.

  13. #13
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting lists in Word or Excel

    Hi,

    That "split" command is nice, I didn't know it was there. Thanks.

    Of course, I still have to be carefule of earlier versions- but that's cool.

    I had a "split" command in one of my mainframe dinosaur languages- I'm glad to see it back.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  14. #14
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    England
    Posts
    1,306
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting lists in Word or Excel


  15. #15
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Tallahassee, Florida, USA
    Posts
    901
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting lists in Word or Excel

    That would be nice. I look forward to such an addition.
    Karen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •