+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Deleting duplicates as well as associated originals

  1. #1
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Deleting duplicates as well as associated originals

    Hi,

    How would I go about deleting duplicates in such a fashion, that it also deletes the original values which matched the duplicates and leave uniques only?

    To illustrate:

    AAA
    BBB
    AAA
    CCC
    BBB

    Should after removing duplicates leave only CCC instead of AAA, BBB, CCC

    Example:
    Check in column B, D, E. If the values all match, delete both originating rows

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Deleting duplicates as well as associated originals

    do you mean that want the combined contents of B,D, and E to be compared? please clarify with an attached example sheet, the process is easy with such an example.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  3. #3
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Deleting duplicates as well as associated originals

    Here you go

    Check Columns B,C,D,E. If they match in more than 1 row, delete all rows with that feature.

    Only 18 of the 27 rows would remain
    Attached Files Attached Files

  4. #4
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Deleting duplicates as well as associated originals

    Looks like this does the trick.


    Please Login or Register  to view this content.


    --EDIT--

    Noticed an error when the repeated row went all the way to the top, corrected the logic of the delete statment to not fail.
    Attached Files Attached Files
    Last edited by GeneralDisarray; 05-09-2012 at 12:24 PM. Reason: CORRECTED ERROR FOR REPEATED TO TOP ROW CASE

  5. #5
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Deleting duplicates as well as associated originals

    Hi,

    Thanks for the code, but it seems to run into problems.
    The Macro1 section always returns an error when I try to run it saying "Variable not defined", highlighting "Sub Macro1()"

    When I import the example, it also does not delete the two 7912L entries though it deals with the others just fine.

    I am running it inside the workbook you uploaded

    When I then modify your macro for my actual large data (replacing B with A, C with B, D with AA and E with AB and increasing the max from 27 to 13412), the general disarray sorts them so that duplicates are all next to each other but does not delete them at all.

    This may be because the duplicate information is not entirely complete: The entries in the 4 columns marked for comparison are always there, but contents of other columns vary but should be ignored as I want the rows deleted and only those 4 columns compared to determine if they are identical.

    I have attached a new example workbook. I have hidden all the irrelevant columns, but if you unhide them (they look daunting!) it gives you an idea how a lot of thinks are non dup, dup and blank and that's why they shouldn't be considered in the criteria.

    While the new example only has short of 100 rows total, the 13413 is the actual row count - I can edit that into the macro.
    Attached Files Attached Files
    Last edited by dip11; 05-10-2012 at 06:41 AM.

  6. #6
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Deleting duplicates as well as associated originals

    Quote Originally Posted by dip11 View Post
    Hi,
    When I then modify your macro for my actual large data (replacing B with A, C with B, D with AA and E with AB and increasing the max from 27 to 13412), the general disarray sorts them so that duplicates are all next to each other but does not delete them at all.

    Yeah, that's a huge difference. I had the thing compare columns 2,3,4,5....can change it to compare 1,2,27,28 also, i was stepping backwards in the loop...change the i=j to i=j+1 and the thing seems to work on your example. WHen you run the general disarray macro on what you just posted, i'm left with just 3 rows.



    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    06-10-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    341

    Re: Deleting duplicates as well as associated originals

    Worked brilliantly - at first I thought excel was having a fit, but the result was exactly what I wanted, thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1