Sorting, Searching, and Replacing

[Hunter Pieper]

Hello,

Ive got a list of of numbers like so;

InboundBlockIPHIP=218.80.167.23
InboundBlockIPHIP=218.94.97.221
InboundBlockIPHIP=219.233.28.141
InboundBlockIPHIP=219.252.1.30
InboundBlockIPHIP=219.95.228.170
InboundBlockIPHIP=220.75.154.171
InboundBlockIPHIP=221.124.0.199
InboundBlockIPHIP=24.126.174.97
InboundBlockIPHIP=24.151.21.101
InboundBlockIPHIP=24.168.28.213
InboundBlockIPHIP=24.200.5.180
InboundBlockIPHIP=24.218.170.95
InboundBlockIPHIP=24.218.204.241
InboundBlockIPHIP=24.224.192.162
InboundBlockIPHIP=24.25.88.178
InboundBlockIPHIP=24.31.215.163
InboundBlockIPHIP=24.33.230.92


This is a growing list.

How can I sort this list numerically, and then search it
for duplicate numbers so that they may be deleted?

Any help would be greatly appreciated.

Regards

Hunter Pieper

 

[Jay Freedman]

Hi, Hunter,

Try this macro (see http://www.mvps.org/word/FAQs/MacrosVBA/CreateAMacro.htm
if necessary).

It may not get the fourth column of IP nodes sorted correctly, because
Word's sort routine allows only three sort fields. In some cases that may
lead the macro to miss duplicates because they're separated by non-duplicate
rows. So you'll need to do some manual checking on the final result, or
substitute your own sort routine for the built-in one.

Sub SortAndPrune()
Const strStart = "InboundBlockIPHIP="
Dim oRg As Range
Dim oTbl As Table
Dim nRow As Long
Dim oCell As Cell

Set oRg = ActiveDocument.Range

' temporarily remove the starting text
With oRg.Find
.ClearFormatting
.Replacement.ClearFormatting
.Format = False
.Forward = True
.Wrap = wdFindContinue
.Text = strStart
.Replacement.Text = ""
.Execute Replace:=wdReplaceAll
End With

' doc now contains only IP addresses
Set oRg = ActiveDocument.Range

' make a table, separating columns at the dots
Set oTbl = oRg.ConvertToTable '(Separator:=".")
With oTbl
' sort the table on the first three columns
' (that's the best Word can do, sorry...)
.Sort excludeheader:=False, _
fieldnumber:=1, sortfieldtype:=wdSortFieldNumeric, _
fieldnumber2:=2, sortfieldtype2:=wdSortFieldNumeric, _
fieldnumber3:=3, sortfieldtype3:=wdSortFieldNumeric

' eliminate duplicates
For nRow = .Rows.Count To 2 Step -1
If .Cell(nRow, 1).Range.Text = _
.Cell(nRow - 1, 1).Range.Text And _
.Cell(nRow, 2).Range.Text = _
.Cell(nRow - 1, 2).Range.Text And _
.Cell(nRow, 3).Range.Text = _
.Cell(nRow - 1, 3).Range.Text And _
.Cell(nRow, 4).Range.Text = _
.Cell(nRow - 1, 4).Range.Text Then

.Rows(nRow).Delete

End If
Next nRow

' restore the starting string
For Each oCell In .Columns(1).Cells
oCell.Range.InsertBefore strStart
Next oCell

' change the table back to text
.ConvertToText Separator:="."
End With
End Sub