+ Reply to Thread
Results 1 to 11 of 11

Consolidate, but Calculate the Difference (Subtract During Consolidate)

  1. #1
    Registered User
    Join Date
    03-09-2021
    Location
    Arkansas, USA
    MS-Off Ver
    2013
    Posts
    5

    Consolidate, but Calculate the Difference (Subtract During Consolidate)

    As per the title, I have a sheet with book titles and the sales. I want to consolidate the data such that it shows me a list of the books and the DIFFERENCE between their sales numbers instead of calculating the sum. My sheet is a small sample. Actual sheet has 5000+ rows of data, though the book titles should only appear twice at most (sometimes once if it is a new book to the list).

    Thanks!
    Attached Files Attached Files

  2. #2
    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,104

    Re: Consolidate, but Calculate the Difference (Subtract During Consolidate)

    A bit of explanation would help!!

    If there are two values, that's obvious. If there are 3... what do you expect to see? If there is just 1... what do you expect to see?

    This MIGHT be what you want!!

    G2, copied down:
    =IFERROR(INDEX($A$2:$A$100,MATCH(1,INDEX(--ISNA(MATCH($A$2:$A$100,G$1:G1,)),),))&"","")

    H2, copied down:
    =SUM(INDEX(B:B,AGGREGATE({15,14},6,ROW($B$2:$B$20)/($A$2:$A$20=G2),1))*{-1,1})



    You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

    These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    03-09-2021
    Location
    Arkansas, USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Consolidate, but Calculate the Difference (Subtract During Consolidate)

    Ah, sorry about the lack of clarity. I see now based on your response that I need some adjustment to my thinking. Your response is definitely on the right track! Thank you! To clarify my need:
    1. Titles/sales will never appear more than twice
    2. Titles/sales might appear just once if it is a new entry
    3. If the title appears twice, consolidate and calculate the difference in sales numbers
    4. If the title appears once, then sales is the difference. Ex: jan's dogs should show 14 instead of 0.

    My apologies if this isn't coming across as clearly as I am hoping.

  4. #4
    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,104

    Re: Consolidate, but Calculate the Difference (Subtract During Consolidate)

    maybe this, then:

    Formula 1, as previously. Formula 2:

    =IF($G2="","",IF(COUNTIF($A:$A,$G2)=1,VLOOKUP($G2,$A:$B,2,FALSE),SUM(INDEX($B:$B,AGGREGATE({15,14},6,ROW($B$2:$B$20)/($A$2:$A$20=$G2),1))*{-1,1})))
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-09-2021
    Location
    Arkansas, USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Consolidate, but Calculate the Difference (Subtract During Consolidate)

    Thank you! That works until I change the sales numbers. If I change B2 to 123 then change B6 to 234, the result changes to 0. I tried the "Refresh All" and that didn't change anything. My apologies for probably missing some goofy little step, but any thoughts? Edit: Actually, changing B2 to another number, changes a lot of the others to 0... Weird!

    I seriously cannot thank you enough for your help with this!

  6. #6
    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,104

    Re: Consolidate, but Calculate the Difference (Subtract During Consolidate)

    It will (probably) be another Excel version compatability thing.... I will come back in a moment with a longer formula that (should) work...

  7. #7
    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,104

    Re: Consolidate, but Calculate the Difference (Subtract During Consolidate)

    OK. try this. I have made one further assumption, that I forgot to tell you about... that I am subtracting the value that appears second (larger row number) from that which appears first (smaller row number). is thta correct??
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-09-2021
    Location
    Arkansas, USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Consolidate, but Calculate the Difference (Subtract During Consolidate)

    Getting close! When I paste in real data, the calculation reverts back to all zeroes.

    I have attached a small sample with real data (see attachment).

    As an aside, I will have over 6600 rows of data that will used. So, I can scale this as needed.
    I believe I can edit G2 to reflect the actual number of rows by changing the "100" to the number of rows.
    I am not sure how to edit the formula in H2 to do the same.

    I think we are definitely running into a version issue. I am using Office 2013, unfortunately.

    On another note - can I buy credits to pay you for your time and help with this? How does that work? Thanks!
    Attached Files Attached Files

  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,104

    Re: Consolidate, but Calculate the Difference (Subtract During Consolidate)

    Well, there is one obvious reason why they are all showing zero. There is no difference between any of the matching values. Upload a sheet with data showing where there IS a problem.

    You did not answer my Q at Post 5. I shall repeat it once more.

    Are the higher values always later (further down the sheet) than lower values, or is the data randomly distributed.

  10. #10
    Registered User
    Join Date
    03-09-2021
    Location
    Arkansas, USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Consolidate, but Calculate the Difference (Subtract During Consolidate)

    Heya! My apologies. Long day yesterday and I didn't even realize there was no difference. That was a bad data pull on my end.

    The values will always be higher (except when I am being an idiot) further down the sheet. The values should technically never decrease unless they happen to disappear altogether. That is, an item is removed from the list completely.

    I tweaked the upper limits in each function/formula in order to use all of the rows in my dataset. FWIW, my old computer is NOT happy to be calculating so much data. Haha!

    But, in the end, this got me exactly what I needed. Thanks again for your help and your patience!

  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,104

    Re: Consolidate, but Calculate the Difference (Subtract During Consolidate)

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ 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. Consolidate
    By gabbiano325 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-22-2019, 05:10 PM
  2. Replies: 3
    Last Post: 09-19-2018, 07:01 PM
  3. consolidate duplicates entries for payroll and calculate specific condition
    By micvelo64 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-25-2016, 07:36 PM
  4. Consolidate
    By sujan.das2008 in forum Excel General
    Replies: 0
    Last Post: 05-22-2014, 04:41 AM
  5. [SOLVED] Find common values, Consolidate data and create Worksheet with difference
    By hnpsingh in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-28-2012, 04:22 PM
  6. Simplifying a formula: Consolidate formula to calculate SLA
    By Jennasis in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-29-2012, 06:27 AM
  7. Consolidate
    By Jimmy0306 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-10-2010, 05:41 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