What you want to do could be easily done in a pivot table, if you change
your source data to use two lines per transaction. For example:

TransID Date Item Bin Qty
1 20-2-07 Item1 Bin1 -2
1 20-2-07 Item1 Bin1 2
2 20-2-07 Item1 Bin1 -2
2 20-2-07 Item1 Bin1 2

If your data is similar to your sample, it should only take a bit of
cutting and pasting to create the duplicate set of records, and use
paste special to change the quantities for the source items to negatives

Then, create a pivot table with Bin in the Row area, Item in the Column
area, and Qty in the data area.


RD Wirr wrote:
I have a large table of inventory transaction data that generally looks like
this
Data
Date Item Source Destination Qty
20-2-07 Item1 Bin1 Bin2 2
20-2-07 Item1 Bin1 Bin2 2
21-2-07 Item1 Bin2 Bin3 3
20-2-07 Item2 Bin1 Bin2 1
20-2-07 Item2 Bin1 Bin2 2
21-2-07 Item2 Bin2 Bin3 2

Each transaction includes the Items that are transferred from one Bin to
another and the Source and Destination Bins are named and the Qty of each
item transferred.
I subtract the Qty of items from the source Bin and Add the same items to
the Destination Bin in each transaction
I am trying to make a PivotTable that summarrizes these transactions and
calculates the net Qty of items in each Bin that would give these results
from the data above.
I also need to filter this data within Start and End dates.

PivotTable
Date
Sum of Qty Source
Item Bin1 Bin2 Bin3
Item1 -4 1 3
Item2 -3 1 2

Can anyone give me an idea how to approach this with the PivotTable
calculations?
Thanks in advance.
RDW


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html