Solved: duplicate cells

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

mwatech

Thread Starter
Joined
Sep 20, 2012
Messages
18
hello to one and all,

I have a an Excel spreadsheet (2010 windows 7) which has a large amount of data. I know that potentially up to 1/3 of this data is duplicated. I can use the "remove duplicates" button to clean the data but i need to be able to see which entries had been duplicated. Is there any way to be able to see which data is removed during the clean up?

thanks for any advice :)
 
Joined
Jun 29, 2012
Messages
518
The macro that removes the duplicates should be modifiable to copy the duplicate info to another sheet for review before it removes the items. Can you post a sample workbook (no sensitive data) so we can assist?
 

mwatech

Thread Starter
Joined
Sep 20, 2012
Messages
18
i can only see the duplication button which does not give me an option to modify. it allows me to select the columns to be checked for duplications and isolate any headers but that is all.

A dummy spreadsheet should be attached
 

Attachments

Joined
Apr 17, 2012
Messages
455
you can just sort the helper column and delete anything that is greater than 1.
If the list is too long, you might want to convert the helper column to values first.
 

Attachments

mwatech

Thread Starter
Joined
Sep 20, 2012
Messages
18
ah, sorry i dont think i explained very well. there is more than one column and i would like the duplicate data removed from the spreadsheet and recorded either in a new sheet or a new column or a new workbook.

I have created an example of what i would hope for on a second sheet. some of the names may be the same but the regerence details are different and some will be complete duplicates.

does this make more sense?
 
Joined
Jun 29, 2012
Messages
518
This may do what you are looking for. You may need to enable your Developer tab in excel - I do not know your Version number, but you can google it for instructions. This identifies each duplicate in Sheet1, copies the duplicate row to sheet Removed, then removes the row from Sheet1. In your actual workbook, you will need to browse the code and see where it identifies a duplicate entry, and add the few commands to copy the row to the Removed sheet before it actually deletes it. Hope this helps point you in a direction that helps.
 

Attachments

mwatech

Thread Starter
Joined
Sep 20, 2012
Messages
18
thanks for this.

I have enabled the developer tab and have copied the code into the insert procedure option expanding "A" to "A:D" but it wont run! error as highlighted below states that is came to an unexpected end or gives a 1004 error code (application-defined or object-defined error)

Public Sub remduplicates()
Sheets("Sheet1").Select
lastrow = Range("A:D" & Rows.Count).End(xlUp).Row
For i = lastrow To 2 Step -1
Range("A:D" & i).Select
CheckThis = ActiveCell
If ActiveCell.Offset(-1, 0) = CheckThis Then
ActiveCell.EntireRow.Copy
SaveRemoved
ActiveCell.EntireRow.Delete
End If
Next i
End Sub
Public Sub saverems()
Sheets("Removed").Select
lastrow = Range("A::D" & Rows.Count).End(xlUp).Row
Range("A:D" & lastrow + 1).Select
ActiveCell.PasteSpecial xlPasteAll
Sheets("Sheet1").Select
End Sub

i am obviously doing somethign wrong but what?
 
Joined
Jun 29, 2012
Messages
518
in this code sheet1 is the nsme of the sheet where it checks for duplicates
a sheet named Removed is where the removed dupelicates go
SAVEREMOVED is called to save what os removed but you changed the sub name to SAVEREMS. These name differences are causing the errors. just update the code to match your worksheet names, add the sheet named removed and rename the call to saverems.
 
Joined
Jun 29, 2012
Messages
518
using this code...column A must be sorted first. it checks col a to see if it matchs the cell right above.
 

mwatech

Thread Starter
Joined
Sep 20, 2012
Messages
18
i have data that works :)

thank you for all the help provided, much appreciated
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top