Results 1 to 4 of 4

Thread: delete duplicate row

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    delete duplicate row

    In excel sheet have:

    col A col B col C
    AAA 1 0
    BBB 1 0
    AAA 1 0
    AAA 1 0
    ... ecc

    possible to check the 3 colum and delete duplicates rows, with msgbox alert, similar:

    Attention you have 2 duplicate row for :AAA 1 0, can you maintain only the unique row?

    if user choice yes, delete the two lines, and maintain the only unique row...

    new scenario after yes from user

    AAA 1 0
    BBB 1 0

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,422

    Re: delete duplicate row

    What about other duplicate rows later on?

    Like

    col A col B col C
    AAA 1 0
    BBB 1 0
    AAA 1 0
    AAA 1 0
    BBB 1 0
    CCC 1 0
    AAA 1 0
    BBB 1 0
    CCC 1 0
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: delete duplicate row

    there is a way of identifying the duplicates that allows you to markup your sheets and then simplify them.. it works no matter how distant the duplicates are.

    the psudocode is as follows

    start macro
    add/use identification column "IC"
    set "IC" values to 1 where data exists on row
    start at "IC" row "1" - first appropriate data row
    walk the rows until you reach "ICrow"=nul you have not put 1 here
    during walk if the current row "IC" =2 step forward do not start the comparisons
    if "IC"=1 compare the row excluding "IC" with the following rows until "ICrow"=nul
    if the rows are equal mark "ICrow"=2 this is a duplicate

    having completed the walk through the data laden rows

    method 1
    delete every row whoes "IC" is 2
    close up the rows

    method 2
    sort data laden rows by the "IC" value
    the "IC"=1 rise to the top and so deletion of "IC"=2 results in a contiguos table of unique rows

    job done
    end macro

  4. #4
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    985

    Re: delete duplicate row

    You could just use the Range.RemoveDuplicates method

    to identify do something like this
    copy sheet
    remove duplicates on copied sheet
    compare sheets
    if they are not the same there must have been duplicates removed(maybe put the ranges into arrays and compare bits)
    add extra code if you want to point out individual records
    if they want to remove the records then delete first sheet replace with new sheet
    no looping needed
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


Posting Permissions

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



Click Here to Expand Forum to Full Width