1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: duplicate cells

Discussion in 'Business Applications' started by mwatech, Jan 15, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. mwatech

    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 :)
     
  2. 20_2_Many

    20_2_Many

    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?
     
  3. mwatech

    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
     

    Attached Files:

  4. Garf13LD

    Garf13LD

    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.
     

    Attached Files:

  5. mwatech

    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?
     
  6. 20_2_Many

    20_2_Many

    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.
     

    Attached Files:

  7. mwatech

    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?
     
  8. 20_2_Many

    20_2_Many

    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.
     
  9. 20_2_Many

    20_2_Many

    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.
     
  10. mwatech

    mwatech Thread Starter

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

    thank you for all the help provided, much appreciated
     
  11. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    YVW.
    Have a great day!
     
  12. Sponsor

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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1085328

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice