Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merge, update, and add only new entries into a list from other she | Excel Worksheet Functions | |||
Create a cascading list box using Excel | Excel Discussion (Misc queries) | |||
How can I purge duplicate entries in Excel | Excel Discussion (Misc queries) | |||
How do I import a Windows Explorer list into Excel? | Excel Discussion (Misc queries) | |||
Duplicate records in Excel | Excel Discussion (Misc queries) |