Remove Or Delete Duplicate Rows

  • Hi all,


    I have 6 columns on my data sheet. F column is Amount column.


    If there are rows where all the columns A,B,C,D and E duplicate then I need that row to be deleted, BUT before that the value from the column F should be summed to the duplicating row's F column.


    I attached a simple example file, where you can see my point better. (I need the code to work with thousands of rows, and not only with 5 rows which i put on the axample)


    Hopefully someone has ideas to help me out here


    -Rajala-

  • Re: Deleting Duplicating Row And Summing One Of It's Column


    SUM function to sum THEN use AdvancedFilter to remove duplicate rows.

  • Re: Remove Or Delete Duplicate Rows


    Thanks Dave.


    Unfortunatelly your answer doesn't help me alot. Maybe you didn't check the exampe file?


    First of all, I don't know how to pick the correct cells to SUM and secondly I need the rows to be removed, not filtered.


    Preferably this should be done with VBA code.


    I hope someone can still help me out here.


    -Rajala-

  • Re: Remove Or Delete Duplicate Rows


    Perhaps next code could help, to be checked with larger data:

    Files

    • OZ102552.zip

      (11.65 kB, downloaded 41 times, last: )

    Triumph without peril brings no glory: Just try

  • Re: Remove Or Delete Duplicate Rows



    Rajala, read daves post again, and atleast try to implement his solution before dismissing it. Filtering rows is one of the best methods to delete rows. If you try to use daves solution we will help you when you get stuck.

  • Re: Remove Or Delete Duplicate Rows


    I'm interested in seeing how that will work. I understand that you will have the sum of all the records after the duplicates are removed but I believe Rajala needs the values summed at the duplicate level (essentially a group by for the first five columns and a sum on the sixth). The first thing that comes to my mind is SQL via ADO:



    That code puts the result set in a new worksheet leaving the original values intact. It could be quickly edited to replace the original values with the result set, though.

  • Re: Remove Or Delete Duplicate Rows


    Quote from MAVYAK

    I'm interested in seeing how that will work.


    :) Just for you Mavyak:


  • Re: Remove Or Delete Duplicate Rows


    You simply filter for unique rows to another location, then move the unique rows back. AdvancedFilter has an option for unique lists or table rows. See my 1st post for example.

  • Re: Remove Or Delete Duplicate Rows


    Thanks a lot guys, it's working.


    So far I'm using Readify's code, because that was easiest for me to understand.
    But thanks a lot to Dave Hawley, Mavyak and PCI aswell.


    Regards,
    Rajala