Reporting using PivotTable

[shahram.anver]

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

 

[Roger Govier]

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