Deleting duplicate entries in an Excel list
 

I work with a list of about 3500 entries in Excel that is updated every 2
weeks. Is there any way to easily delete out duplicate entries without
manually sorting the list and deleting them one at a time?

There are a couple of ways to do this.
One is using VB and a procedure to delete duplicate items.
Or you could use Data- Filter- Advanced Filter
By selecting the Select Unique Records only and copying to another range you
will get a list of non-duplicate items.

Nick


"ticephotos" wrote in message
...
I work with a list of about 3500 entries in Excel that is updated every 2
weeks. Is there any way to easily delete out duplicate entries without
manually sorting the list and deleting them one at a time?

I tried the filter and still had almost all of the duplicate entries. How
would I write the code in VB to delete the duplicates?

"Nick" wrote:

There are a couple of ways to do this.
One is using VB and a procedure to delete duplicate items.
Or you could use Data- Filter- Advanced Filter
By selecting the Select Unique Records only and copying to another range you
will get a list of non-duplicate items.

Nick


"ticephotos" wrote in message
...
I work with a list of about 3500 entries in Excel that is updated every 2
weeks. Is there any way to easily delete out duplicate entries without
manually sorting the list and deleting them one at a time?

Sub DeleteDups

Dim rInput as range
Dim rNext as range


Set rInput = ActiveCell (Assuming the Cell Selected is the start of your
list and contains the duplicate values)

rInput.Sort key1:=rInput.range

Do until isempty(rinput.value)
set rNext = rInput.Offset(1)
if rInput.Value=rNext.Value then
rInput.Entirerow.Delete
end if
set rInput = rNext
Loop


End Sub

I have tested this actual code put I know this works if I haven't made any
stupid typos

Nick


"ticephotos" wrote in message
...
I tried the filter and still had almost all of the duplicate entries. How
would I write the code in VB to delete the duplicates?

"Nick" wrote:

There are a couple of ways to do this.
One is using VB and a procedure to delete duplicate items.
Or you could use Data- Filter- Advanced Filter
By selecting the Select Unique Records only and copying to another range
you
will get a list of non-duplicate items.

Nick


"ticephotos" wrote in message
...
I work with a list of about 3500 entries in Excel that is updated every
2
weeks. Is there any way to easily delete out duplicate entries without
manually sorting the list and deleting them one at a time?

Ok, I did something wrong. It says "Arguement not optional" when I run it.
All I did was copy and paste the code minus (Assuming the Cell Selected is
the start of your
list and contains the duplicate values). What did I do wrong?

"Nick" wrote:

Sub DeleteDups

Dim rInput as range
Dim rNext as range


Set rInput = ActiveCell (Assuming the Cell Selected is the start of your
list and contains the duplicate values)

rInput.Sort key1:=rInput.range

Do until isempty(rinput.value)
set rNext = rInput.Offset(1)
if rInput.Value=rNext.Value then
rInput.Entirerow.Delete
end if
set rInput = rNext
Loop


End Sub

I have tested this actual code put I know this works if I haven't made any
stupid typos

Nick


"ticephotos" wrote in message
...
I tried the filter and still had almost all of the duplicate entries. How
would I write the code in VB to delete the duplicates?

"Nick" wrote:

There are a couple of ways to do this.
One is using VB and a procedure to delete duplicate items.
Or you could use Data- Filter- Advanced Filter
By selecting the Select Unique Records only and copying to another range
you
will get a list of non-duplicate items.

Nick


"ticephotos" wrote in message
...
I work with a list of about 3500 entries in Excel that is updated every
2
weeks. Is there any way to easily delete out duplicate entries without
manually sorting the list and deleting them one at a time?

tice

Remove the ".range" from rInput.Sort key1:=rInput.range

Sub DeleteDups()
Dim rInput As Range
Dim rNext As Range
Set rInput = ActiveCell
rInput.Sort key1:=rInput.Range
Do Until IsEmpty(rInput.Value)
Set rNext = rInput.Offset(1)
If rInput.Value = rNext.Value Then
rInput.EntireRow.Delete
End If
Set rInput = rNext
Loop
End Sub


Gord Dibben Excel MVP

On Tue, 3 May 2005 11:12:05 -0700, "ticephotos"
wrote:

Ok, I did something wrong. It says "Arguement not optional" when I run it.
All I did was copy and paste the code minus (Assuming the Cell Selected is
the start of your
list and contains the duplicate values). What did I do wrong?

"Nick" wrote:

Sub DeleteDups

Dim rInput as range
Dim rNext as range


Set rInput = ActiveCell (Assuming the Cell Selected is the start of your
list and contains the duplicate values)

rInput.Sort key1:=rInput.range

Do until isempty(rinput.value)
set rNext = rInput.Offset(1)
if rInput.Value=rNext.Value then
rInput.Entirerow.Delete
end if
set rInput = rNext
Loop


End Sub

I have tested this actual code put I know this works if I haven't made any
stupid typos

Nick


"ticephotos" wrote in message
...
I tried the filter and still had almost all of the duplicate entries. How
would I write the code in VB to delete the duplicates?

"Nick" wrote:

There are a couple of ways to do this.
One is using VB and a procedure to delete duplicate items.
Or you could use Data- Filter- Advanced Filter
By selecting the Select Unique Records only and copying to another range
you
will get a list of non-duplicate items.

Nick


"ticephotos" wrote in message
...
I work with a list of about 3500 entries in Excel that is updated every
2
weeks. Is there any way to easily delete out duplicate entries without
manually sorting the list and deleting them one at a time?