Deleting Duplicate Entries in a list.

[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

 

[Otto Moehrbach]

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

 

[Ron de Bruin]

One way Scott is to

Use Advanced filter to create a Unique list
http://www.contextures.com/xladvfilter01.html#FilterUR

 

[JMay]

If x = y Then <<< What is Y ?
TIA,

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