Reporting

[shahram.anver]

Dear Excel Experts,

I need your help assessing whether the following project can be done
using Excel. I'm an Excel newbie and would greatly appreciate the
feedback. I just need a pointer in the right direction...

anyway, this is what I have to do.

I receive a large table with about 2000 records periodically and I need
to generate a report based on this data.

There are many fields in the table 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 how many products were available and not
available.
The breakdown must be Country, Product Type, Sales Order & Delivery
Number (figure below).

Country: ALL or Select a Country
Prod A Prod B
Prod C
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. I did this by sorting
the table by product type and running a combination of sum and
frequency formula's to calculate the unique count. It seems to work.

It gets more complicated for the second section.
For instance, if there are 2 delivery orders attached to the same sales
order. Assume one of the delivery orders is not available and the other
is available. Order Availability should be 0 since one delivery is
unavailable. In terms of delivery , it should be 1 (50%) since there
one delivery was available. The final result must be a sum of results
for all delivery numbers and sales orders.

Finally, all these results must be broken down by product type and
country. Product type is like a column and Countries would be like a
Page field in a pivot table layout.

Any suggestions on how I can accomplish this? I doubt I can use a pivot
table because functionality like unique count is missing.

Thank you!
Shahram

 

[Ed Ferrero]

Use a pivot table. There is a tutorial here
http://www.edferrero.com/tutorials.aspx

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com

 

[shahram.anver]

How can I use a pivot table? I need a count of all unique sales order
numbers and delivery numbers.
I read your tutorial and one of the features that seemed relevant was
calculated fields. Is there a way for me to use those to count only the
uniqe sales order numbers?

and what about the second section that requires me to look at two
fields to decide if it should be counted. I.e. 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. How can I do this? :S

thank you!
Shahram

 

[Roger Govier]

see reply to your separate posting in worksheet.functions