Sorting Lists (Macro methods)

Home Up Odds & Ends Photo Gallery Search Contact Me Privacy Notice What's New?

 

 

 The information in this website is provided without risk or obligation and free of charge.  However, if you have benefitted from my efforts here and would like to make a contribution to help me continue and maintain this work then any donation will be greatly appreciated. Please click the adjacent button to access PayPal.  Thank you.
 
This Microsoft Word Tips & Microsoft Word Help page provides discussion and a few simple macros that you can use to sort a selected list of words or phrases.  

Consider the list of U.S. state names shown below:
The names of the listed states are in random order and some states are listed more than once.  Each state name is listed on an individual line and represents a complete Word paragraph.  

The following macro can be used to sort the list and remove duplicate entries:
  Sub SortAndRemoveDuplicatesFromList()
Dim oPars As Paragraphs
Dim oPar As Paragraph
Dim myCol As New Collection
Set oPars = Selection.Paragraphs
'Perform the sort
If oPars.Count > 1 Then
    Selection.Sort SortOrder:=wdSortOrderAscending
Else
    MsgBox "There is no valid selection to sort"
    Exit Sub
End If
'Remove duplicates
For Each oPar In ActiveDocument.Range.Paragraphs
    On Error Resume Next
    myCol.Add oPar.Range.Text, oPar.Range.Text
    If Err.Number = 457 Then oPar.Range.Delete
Next
End Sub
The first step is to select the list members.  Since the macro processes the "selected" paragraphs, be sure to include the last paragraph mark in the list in your selection. You may want to show non-printing characters before making the selection

 :old:For information on displaying and using non-printing characters such as the Pilcrow (paragraph mark) circled below, see:  http://word.mvps.org/FAQs/Formatting/NonPrintChars.htm

First the macro sorts the list.  Once the list is sorted, the macro then begins to step through each paragraph and adds the paragraph text to a collection.  The text of each paragraph is also used as the unique key for each collection item.  When the macro attempts to add an item with the same key as an existing item the code generates a runtime error number 457.  By trapping this error we can delete the redundant paragraph text.  When all duplicate paragraphs are deleted, the result appears as shown below:

The next illustration shows a listing of the Modern Library Reader's Choice books.  The basic list is on the left is sorted by popularity, the list on the right is sorted alphabetically using the user interface (UI).

Sometimes people prefer to exclude articles "A" and "The" from the search criteria as shown on the left in the illustration below or move the articles to the end of the list member as shown on the right.

You can exclude the leading articles from the sort criteria as shown in the example on the left by first applying the "hidden" font attribute to the article, turning off display of hidden text and sorting the list with the UI, and then removing the hidden font attribute.  A simple task for a short list, but a macro can handle short or long lists with relative ease:
  Sub SortMacroI()
Dim bCurrentStateAll, CurrentStateSHT
Dim oPar As Paragraph, Dim pStr As String
If Selection.Range.Paragraphs.Count < 2 Then
    MsgBox "Select the list members and try again.", vbCritical, "Nothing selected!"
    Exit Sub
End If
'Apply hidden font attribute to leading articles
For Each oPar In Selection.Range.Paragraphs
    Select Case UCase(oPar.Range.Words.First)
        Case "A ", "THE "
            oPar.Range.Words.First.Font.Hidden = True
        Case Else
            'Do Nothing
    End Select
Next
'Ensure hidden font is not displayed
bCurrentStateSHT = ActiveWindow.ActivePane.View.ShowHiddenText
bCurrentStateAll = ActiveWindow.ActivePane.View.ShowAll
ActiveWindow.ActivePane.View.ShowHiddenText = False
ActiveWindow.ActivePane.View.ShowAll = False
'Perform the sort
Selection.Sort
'Restores settings and remove hidden font attribute
ActiveWindow.ActivePane.View.ShowHiddenText = bCurrentStateSHT
ActiveWindow.ActivePane.View.ShowAll = bCurrentStateAll
Selection.Range.Font.Hidden = False
End Sub
Similarly, you can manually move the articles to the end of the list members and sort the list with the UI.  Again, a macro makes short work of tedious, repetitious tasks:
  Sub SortMacroII()
Dim oRng As Word.Range, oRngProcess As Word.Range
Dim oPar As Paragraph, pStr As String
Set oRng = Selection.Range
If oRng.Paragraphs.Count < 2 Then
    MsgBox "Select the list members and try again.", vbCritical, "Nothing selected!"
    Exit Sub
End If
For Each oPar In oRng.Paragraphs
    Set oRngProcess = oPar.Range
    With oRngProcess
        Select Case UCase(.Words.First)
            Case "A ", "THE "
                'Store article in a variable string
                pStr = ", " & Trim(.Words.First)
                'Delete the article
                .Words.First.Delete
                'Insert variable string before the ending paragraph mark
                .End = .End - 1
                .Words.Last.InsertAfter pStr
            Case Else
                'Do nothing
        End Select
    End With
Next
'Perform the sort
oRng.Sort
End Sub
I prefer the second method with the articles moved to the end of the list member.  Simple enough in the example above, but consider as more complicated list.  The basic list on the right now includes the books author. 

Here we need to relocate the article in the text string vice simply moving it to the end.  The key is to leverage off of the separator word "by" as shown:
  Sub SortMacoIII()
Dim oRng As Word.Range, oRngProcess As Word.Range
Dim pStr As String, pSep As String
Dim oPar As Paragraph, i As Long
'Define the separator
pSep = "by"
Set oRng = Selection.Range
If oRng.Paragraphs.Count < 2 Then
    MsgBox "Select the list members and try again.", vbCritical, "Nothing selected!"
    Exit Sub
End If
For Each oPar In oRng.Paragraphs
    'Set a processing range
    Set oRngProcess = oPar.Range
    With oRngProcess
        Select Case UCase(.Words.First)
            Case "A ", "THE "
                'Store article in a variable string
                pStr = ", " & Trim(.Words.First)
                'Delete the article
                .Words.First.Delete
                'Find separator in processing range
                i = InStr(.Text, pSep)
                'Redefine processing range and re-insert variable variable
                .Start = .Start + i - 2
                .InsertBefore pStr
            Case Else
                'Do nothing
        End Select
    End With
Next
'Perform the sort
oRng.Sort
End Sub
That is it for now.  If I think of other nifty tricks for list sorting I will post them here.

Need help applying macros?  See Word MVP Graham Mayor's  Guide for Installing Macros 

Looking for something else?

Google