Can PIVOT tables do this?

[Guest]

Cross Posting as I am getting nowhere in General Questions:

I have just started to explore Pivot tables but before I go any farther I
need to check to see if what I am trying to do is in fact a correct use of
Pivot tables.
We have a product price list : Item #, Description, Qty per box, Price per
box and Qty Ordered.
We have 993 different products TODAY but it goes up & down as new stuff
arives / old stuff is sold.

Right now we email or fax a 20 page Word doc to our customers who fill in
the Qty ordered and fax it back to us.

I have copied it to Excel and added a column to calculate Order Cost = Price
Per Box x Qty Ordered.

Out of the 993 items we offer on average an order will have about 50 items.

Can a Pivot Table summaraize just what has been ordered?

if it cannot then I will not go farther in learning Pivot Tables. If yes
then I need to pursue and I will appreciate all tips & pointers

Thanks
(e-mail address removed)

 

[Tom Ogilvy]

I wouldn't see a PivotTable as being the correct solution for this. Once
you have copied the information (and assume it lines up properly in your
spread sheet)


I would see either an Autofilter (data=>Filter=>Autofilter), then select the
quantity column and set to Non Blanks, so only the rows ordered would be
visible. You can then highlight all the rows and do a copy, then paste to
another sheet and only the visible rows will be copies.

the alternative would be an advanced filter, but this would be a bit more
complex and probably not worth the effort.

 

[Guest]

Yes a pivot table can do that. That being said it is not really what a pivot
table is designed for. Pivot tables aggregate large amounts of data into
their respective categories. For what you are trying to do a simple filter
would probably be easier.

If you want to proceed with the Pivot Table then you need to add a new
column to your data. Call it "Included" or something similar. I would assume
that the criteria for whether an item was ordered or not is based on the
quantity column. In the included column add a formula that results in "True /
False" or "Included / Excluded" based on the result in the quantity column.
Drag this item to the top of the pivot table and set it to "Included" or
"True". As an aside the Order Cost could be a caluclted field within the
pivot table and does not need to be a seperate column in the source data.

 

[Guest]

Sorry scratch the part about the calulated field. Stick with your intial
solution for calculating the total. My mistake.