Sort By Last Name in a List

eliz.bell · #1 05-05-2009, 06:35 PM

I frequently get email lists of names, First Last, in several different batches. I copy and paste these names into a master document which I must then alphabetize by last name. How can I sort my master list so that the last name is alphabetized first in the list? In other words, I want to sort by line, field 1, word 2 (the last name), followed by field 1, word 1 (the first name).

Bird_FAT · #2 05-05-2009, 11:27 PM

If you highlight the list and turn it into a table:
(2003)

Table > Convert > Text to Table
(for a separator, use a SPACE)

(2007)

Insert > Table > Convert Text to Table
(for a separator, use a SPACE)

You can then sort the table by column 2:
(2003)

Highlight table
Table > Sort
Sort by > Column 2

(2007)

Table Tools > Layout
Sort
Sort by > Column 2

Then, if needed, convert back to text.

Bird_FAT · #3 05-05-2009, 11:39 PM

Here's a bit of code that will automate the process for you. It will select all, convert to list, sort by column 2 and then reconvert to text:

Open your VB window (Alt+F11)
In the left hand window, look for 'Normal' and click on the +
Click the + next to Microsoft Word Objects
Double-click ThisDocument
Paste the code below
Import/Open your name list (make sure you only have names in your document)

Run the Macro:
(2003)
Tools > Macro > Macros > (choose 'Sort_Name_List) > Run

(2007)
View > Macro > View Macros > (choose 'Sort_Name_List) > Run

Code:

Sub Sort_Name_List()
'
' 06/05/2009 by Bird
'
    ScreenUpdating = False
    Selection.WholeStory
    Selection.ConvertToTable Separator:=wdSeparateByDefaultListSeparator, _
        NumColumns:=2, NumRows:=7, AutoFitBehavior:=wdAutoFitFixed
    With Selection.Tables(1)
        .Style = "Table Grid"
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = True
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = True
    End With
    Selection.Sort ExcludeHeader:=False, FieldNumber:="Column 2", _
        SortFieldType:=wdSortFieldAlphanumeric, SortOrder:=wdSortOrderAscending, _
        FieldNumber2:="", SortFieldType2:=wdSortFieldAlphanumeric, SortOrder2:= _
        wdSortOrderAscending, FieldNumber3:="", SortFieldType3:= _
        wdSortFieldAlphanumeric, SortOrder3:=wdSortOrderAscending, Separator:= _
        wdSortSeparateByCommas, SortColumn:=False, CaseSensitive:=False, _
        LanguageID:=wdEnglishUK, SubFieldNumber:="Paragraphs", SubFieldNumber2:= _
        "Paragraphs", SubFieldNumber3:="Paragraphs"
    Selection.Rows.ConvertToText Separator:=wdSeparateByDefaultListSeparator, _
        NestedTables:=True
    ScreenUpdating = True
End Sub

By putting this code in the 'ThisDocument' code, then it means that it will be available in all documents.

eliz.bell · #4 05-06-2009, 09:42 AM

When I press Alt+F11 I get a screen that says Microsoft Visual Basic - Normal.

Under that is the "normal" file menu of File, Edit, View, Insert, etc.

Then there is a screen that says:

Normal New Macros (Code)
and then

(General) (Declarations)

I obviously did something wrong. How do I get back to my starting point where I get the "Normal" in the left hand window in order to click on the + ?

Bird_FAT · #5 05-06-2009, 12:33 PM

Go to the View menu at the top and choose Object Browser - then follow the steps above.

eliz.bell · #6 05-06-2009, 07:38 PM

This is what I got when I ran the macro. The names in the same order with a dash after them. Obviously I am missing something.

eliz.bell · #7 05-06-2009, 08:13 PM

I tried the table method and that worked just fine, but I would really like to get the macro to work for me as it would take out all those steps.

I am an excellent WordPerfect user, but a virtual beginner at Word.

Bird_FAT · #8 05-06-2009, 11:37 PM

I think the easiest thing for you to do would be to create your own Macro!

Go to the Visual Basic Editor and delete the code I gave you (a clean start)
Close the visual basic window
Open a document with a list of names in
(2003)

Tools > Macro > Record New Macro

(2007)

View > Macros > Record Macro

You will now get a 'Record Macro', here you need to change the name (call it what you want - NO SPACES ALLOWED though!) and click OK.

You should now see a little box with a pause and a stop button to let you know the recorder is running. Now do the following:

Ctrl+A (Select All)
Turn Text to Table
Sort by Column 2
Turn Table to Text
Click stop on the Macro Toolbar

YAY! You've just created your first macro!! Now you can do one final edit to tidy it up a little.

We are now going to Edit the Macro.

(2003)Tools > Macro > Macros > highlight your macro > Edit
(2007)View > Macros > View Macros > highlight your macro > Edit

The Visual Basic window will open showing the code for your macro.
Now, if you look back at the code that I put in my post - can you see the lines:

Code:

    ScreenUpdating = False

and

Code:

    ScreenUpdating = True

Notice where they are, and add them in the same place to your macro. What these lines do is stop the screen from showing your macro as it runs - otherwise you see it flickering through the different steps - this way it seems to miraculously go from unsorted, to sorted, and is much more professional looking!

Hope this is simple enough to follow! And ... now you know how to make your own macros, you can automate ALL SORTS of tasks that you find yourself doing over and over again.

If you need any more help - feel free to post a thread with us here - always happy to help!

eliz.bell · #9 05-07-2009, 01:33 PM

Yes!! I did it!! Thank you so much! I tried to give your reputation another boost, but apparently can't until I boost someone else's reputation.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how do i take my contact list to a new computer?	blade	Outlook	2	01-13-2011 07:03 AM
List option	shashijb	Excel	1	12-18-2008 03:07 AM
Which app to use for list on Outlook?	sus	Misc	0	08-26-2008 08:46 AM
creating a categories list	owwiii	Outlook	0	08-09-2007 01:56 PM
Creating a Map From a List of Addresses	aleccamp	Excel	0	11-19-2005 03:04 PM