We help IT Professionals succeed at work.

Excel "Subtraction in Subtotaling"

apitech
apitech asked
on
Hello:

In Excel 2010 32-bit, I'm going to be creating an Excel-based macro that will launch the creation of a pivot table.  After I have created the pivot table, I need for the macro to subtract the second row of data from the first row of data.  

The first column is ITEM, the second is ITEM DESCRIPTION, the third is LOCATION, and the fourth and final column is QTY.

There are two locations--location 1 and location 2.  Each item has these two locations.

There will be two rows in each set of data, so it will need to be some sort of automatic subtraction for the entire spreadsheet of data.  Normally, I could do this by clicking the subtotal button.  But, it's not "totaling" that I want.  It's subtracting.

Thanks!  Any help is much appreciated!

Apitech
Comment
Watch Question

You may be able to use the GETPIVOTDATA() in-cell function on both rows within the resultant Pivot Table & perform the total/calculation you require.

If you cannot establish the (sub)total(s) you require, is there any chance of providing a sample workbook with the layout of the resultant Pivot Table &/or the source data used to generate this Table?

BFN,

fp.
Rob HensonFinance Analyst

Commented:
I have often found the easiest way to get a field into a pivot table is to create the data in a column with the original data set and then include that column in the pivot.

Would you be able to calculate the required figure per line of data in the original data table?

If not you might be able to use a calculated field/item in the pivot table.

A calculated field allows you to use multiple fields eg using Sales Value & Sales Volume could give you £/Kg by dividing one by the other.

A calculated item allows you to use entries from the same field, more education needed on my part for this as I have used only calculated fields.

Thanks
Rob H
Rob HensonFinance Analyst

Commented:
Try this site for lots of Pivot table info:

http://www.contextures.com/tiptech.html#P

Thanks
Rob H

Author

Commented:
Hello:

Thanks, for the responses!  I did not make myself clear.  I do not want this math done in the pivot table.  The formula for this math, will actually, be used after the creation of the pivot table where I will be copying the data to a separate spreadsheet.

So, the math will be done on that separate spreadsheet--not in the spreadsheet of the pivot table.

I apologize, for the confusion.

Ultimately, the macro will create the pivot table and the "post-pivot table" math.  For that reason, I need a formula that will subtract row 2 from row 1, based on the columns that I mentioned at the beginning of this posting.  

This formula, also, will need to be used "across" that entire spreadsheet and will need to be "dynamic", because there is no telling how many rows of data there will be at any time.

Thanks!

Apitech
Hi Apitech,

The GETPIVOTDATA() in-cell function I mentioned is used following the creation of the Pivot Table to retrieve data from (& subsequently perform arithmetic calculations, if desired) on the resultant contents of the Pivot Table.

It can be called within Visual Basic for Applications code with this syntax...

Range("<cell reference").PivotTable.GetPivotData(...)

Background reading:

[ http://office.microsoft.com/en-us/excel-help/convert-pivottable-cells-to-worksheet-formulas-HA010096303.aspx ]

BFN,

fp.
I know the topic is closed, but IMHO, the easiest way to get the syntax for GetPiovtData() is to select a cell outside of the Piovt Table, press =, then click on the cell in the Pivot Table.   Excel will use the GetPivotData() function for the cell.  (It can then be used in your Macro, on another worksheet in a formula, etc.