• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

pivot table for stock to and from

J

jason carter

New Member
I have data listing stock to and from and need to create a pivot table of it.
I'd like the pivot table to list each category (ABCD) and then a value being the sum of all TO's minus all FROM's.

My data looks like this.


DATE AMOUNT FROM TO
1/07/2014 10 A D
16/07/2015 20 A C
31/07/2015 30 A C
15/08/2015 40 B A
30/08/2015 50CB
14/09/201560D
A
 
Thanks, that was the answer I was fearing. The problem with formulas is it limits the ability to analyse the data in detail and every time a new category is added I need to change the summary table to include it. The actual sheet has around 50 categories (and growing) and thousands of transfer entries.

I've so far found a work around but it is very limiting,
I use two pivot tables one under the other (one for TO and one for FROM with the FROM table having a function to sum the negative amount) and then to have another pivot table to summarise those tables. this is ok for simple summaries but loses a lot of data that is needed for analysis. the final pivot table can only display the amount and the category. generally to perform an analysis I am setting up data tables to run what-if analyses to get the answers I want, it is very clunky.
 
All might not be lost. With a slight rearrangement of your data, then you can get the pivot you want.

And the rearrangement could be done by a simple macro. It just needs to be set out like the attached file. You may need to upload a sample file with dummy data if you need help with coding up a macro to transform the data, but it's no big deal.
 

Attachments

good thinking. I've kept my original input sheet and then written a macro to transfer it all to a new double-entry sheet in the rearranged format keeping all the extra data fields. I just click an update button and it all works beautifully, thanks.
 
You must log in or register to reply here.
Back
Top