Move Duplicate Rows to New Sheet

  • I have a rather large worksheet of 40000+ rows. I need to find duplicate rows based off of three separate columns. The duplicates then should be moved to a new sheet, and the row deleted in the source sheet. I've got a working macro that just loops through the whole sheet a row at a time but it takes forever, and I'm hoping their is a quicker solution. Any ideas would be greatly appreciated.

  • Re: Move Duplicate Rows to New Sheet

    Hello derek,

    Welcome to Ozgrid.

    You can accomplish this using a couple of helper columns and autofilter.

    Try adapting the code below. It uses two helper columns and autofilte.

    In the first helper column, say "D", a formula is inserted to join the three cells into a string
    In the second helper column, say "E", a formula us used to count the occurrences of the string.
    Because the formula return a count of the occurrences any value greather than one is a duplicate.

    Last, autofilter is applied to filter on values greater than one, then we copy/delete rows.

    Always test code on a back up copy of the workbook.
    Adjust range and sheet references as needed for your workbook.

  • Re: Move Duplicate Rows to New Sheet

    Thanks for the code, it works great. Currently, it's deleting all the duplicatets and I need to keep one of each. I may just need to change some value in the code but don't see anything that would accomplish that. Thanks.

  • Re: Move Duplicate Rows to New Sheet

    In my testing, the code does not delete duplicates - it leaves one row for each occurrence.

    Consider uploading a sample workbook if you don't get it going right.
    Remove all sensitive data from the workbook before uploading.

  • Re: Move Duplicate Rows to New Sheet

    Still having trouble getting it to leave one occurence. Here is an example of the file I'm using. There are more columns but these are the three I need to check on for duplicates. I was also having problems with the range not including the two helper columns which causes an out of range error. Thanks.

  • Hi-

    I'm commenting on this old post. I needed to do this exact thing and the code provided here works for me. However I am having the same issue as the original Poster--The code is moving all duplicates to "Sheet2" rather than keeping the most recent occurrence in "Sheet1". There was not a solution posted for this. Does anyone have an idea for a modification to this code so that the most recent occurrence in a given set of duplicates will remain in the original sheet? Thank you.

  • Afternoon RRigs,

    Myself I would load the data into an Array and then use a dictionary to test for duplicates. Far faster if you have allot of data to go through.

    That way anytime the next row encounters a duplicate that already exists in the dictionary, you can use the code to move it to another sheet keeping the original.

    Would have to see sample of what your trying to accomplish to give better direction considering the age of the original post.

  • royUK

    Closed the thread.