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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A Pivot Table challenge--I can't do it, can you?
Hi Lawrence
You will need to add another column to your source table Named Count In D2 enter =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=$B2)*($C$2: $C2=C2))=1,1,0) and copy down Add the Count Column to the source range for your PT, and use Sum of Count as your data field -- Regards Roger Govier "LawrenceHG" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
A Pivot Table challenge--I can't do it, can you?
Taking into account that different POs
can have the same Vendor, Dept, and Div... http://www.freefilehosting.net/download/3ej3g |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
A Pivot Table challenge--I can't do it, can you?
Roger--
All hail thee king of the MVPs! It works. Very clever...I've seen this type of formula before, but I hadn't added it to my bag of tricks...perhaps now I will. I'm having trouble getting it to work on all 19,000 rows...the calculation time is immense, but I verified it on a subset of the data. Thanks for your help. I've really learned something good. "Roger Govier" wrote: Hi Lawrence You will need to add another column to your source table Named Count In D2 enter =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=$B2)*($C$2: $C2=C2))=1,1,0) and copy down Add the Count Column to the source range for your PT, and use Sum of Count as your data field -- Regards Roger Govier |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
A Pivot Table challenge--I can't do it, can you?
Hi Lawrence,
If you can sort the data by Division then Department then Vendor you can add a faster formula than sumproduct: assuming vendor is in column D and the data starts in column 2 then add a Counter column in E2 =IF(D1=d2,0,1) and copy down for the 15000 rows. Then just add the counter column to the pivot: SUM will give you the answer. regards Charles Williams Decision Models "LawrenceHG" wrote in message ... Roger-- All hail thee king of the MVPs! It works. Very clever...I've seen this type of formula before, but I hadn't added it to my bag of tricks...perhaps now I will. I'm having trouble getting it to work on all 19,000 rows...the calculation time is immense, but I verified it on a subset of the data. Thanks for your help. I've really learned something good. "Roger Govier" wrote: Hi Lawrence You will need to add another column to your source table Named Count In D2 enter =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=$B2)*($C$2: $C2=C2))=1,1,0) and copy down Add the Count Column to the source range for your PT, and use Sum of Count as your data field -- Regards Roger Govier |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
A Pivot Table challenge--I can't do it, can you?
Hi Charles--
Thanks for the tip. Sorting the rows as you say clearly enables you to simplify the formula. I'll try it out. By the way, I don't know if you remember me, but I purchased a copy of FastExcel a couple years ago and gave you suggestions for improving the Users Guide. Keep up the good work. Lawrence "Charles Williams" wrote: Hi Lawrence, If you can sort the data by Division then Department then Vendor you can add a faster formula than sumproduct: assuming vendor is in column D and the data starts in column 2 then add a Counter column in E2 =IF(D1=d2,0,1) and copy down for the 15000 rows. Then just add the counter column to the pivot: SUM will give you the answer. regards Charles Williams Decision Models |
Reply |
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 |