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!
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...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
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.
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})))
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!
It will (probably) be another Excel version compatability thing.... I will come back in a moment with a longer formula that (should) work...
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??
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!
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.
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!
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks