Hi Debra,
Thanks for your help. I think this solution treats the data something like
an accounting General Ledger right? When I first set up this spreadsheet. I
considered doing it like that, (Well proven concept) but with the sheer qty
of records I was worried about running out of rows. So I put all the
conditional arguements in one row for each record. Now I can see some
disadvantages in my thriftiness but now to rework the rest of the system
around the concept will require a fair amount of tweaking. (there is lots of
data input logic that translates user input into database records). I am a
little hesitant for now to do this with a working system but it is due for an
overhaul soon so I will consider changing to your suggestion. I want to see
if it will make it run any faster so I will test it in the next day or two. A
good thing you noted in your solution to have a proper record ID. I have been
using the time/date stamps for uniqueness but there are problems with that
approach.
In the mean time, I wonder if you can point me in the right direction on how
to filter this data in a pivot table by start and ending date range. My data
all has date/time stamps (unique for each record) and just putting the date
field in the page field only shows one date. I need all records between two
dates. I am needing something that is easy for users to plug dates in a
couple of cells and have to PT change the data set.
BTW, I really like your contextures website. Lots of useful and accessible
tips. I refer to it all the time. Thanks for that.
Rgds,
RDW
"Debra Dalgleish" wrote:
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