+ Reply to Thread
Results 1 to 23 of 23

Summing duplicates excel.

  1. #1
    Registered User
    Join Date
    06-07-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    28

    Summing duplicates excel.

    Hi,

    EDIT: Attached example workbook. 3 tabs - 'Starting Point' is unprocessed. 'What I Want' is how I want the data to look when processed. 'What I Get' is what happens when I use excels consolidate tool.

    I've read a few forums/webpages on this subject but none of them seem to cover my issue.

    I have a list with multiple columns with text and numbers (a parts list) that has the same parts repeated throughout the list.

    1.JPG

    I want to consolidate this list to look this this (i.e. only sum the columns highlighted green):

    2.JPG

    But what I actually get is this:

    3.JPG

    Where ALL the columns are being summed and the text disappears.

    Can anyone tell me if there is an easier way of doing this than manual going through adding up the quantities and the deleting the duplicates?

    Thanks,
    Luke.
    Attached Files Attached Files
    Last edited by stax67; 06-07-2019 at 08:26 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,484

    Re: Summing duplicates excel.

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Alan


    Have you debugged your code?
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    06-07-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    28

    Re: Summing duplicates excel.

    Thanks Alan. I have attached an excel file to my original post.

    Cheers,
    Luke.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,484

    Re: Summing duplicates excel.

    Luke
    I created a Pivot Table in the attached that resembles what you are looking for.
    Attached Files Attached Files

  5. #5
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,908

    Re: Summing duplicates excel.

    Firstly unique extract.
    "c16"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    ctrl+shift+enter
    Above formula copy paste upto "H" column. & column "J" & "K"
    "I16"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    "L16"
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  6. #6
    Registered User
    Join Date
    06-07-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    28

    Re: Summing duplicates excel.

    Thank you both, I will have a play witht the examples you've posted.

    avk - I'm trying to understand what going on in the formula below but I'm not sure I understand, could you please explain a little for me?

    =IFERROR(INDEX('Starting Point'!C$3:C$32,MATCH(0,COUNTIF(C$15:C15,'Starting Point'!C$3:C$32),0)),"")

    Thanks,
    Luke.

  7. #7
    Registered User
    Join Date
    06-07-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    28

    Re: Summing duplicates excel.

    Anyone able to explain how these formulas are working? I'm struggling to get my head around them. (See avk's post above)

    =IFERROR(INDEX('Starting Point'!C$3:C$32,MATCH(0,COUNTIF(C$15:C15,'Starting Point'!C$3:C$32),0)),"")

    =SUMIF('Starting Point'!$C$3:$C$32,$C16,'Starting Point'!$I$3:$I$32)

    Thanks,
    Luke.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Summing duplicates excel.

    Easy one first:

    =SUMIF('Starting Point'!$C$3:$C$32,$C16,'Starting Point'!$I$3:$I$32)

    Red: for every cell in this range
    Orange: that is equal to this value
    Black return the correspondng vin I2:I32 and add them up.
    Glenn



  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Summing duplicates excel.

    IFERROR(INDEX('Starting Point'!C$3:C$32,MATCH(0,COUNTIF(C$15:C15,'Starting Point'!C$3:C$32),0)),"")

    Red: look in this range and see if any cells contain the same as
    Orange: this range.

    It will return a string of 0s or 1s. Zero if it finds matching values, 1 if it does.

    Cyan: return the row number of the first zero it finds. In the first cell it will always be the first row, as you haven't treturned any unique values yet.

    Blue, return the corresponding value from column C.

    Black, if an error is returned, return a blank instead.

    Personally, I'd use this instead:

    =IFERROR(INDEX('Starting Point'!C$3:C$32,MATCH(0,INDEX(COUNTIF(C$15:C15,'Starting Point'!C$3:C$32),),0)),"")

    the bit in blue removes the need to set the formula as an array. Ordinary enter will work fine.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-07-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    28

    Re: Summing duplicates excel.

    Thanks Glenn, that's great.

    I have one more question but I should give you a bit of background. 'Starting Point' is a list copied from a database of parts for several orders.
    An order can contain multiple products that use the same parts so the 'What I Want' tab shows is the sum of parts for each order.

    The list copied from the database will change length each time so I would like to be able to change the range (from 'Starting Point' tab)
    of the formula using a number in a cell so that I can type in the number of items in the list and have the formulas on 'What I Want' tab have
    there ranges adjusted to suit (Unless there's a way to have this happen automatically)

    e.g. 'Starting Point'!B$2:B$XX where XX is a cell value, so if X = 100 the formula would be 'Starting Point'!B$2:B$102.

    Thanks,
    Luke.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Summing duplicates excel.

    That seems a bit clanky. Are you searching by order number? If so, then wouldn't it be nice to have a dropdown box with a list of unique order number. You choose one and the whole thing populates??

    See sheet for a sample. happy to explain. If you like it, I need to know roughly... the maximum number of rows in Starting Point and roughly the maximum number of unique order numbers you will have.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-07-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    28

    Re: Summing duplicates excel.

    Thanks Glenn, that's nice.

    Is there a way to show all orders? (If not I could just you the 'What I Want' tab for avk's version to show all and yours on a tab to filter by order).

    Thanks,
    Luke.

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Summing duplicates excel.

    Can be done. I'll look at it later.

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Summing duplicates excel.

    Hi. I was having a senior moment... Take a look. You still need to answer my Qs in Post 11, before we can finalise this.

    BtW, I accidentally deleted your two named ranges!!
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    06-07-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    28

    Re: Summing duplicates excel.

    Thanks Glenn, this is great.

    To answer your questions, the max rows is 100 and the max order no's is 15.

    Do you mind giving a quick explanation as to what's happening?

    Thanks,
    Luke

  16. #16
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Summing duplicates excel.

    Sure. Tomorrow morning. Enjoying some heady Spanish wine now and don't want to mess up!

  17. #17
    Registered User
    Join Date
    06-07-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    28

    Re: Summing duplicates excel.

    Hi Glenn,

    Sorry didn't notice earlier but when I filter by order the quantities don't change, e.g. component 602535 is on 203191 x 7, 203193 x 6 & 203194 x 7 but when I select any of these orders from the drop down the quantity stays as 20.

    Thanks,
    Luke.

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Summing duplicates excel.

    Ok. I'll add it to the list.

    G.

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Summing duplicates excel.

    You have 5 Named Ranges. I managed to delete your survivingone, so you'll have to add them both back in!!

    Four are exactly the same, autmatically adjusting the range in a column (e.g. column C) from C4, where the data start to the last non-empty cell in the column. As the number of rows increase/decrease, the range compensates automatically. They all look like this:
    =Raw!$C$4:INDEX(Raw!$C:$C,MATCH(1E+100,Raw!$C:$C))

    The different one, does exactly the same, but looks different as it needs to deal with formula blanks in the range. A formula blank is not the same as an empty cell. It provides the dropdown list for D1 on the report sheet. It takes the results of the formula (explained previously) to produce unique order numbers. So the list contains "All", unique order numbers and formula blanks. The setup of this range uses LEN(range)>0 to select only the non-blank cells in the range. It looks like this:

    =Raw!$P$2:INDEX(Raw!$P$2:$P$27,SUMPRODUCT(--(LEN(Raw!$P$2:$P$27)>0)))

    The SUMIF formulae you had have been replaced by SUMIFS:
    =IF(C4="","",SUMIFS(Quantity,Components,C4,Orders,IF($D$1="All","<>",$D$1)))

    so that if ALL is selected, it will sum all the non-blank cells in the range, otherwise it only sums those = D1.

    Finally, the big formula:

    =IFERROR(INDEX(Raw!C:C,AGGREGATE(15,6,ROW(Components)/((Orders=IF($D$1="All",Orders,$D$1))*(COUNTIF($C$3:$C3,Components)=0)),1)),"")

    Red: If D1 = All, return all, otherwise return only those equal to D1
    Orange: providing they have not already occurred in the range C$3:C3
    Cyan: return the row number
    Cyan: in ascending row order
    Purple: return the first instance
    Dark green: finally returning the corresponding value from C:C
    Blank: returning a blank when valid results run out.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    06-07-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    28

    Re: Summing duplicates excel.

    Thanks Glenn, that's spot on.

    Just one little thing when I display all orders the top row, comp no. 602535, it say 17 as the quantity but should be 20.

    Thjanks,
    Luke.

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Summing duplicates excel.

    There's no fool like an old fool. I patiently went through all the named ranges in the data body, changing the start row from row 3 to row 4. Now... I have no idea why I did that. Reversed. They all start in row 3 one again!!
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    06-07-2019
    Location
    UK
    MS-Off Ver
    2013
    Posts
    28

    Re: Summing duplicates excel.

    Glenn, this is perfect, does exactly what I need, I've been manually do this for a couple of months so it's mde one hell of a difference.

    Thank you so much for all your time and help.

    Cheers,
    Luke.

  23. #23
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: Summing duplicates excel.

    You're welcome. Thanks for the kind words and for the rep.

+ 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. Summing duplicates in an array
    By obrieh in forum Excel General
    Replies: 1
    Last Post: 01-25-2016, 01:10 PM
  2. [SOLVED] Help with Summing Duplicates, deleting rows after summing and filling col. for unsummed
    By solidrock1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-04-2014, 09:11 AM
  3. [SOLVED] Deleting duplicates and summing totals
    By JetRich in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-28-2014, 03:00 PM
  4. [SOLVED] add two list togeter and summing duplicates
    By DRFILL in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 12-17-2012, 03:17 AM
  5. [SOLVED] Summing unique values in rows with duplicates
    By mchauho in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-24-2012, 06:20 PM
  6. Duplicates and summing totals
    By KRayKeeling in forum Excel General
    Replies: 2
    Last Post: 07-12-2007, 01:49 PM
  7. Summing duplicate entries and non duplicates
    By Big H in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-17-2005, 07:05 PM

Tags for this Thread

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