Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A Pivot Table challenge--I can't do it, can you?
I need some help getting a particular result in a Pivot Table.
I have an Excel list of 19,000 purchase orders. Included in each purchase order record (row) is the vendor name as well as the department and division of the company that placed the purchase order. (Each division is made up of numerous departments.) I'm trying to answer the following question: How many departments in each Division used a particular vendor? Please note that I'm *not* looking for the number of orders placed with each vendor--I'm looking for the number of departments that placed at least one order. This is best illustrated with an example. Imagine this set of data. (If this is hard to read, copy to a text editor with fixed-width font--my apologies.) Vendor Department Division Alpha Sales Asia Alpha Sales Asia Alpha Finance Asia Alpha Finance Asia Alpha Finance Asia Alpha Finance Europe Beta Sales Asia Beta Mfg Europe I want to produce the following Pivot Table Sales Finance Asia Div Tot Finance Mfg Europe Div Tot Grand Total Alpha 1 1 2 1 1 3 Beta 1 1 1 1 2 I cannot figure out how to do this in a Pivot Table. If I set the department as the data field of the Pivot Table and use the count function, I get the number of purchase orders each department places. Not what I want. If I use a function that gives me "1"s in the data cells (e.g., adding a field of "1"s to the database and using the "Max" function to aggregate data), then the PivotTable won't use the Sum function to create the subtotals and Grand Total (I get "1"s there as well). In a Pivot Table, is it possible to use a different function to total the rows than is used to produce the individual values in the rows? Can anyone suggest a Pivot Table method that will produce the results I seek? Outside the Pivot Table world, I think I can do it with repeated applications of the SUBTOTAL command, but it would take too long to execute SUBTOTALS with 19,000 records. Any help is appreciated. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Copying values from pivot table to cells outside pivot table | Excel Discussion (Misc queries) | |||
Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions |