Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reporting using PivotTable
Dear Excel Experts,
I need your help assessing whether the following project can be done using a pivottable. There are many fields in my worksheet but the relevant ones to the problem are : Country Product Group Sales Order Number Delivery Number Available (this is a boolean field, Y or N) I need to do a report on the availability of each product group. The breakdown must be Country, Product Type, Sales Order & Delivery Number (figure below). Country: <User can toggle between ALL or specific country Prod A Prod B No. of orders ## ## No. of DN ## ## Available orders Orders ## ## DN ## ## For the first section, I need a unique count of Sales Order and Delivery Number since there are many duplicates. How do I do a unique count using a PivotTable? For the second section I need to look at multiple fields to decide if it should be counted. I have to count all unique delivery numbers that have its corresponding Available field = Y. Sales Order Numbers are a bit more confusing because multiple delivery numbers can be part of one sales order. If even ONE of these delivery numbers is unavailable (i.e. Available=N) then I should not count that sales order. I have no idea how to approach this... any ideas? To break down the results, I think the PivotTable Layout feature will come in handy. I'll just assign Country as the paging field and Product Group as the column. I'm not too concerned with the pivottable looking exactly like the table format above. Any suggestions on how I can accomplish this? I'd hate to dive into visual basic to do something that seems so much suited for a pivot table. Thank you! Shahram |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reporting using PivotTable
Hi Shahram
I think I would add two extra fields to my raw data to calculate the unique items, then include these in my PT. With SON in A, DN in B and Availability in C To calculate unique SON and DN =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))1,0,1) and copy down To calculate unique SON and Availability =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2="y"))1,0,1 )* (SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2="n"))<1) Change ranges to suit location of your relevant fields -- Regards Roger Govier wrote in message ups.com... Dear Excel Experts, I need your help assessing whether the following project can be done using a pivottable. There are many fields in my worksheet but the relevant ones to the problem are : Country Product Group Sales Order Number Delivery Number Available (this is a boolean field, Y or N) I need to do a report on the availability of each product group. The breakdown must be Country, Product Type, Sales Order & Delivery Number (figure below). Country: <User can toggle between ALL or specific country Prod A Prod B No. of orders ## ## No. of DN ## ## Available orders Orders ## ## DN ## ## For the first section, I need a unique count of Sales Order and Delivery Number since there are many duplicates. How do I do a unique count using a PivotTable? For the second section I need to look at multiple fields to decide if it should be counted. I have to count all unique delivery numbers that have its corresponding Available field = Y. Sales Order Numbers are a bit more confusing because multiple delivery numbers can be part of one sales order. If even ONE of these delivery numbers is unavailable (i.e. Available=N) then I should not count that sales order. I have no idea how to approach this... any ideas? To break down the results, I think the PivotTable Layout feature will come in handy. I'll just assign Country as the paging field and Product Group as the column. I'm not too concerned with the pivottable looking exactly like the table format above. Any suggestions on how I can accomplish this? I'd hate to dive into visual basic to do something that seems so much suited for a pivot table. Thank you! Shahram |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable and Chart | Excel Discussion (Misc queries) | |||
Excel 2007 PivotTable and Chart | Excel Discussion (Misc queries) | |||
QUESTION: Copy pivottable from excel file to another. | Excel Discussion (Misc queries) | |||
Dynamic parameterized PivotTable update? | Excel Discussion (Misc queries) | |||
Sorting in PivotTable | Excel Discussion (Misc queries) |