Re: Reporting
- From: "Roger Govier" <roger@xxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 4 Oct 2006 08:48:45 +0100
see reply to your separate posting in worksheet.functions
--
Regards
Roger Govier
"Ed Ferrero" <ed@xxxxxxxxxxxxxxxxxxx> wrote in message
news:O0dd6Yu5GHA.3808@xxxxxxxxxxxxxxxxxxxxxxx
Use a pivot table. There is a tutorial here
http://www.edferrero.com/tutorials.aspx
Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com
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
.
- References:
- Reporting
- From: shahram . anver
- Re: Reporting
- From: Ed Ferrero
- Reporting
- Prev by Date: Re: Reporting
- Next by Date: Delete Confirmation Permanently Suppressed
- Previous by thread: Re: Reporting
- Next by thread: autofill non-number character
- Index(es):
Relevant Pages
|
