Deleting Duplicate Entries in a list.

  • Thread starter Thread starter Scott Sackville
  • Start date Start date
S

Scott Sackville

I am working with a list of people and addresses, a
registered voter list. For a mailing, is there a way to
delete the duplicate addresses so I will only send one
letter to each household?

Thanks
 
Scott
Here is a write-up I have on what you want to do. HTH Otto
Before you run the macro, you must sort your data a-z so that all the
duplicate entries
appear sequentially. The macro works by comparing the value in each row
with the value in
the preceding row (i.e. is this cell contents the same as the cell above?).
If so it
deletes the row and continues.

This macro is written for data in Column A starting in Row 1. Amend this as
needed.
Sub DupeClear()
Dim r As Single
Dim c As Single
r = 1
c = 1
Do Until Cells(r, c).Value = ""
x = Cells(r, c).Value
If x = y Then
Rows(r).Select
Selection.Delete Shift:=xlUp
Else
r = r + 1
End If
y = x
Loop
End Sub

Another way without VBA
sort the data
add a column to test for duplicates by a formula, e.g. in B2 put
=IF(A2=A1,"Y","")
drag this cell down as far as your data goes.
use Autofilter on column B and select the Y's
delete the rows showing
Clear AutoFilter
 
If x = y Then <<< What is Y ?
TIA,


Otto Moehrbach said:
Scott
Here is a write-up I have on what you want to do. HTH Otto
Before you run the macro, you must sort your data a-z so that all the
duplicate entries
appear sequentially. The macro works by comparing the value in each row
with the value in
the preceding row (i.e. is this cell contents the same as the cell above?).
If so it
deletes the row and continues.

This macro is written for data in Column A starting in Row 1. Amend this as
needed.
Sub DupeClear()
Dim r As Single
Dim c As Single
r = 1
c = 1
Do Until Cells(r, c).Value = ""
x = Cells(r, c).Value
If x = y Then
Rows(r).Select
Selection.Delete Shift:=xlUp
Else
r = r + 1
End If
y = x
Loop
End Sub

Another way without VBA
sort the data
add a column to test for duplicates by a formula, e.g. in B2 put
=IF(A2=A1,"Y","")
drag this cell down as far as your data goes.
use Autofilter on column B and select the Y's
delete the rows showing
Clear AutoFilter
Click to expand...
 
Back
Top