Use Pivot Table to Sum Most Items and Subtract Only a Few

[Guest]

I am wondering if there is a way to use a Pivot Table to Sum up all items by
‘Type’ (in Column D), which contains the ‘Total Charge’ for a job (in Column
E), and then subtract out a portion that has not been paid, such as ‘Balance
Due’ (in Column J). Creating a PT with the Type and the Total Charge is
simple, but some clients don’t pay on time and thus there may be a small
Balance Due at the end of each month. The sum in the PT is too high for some
of the accounts that are not paid on time. Is there a way to account for all
the items?


Regards,
Ryan---

 

[Guest]

Thanks to anyone and everyone who looked at my post. I just figured out what
to do. I added a helper column (Column M) and then took the difference
between charges in Total Job (Column E) and Credit Card (Column H). The Type
(Column D) described the kinds of orders handled, and each corresponded to a
unique Job Number (Column B). It is sort of confusing. Well, basically, I
filled in all blank cells in Type (Column D) with the following code:

Range("D4").Select
Range(Selection, Selection.End(xlDown)).Select
Range("D48000").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

These Job Numbers typically had data in just one row, but if the total was
not 100%, there was related data in lower rows, and these were not being
picked up in the Pivot Table because Type was only listed once in the first
row for each Job Number. Once I made the modifications described above, the
issue was resolved. The long and short of it was that I controlled
everything in the spreadsheet and just let the Pivot Table do its thing. If
anyone is interested in this, and needs further clarification, let me know.

Regards,
Ryan---