Excel Pivot Table Consolidation soing subtraction

I have one sheet for products purchased and another one that sold. I want to make a pivot table from two sheet to get current inventory for each product. But options are only sum, average etc there is no subtraction. How could I do that? I am using Excel 2007. Thanks

Gok Demir

Posted 2010-02-05T15:30:15.010

Reputation: 133

Answers

The simplest would be to store the number sold as negative numbers.

If they're currently all positive numbers, you can easily change them to negative by typing -1 in an empty cell, copying it, and doing a Paste Special - Multiply on the number sold column.

Martha

Posted 2010-02-05T15:30:15.010

Reputation: 900

THanks but not looking that. – Gok Demir – 2010-02-05T15:56:53.327

Eh? (Do you mean "this won't work for me"? If so, why not?) – Martha – 2010-02-05T16:22:10.740

Sounds like your problem is that the data is in two places. Possible solutions: a) Make 2 Pivot Tables for the two chunks of data. Use GetPivotData formulas to extract the values for the matching items (eg by product code) and subtract them.

b) Carry some of the data across from one sheet to the other using eg VLOOKUP to fetch the starting inventory figure across to your sales table, but only for the first occurence of a product being sold. Include this extra column in your sales pivot and add a calculated field to subtract them.

I prefer (a) but you would have to do some prep to create a list of unique product codes (paste sales + inventory together and remove duplicates then sort) so you can point the GPD functions at those cells.

AdamV

Posted 2010-02-05T15:30:15.010

Reputation: 5 011