L
LawrenceHG
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.
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.