Jump to content
Sign in to follow this  
zico martin

duplicate lines in an excel spreadsheet

Recommended Posts

Im sure there must be a way of removing duplicate lines for a spreadsheet but cant work out how to do it. I thought importing it into access and setting a primary key would have solved the problem but it isnt.

 

help?

Share this post


Link to post
Share on other sites

Did you import to access into an existing table with a primary key already or just in and then added the primary key?

 

What you need to do is make the table first, allocate the primary key to the field and then import the data into it, this will discard any duplicates (giving you the message that x records were not added.

 

However the following VBA subroutine should work (Im not at work and dont have Excel so cant test it!)

 

 

Sub DeleteDups()

col = 2

lastValue=Cells(1,col).value

for x = 2 to 1000 (or number of rows in spreadsheet)

currValue= cells(x,col).value

if (currValue = lastValue) then

Selection.Rows(x).EntireRow.Delete

x = x - 1

End if

lastValue = currValue

next x

 

Do me a favour though and take a backup before running that as it could be flawed and delete it all!

 

If you're not used to VBA then goto Tools>Macro>Visual basic then add a module and paste that code in there. Then make sure youve got the right worksheet selected and in order before running it.

Share this post


Link to post
Share on other sites
Did you import to access into an existing table with a primary key already or just in and then added the primary key?

 

What you need to do is make the table first, allocate the primary key to the field and then import the data into it, this will discard any duplicates (giving you the message that x records were not added.

 

However the following VBA subroutine should work (Im not at work and dont have Excel so cant test it!)

 

 

Sub DeleteDups()

col = 2

lastValue=Cells(1,col).value

for x = 2 to 1000 (or number of rows in spreadsheet)

currValue= cells(x,col).value

if (currValue = lastValue) then

Selection.Rows(x).EntireRow.Delete

x = x - 1

End if

lastValue = currValue

next x

 

Do me a favour though and take a backup before running that as it could be flawed and delete it all!

 

If you're not used to VBA then goto Tools>Macro>Visual basic then add a module and paste that code in there. Then make sure youve got the right worksheet selected and in order before running it.

 

 

 

thanks but it aint working :(

 

using the access route it tells me 'an error occured trying to import file ....'

 

 

using the visual basic code I get 'Compile error: Expected End Sub' - so i guessed I had to add the line 'End Sub' to the end - but then when I run it, it seems the program is running in a loop and never finishes.

 

Can anyone who knows a little about VB give me a hand?

Share this post


Link to post
Share on other sites

Try this one, it works for me, obviously edit it to suit.

 

Option Explicit

 

Sub DeleteDups()

 

Dim x As Long

Dim LastRow As Long

 

LastRow = Range("A65536").End(xlUp).Row

For x = LastRow To 1 Step -1

If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then

Range("A" & x).EntireRow.Delete

End If

Next x

 

End Sub

Share this post


Link to post
Share on other sites
Try this one, it works for me, obviously edit it to suit.

 

Option Explicit

 

Sub DeleteDups()

 

Dim x As Long

Dim LastRow As Long

 

LastRow = Range("A65536").End(xlUp).Row

For x = LastRow To 1 Step -1

If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then

Range("A" & x).EntireRow.Delete

End If

Next x

 

End Sub

 

 

thanks guys

 

Ive actually realised there's an even easier way though:

 

data - filter - advanced filter - copy to new location and click 'unique records only'

 

sheesh all the time wasted and it was right under my nose eh!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

Recent tweets

Toontastic Facebook

Donate to Toontastic

Keeping the lights on since... well ages ago
TT-Staff


×