Duplicate Merge

  • Hello,


    I have this report that I need some help with regarding a macro.


    I would like this hotkey-based macro to scan a range, find duplicates, then delete the duplicates underneath the first entry, but copy/paste the contents from column F in column F of the first duplicate it found. Using a comma as a seperator if this is possible. The macro would determine duplicates based on columns D, E, F and H.


    I have attached a test file as an example.


    Thank you so much for the help!

    Files

    • testcopy.xlsm

      (9.49 kB, downloaded 78 times, last: )
  • Re: Duplicate Merge


    Below is a macro that I have. It essentially locates duplicates and pastes the copy on the line above it. However, I can't get it to just paste part of the entry. Hopefully, someone can clean this mess up for me and finish it up.


    Thank you.


  • Re: Duplicate Merge


    Try this


    I have attached your workbook and added some entries at the end to ensure that it should do what you are after. I would like to see it tested on a larger data range so let me know how you go.

    Files

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Duplicate Merge


    That works wonderfully! I am curius if it could be adjusted to column F though. Currently, it combines column H and I am unsure how to modify this correctly.


    Thank you for the help, you've got me that much closer to completing this.

  • Re: Duplicate Merge


    Try this - I just added in some error handling as well and some code to make the transition to the unique list a little smoother.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Duplicate Merge


    PS: I just read your original post - You did say Column F in your first post so I don't know why I got column H in my head....Sorry about that. :)

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Duplicate Merge


    Hi smuzoen,


    I tested the new macro, but I am curious as to how it finds the duplicates. It seems to compile numerous duplicates at the last line of data where things were not matching to begin with. For example, when you run the macro, the last line is 'smith, robert' and there are many things under column F that did not match to begin with.


    Thank you for the help :smile:

  • Re: Duplicate Merge


    I concatenate columns 4,5,6,8 and use that as the comparison string - Then I create an array of data that is unique then compare that unique array to the original array that contains all the data. Send me a sample workbook where the matching should not occur and I will take a look for you. There is obviously a contingency I have not accounted for. Send a sample where there is matching occurring that should not and I will adjust the code.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Duplicate Merge


    In my original post I had an attachment and I said in it

    Quote

    I have attached your workbook and added some entries at the end to ensure that it should do what you are after. I would like to see it tested on a larger data range so let me know how you go.


    Did you place my macro into the test workbook I uploaded or the original - I put an extra 5 lines at the end just to ensure the code worked - try the attached workbook with your original data and see if the problem is still persists. I added the last line of your data in 5 times at the end to your original workbook to test the code - see your original workbook with the amended code.

    Files

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Duplicate Merge


    Ahh, that's my mistake entirely! I'm sorry about that. I do have one additional question. If I wanted to remove a column that would 'match', would I just adjust this line in the code?


    Code
    1. compStr = UCase(Trim(Cells(k, 4)) & "," & Trim(Cells(k, 5)) & "," & Trim(Cells(k, 6)) & "," & Trim(Cells(k, 8)))


    by removing either column 4, 5, 6, or 8?


    Thank you!

  • Re: Duplicate Merge


    Yes you are correct - just adjust this line to the column you do not want to compare. This string is just a concatenation of columns 4,5,6, and 8 separated by a comma ",". This is the string that goes into an array's for comparison.
    Let me know if you run into problems.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Duplicate Merge


    Removing Column H from the comparison the code should be as follows - see attached workbook.

    Files

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Duplicate Merge


    This tiny code should do the job....

  • Re: Duplicate Merge


    Thank you all for the help with this macro.


    smuzoen, the new code gives me an out of range error.


    jindon, this works just as intended, much like smuzoen's before I started to ask for the modifications. Along with the comma, can I also specify a space after the comma seperator? It's a tiny nitpick of mine, but not needed at all.


    Thank you both!

  • Re: Duplicate Merge


    Nice code Jindon. :)

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Duplicate Merge


    Quote from smuzoen;589754

    Nice code Jindon. :)


    Thanks


    Dictionary object has CompareMode property that switches non/case-sensitive comparison for its key.
    And in this case, rows will never exceed than the existing data, so I re-used the same array that saves the resource.