+ Reply to Thread
Results 1 to 11 of 11

Find duplicates rows, sum cells then remove duplcate rows

  1. #1
    Registered User
    Join Date
    05-09-2009
    Location
    Manchester,England
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    58

    Find duplicates rows, sum cells then remove duplcate rows

    Hi All,

    I am looking for a macro that will check the the values in 2 columns and if a match is found sum up the qty in another column and put this value in the row what will be kept before removing the duplicated rows.

    The attached file shows the data and the result expected, my current file is 40k rows and will grow.

    If col B & col C are the same
    Add up the values of col N, based on different dates in Col O
    Put the SUM of col N duplicate rows into the row with the newest (latest) date in col O
    Remove duplicate rows
    The qty Ordered col G should total the Made qty col N and the Shipped qty col E

    Any help would be appreciated

    If you need clarification on anything please let me know.
    Thank you

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Find duplicates rows, sum cells then remove duplcate rows

    Hi, Wagstaff,

    in your sample on Data within the rows 39 to 44 we have the same ordernumber in Column C but different Customers in Column K (same for 22 to 27). In Result there is only one customer (A) - what happened to customer B and what and why should the macro handle this?

    I assume from Result that it could be done starting at the bottom of the rows and keeping the updated information for the last record being found - is that corrrect?

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    05-09-2009
    Location
    Manchester,England
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    58

    Re: Find duplicates rows, sum cells then remove duplcate rows

    Hi Holger

    Thanks for your reply.

    Sorry I autofilled down when I changed the customer name for the sample data, in reality it would be the same customer for the same order number.

    For the same order and line number the only things will change is the ship date , the ship qty, produce date and produced qty.

    The file shows duplicate records if there has been more than one shipment made against the same order and line number and also duplicates if the qty produced was a partial qty of the order.

    So in rows 22 to 27 on the Data sheet it shows 6 rows of data. There is only 2 actual shipments of 24pcs & 72pcs made on the same day which total 96pcs as per the ordered qty column G, but because these 96pcs took 3 days to complete is shows 6 times (3 times for every shipment made).

    The dates completed were
    6 12-Sep-12
    36 06-Sep-12
    54 11-Sep-12

    So I would want the row that is remaing to show

    Ship Date = last date shipped ( to complete the order total)
    Ship qty = 96
    Produced Qty = 96
    Produced Date = 12th Sept
    All other columns details would remain the same.

    Thanks
    Elaine

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Find duplicates rows, sum cells then remove duplcate rows

    Hi, Elaine,

    for the sample the results differ from the sheet Result due to the reason of mulitple Customers but you could at least try and test it to see if the code comes near to what you expect. Please try the code on a copy of the data or the sample:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  5. #5
    Registered User
    Join Date
    05-09-2009
    Location
    Manchester,England
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    58

    Re: Find duplicates rows, sum cells then remove duplcate rows

    Thanks Holger,

    I have tested the code and unfortunately is does not give me the desired result.

    On the rows 22 to 27, it reduces the 6 rows to 2.

    The shipped qty shows one row with 24pcs shipped and one row with 72pcs shipped, this should be one row with 96pcs shipped, if the ship date is different it should use the newest date.

    The qty produced left me with one row of 6pcs made for the 12th Sept and one row with 192pcs for the 11th Sept, this should have been one row with 96pcs made for the 12th Sept

    I am assuming the 192pcs comes from the original data

    6 12-Sep-12
    36 06-Sep-12
    54 11-Sep-12
    6 12-Sep-12
    36 06-Sep-12
    54 11-Sep-12

    So I assume the codes needs to add the qty in Column O if the date in Col N is different.

    If only 2 rows of data were duplicated then both rowa are still showing after the code has run, but the produced qty does change to the correct qty on the 2nd row, so it needs to still delete one of the rows.

    The uniques criteria needs to be against Col B and Col C as the same orders can be used but with a different line number, its only when col B & col C are the same that is considered as duplicate.

    Thanks for the help so far and hope you can assist me further.
    Rgards, Elaine

  6. #6
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Find duplicates rows, sum cells then remove duplcate rows

    Try this:-
    NB:- Test on Trial data, this code will alter the sheet values and remove duplicates.
    Please Login or Register  to view this content.
    Regards Mick

  7. #7
    Registered User
    Join Date
    05-09-2009
    Location
    Manchester,England
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    58

    Re: Find duplicates rows, sum cells then remove duplcate rows

    Thanks Mick,

    I have tested the code and it does nearly everything.

    It was giving me the correct qty's but not neccessarliy the correct date as the dates where not sorted.

    I have added a sort macro to run first on the ship date and made date and once I have run the code your code works perfectly

    Thank you so much!

    Regards, Elaine

  8. #8
    Registered User
    Join Date
    05-09-2009
    Location
    Manchester,England
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    58

    Re: Find duplicates rows, sum cells then remove duplcate rows

    Thanks Mick,

    I have tested the code and it does nearly everything.

    It was giving me the correct qty's but not neccessarliy the correct date as the dates where not sorted.

    I have added a sort macro to run first on the ship date and made date and once I have run the code your code works perfectly

    Thank you so much!

    Regards, Elaine

  9. #9
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Find duplicates rows, sum cells then remove duplcate rows

    Sorry i missed that requirement.
    If you feel like the amended version try this:-
    Please Login or Register  to view this content.
    Regards Mick

  10. #10
    Registered User
    Join Date
    05-09-2009
    Location
    Manchester,England
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    58

    Re: Find duplicates rows, sum cells then remove duplcate rows

    Thanks Mick for the new code.

    When I tested it it didnt give me always the correct date for the made date col, howvever I Changed

    Please Login or Register  to view this content.
    To

    Please Login or Register  to view this content.
    It seemed to work great however further testing highlighted that Column A was not always showing the latest date.

    I tried to mod the code but havent got it to work or even run as I dont fully understand it.

    Can the code leave the latest dates for both the Made Date in column O & the shipped date in column A ?

    If not then I am quite happy with the help you have given.

    Many Thanks
    Regards, Elaine

  11. #11
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Find duplicates rows, sum cells then remove duplcate rows

    Try Adding the line in red, That show add the date to column "A".
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Find and remove duplicates and/or identifiy both rows
    By WiscoKid in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-18-2013, 08:40 AM
  2. Remove rows without Duplicates
    By Apple1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2013, 10:12 AM
  3. Remove Duplicates from Rows
    By dwc in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-24-2013, 06:47 AM
  4. Replies: 1
    Last Post: 10-23-2012, 09:12 AM
  5. Replies: 13
    Last Post: 08-23-2011, 11:00 AM

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