Remove Duplicates Word Doc

schnipps · 2 Dec 2009 at 11:09

Hi - I have a word doc with 170 mobile numbers in, how do i check none are repeated? Should i use another program or can i run a regular expression to check for them? Thanks

wozencl · 2 Dec 2009 at 11:36

Are they listed in lines?

schnipps · 2 Dec 2009 at 11:42

wozencl said:
Are they listed in lines?

I have them in xcel now and they are on seperate lines/cells yep

wozencl · 2 Dec 2009 at 11:59

Code:

Sub RemoveDuplicateLines ()
Dim iRows, iDuplicates
iRows = 170
iDuplicates = 0
 
For i = 1 To iRows
    strCurrent = Sheet1.Range("A" & i)
 
    For n = i + 1 To iRows
        strNext = Sheet1.Range("A" & n)
        If strCurrent = strNext Then
            Sheet1.Range("A" & n).Delete
            iRows = iRows - 1
            iDuplicates = iDuplicates + 1
        End If
    Next n
 
Next i
MsgBox iDuplicates & " duplicate lines removed.", vbInformation
 
End Sub

Something like that should do the trick - it will match blanks though so you might want to refine it.

schnipps · 2 Dec 2009 at 16:34

Do i run this with excel? how do i run it? thanks

wozencl · 2 Dec 2009 at 16:44

Yes. It depends what version you are using...

For Excel 2000/2003(?)

Tools > Macro > Visual Basic Editor

Double click on ThisWorkBook on the right hand side

Paste this there

Code:

Private Sub Workbook_Open()
Dim iRows, iDuplicates
iRows = 170
iDuplicates = 0
 
For i = 1 To iRows
    strCurrent = Sheet1.Range("A" & i)
 
    For n = i + 1 To iRows
        strNext = Sheet1.Range("A" & n)
        If strCurrent = strNext Then
            Sheet1.Range("A" & n).Delete
            iRows = iRows - 1
            iDuplicates = iDuplicates + 1
        End If
    Next n
 
Next i
MsgBox iDuplicates & " duplicate lines removed.", vbInformation
 
End Sub

Press the play button.

It will loop through all cells in column A up to row 170 and delete any duplicate values.

schnipps · 2 Dec 2009 at 16:50

Im using 2010 beta im sure its the same, i will try this, thanks very much. Will i know how many it has deleted?

wozencl · 2 Dec 2009 at 16:54

There's a ribbon for Macros somwhere...

Yes a message box will appear - once it's done copy the numbers to another file and save.

EDIT: If you think you're going to be doing a lot of this in future, and have an interest in programming, you might want to give Perl a try.

div0 · 2 Dec 2009 at 16:55

If you don't want to use a macro, you could always just 'sort' the column of phone numbers and then in another column just use an IF statement to check if two cells match.

Eg:

Phone numbers are in Column A
Sort Column A Ascending
In Column B, start at B2 and enter the formula as: =IF(A2=A1, "DUPLICATE", "")
Apply formula to entire column
Manually delete any rows flagged as duplicates

you can use another cell with the following formula to count how many were duplicates: =COUNTIF(B:B, "DUPLICATE")

dazzerd · 2 Dec 2009 at 17:04

Or just use filter, advanced filter and ask it to return unique values.

Job done.

wozencl · 2 Dec 2009 at 17:13

dazzerd said:
Or just use filter, advanced filter and ask it to return unique values.

Job done.

Even better.

Lee · 2 Dec 2009 at 19:19

A simple pivot table in Excel will easily identify any duplicated numbers.

HungryHippos · 2 Dec 2009 at 19:33

hmm am I missing something? why use macros when the feature is built into excel?

Put them all in the same column in excel > highlight column > select Data ribbon > Remove Duplicates option

Jobs a goodun'