+ Reply to Thread
Results 1 to 5 of 5

Deleting Duplicate values while also adding to sum

  1. #1
    Registered User
    Join Date
    06-26-2022
    Location
    Canada
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    Posts
    2

    Deleting Duplicate values while also adding to sum

    Good afternoon, im attempting to Delete duplicate values while also adding the sum of each duplicate.
    I have attached both the original formatting, and what the result would need to look like. Does Excel have any functions to accomplish this? this is a small sample size, i would need to scale this to over 100K+ lines.
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Deleting Duplicate values while also adding to sum

    You'd probably need VBA in order to collate and delete the original data.

    Here’s two formulae that will collate the data.

    F2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Select and copy the output. Then Paste Special Values. Finally, if required, delete the original data.

    Should be easy enough to put into a macro.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    06-26-2022
    Location
    Canada
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 32-bit
    Posts
    2

    Re: Deleting Duplicate values while also adding to sum

    Thank You TMS. I appreciate you. Are there any good resources to learn more about the VBA coding? We had assumed we were going to have to use pivot tables.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Deleting Duplicate values while also adding to sum

    Well, you probably could use a Pivot Table to collate the values. But my first thought was to use formulae. I have a version of those formulae that might be easier to code.

    And, that said, there are (probably) people on this forum who could combine my formulae into one

    I'll look at the code for you.

    Note: whatever method you use to collate the data, you will need to convert the formulae/pivot table to values before you can delete the original data.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Deleting Duplicate values while also adding to sum

    See the code below:

    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. [SOLVED] Adding Duplicates and deleting duplicate rows
    By jimpatel1993 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-19-2021, 06:19 AM
  2. Help with deleting duplicate values in a list
    By rorshil in forum Excel General
    Replies: 2
    Last Post: 03-01-2019, 10:22 AM
  3. Need help deleting duplicate values
    By harrisonD in forum Excel General
    Replies: 4
    Last Post: 05-26-2017, 06:50 PM
  4. [SOLVED] deleting all duplicate values in a spreadsheet
    By bawlmer in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 02-19-2014, 02:10 PM
  5. deleting duplicate values
    By Suraj3825 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-20-2013, 08:37 AM
  6. Deleting Duplicate Values
    By epreuss in forum Excel General
    Replies: 3
    Last Post: 07-17-2012, 12:01 PM
  7. [SOLVED] Do until loop (deleting duplicate values)
    By buntalan80 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-31-2012, 05:50 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