Macro project incl dcount, vlookup, etc?? help required

[Rochelle]

Hi!

I need help with using Excel to do a trend analysis of daily faults and
report the totals on a daily, weekly, monthly and finally yearly basis. First
some background:

I receive a table daily consisting of 25 columns, a variable amount of rows
(as it it a daily fault report) of which the first row is a header row. One
of the columns, the eigth one, contains a codes reflecting the type of fault
that occurred. There is a finite list of such codes.

What needs to be done:
1. The faultcodes in column 8 of the daily report needs to be totalled;
using DCOUNT possibly?
2. These totals need to be written to two separate tables - one for the
daily report and one for keeping track of the weeks totals. possibly a lookup
function???
3. Since this is a daily report, each day's totals must be added to both
tables, however the first table's totals may be overwritten.
4. The totals that are added to the second table must accumulate so that
there is a sum total for each different type of fault code for that week.
5. This needs to be done for the months as well, so I assume that there
would have to be a third table so that the totals for a month can also be
obtained and finally a fourth for the year.
6. Each table will be used to populate it's own graph so that the daily,
weekly, monthly and year's results can be displayed for reporting purposes.
7. Since this is a daily task - and very time consuming, it has to be
automated so that one only has to open the "report file", browse for and
select the daily report (that I receive) and then press a button to run the
macro. The result should be that the graphs get updated and then there should
be an option to print the result.

From what I've read on this site I have seen that several tools/functions
could possibly be used to achieve this: DCount to do the totalling, VLookups
for extraction, and a macro program to run through all of these tasks for the
user. However, I'm not really sure where to start right now.

Any comments, suggestions and references to helpful information would be
greatly

appreciated.

 

[Duke Carey]

Rochelle -

Do you have MS Access? This sort of analysis/report is much better handled
by a database.

When you recieve your daily 'table' you should stuff it into a database -
Access or an Excel-based database - and, if it isn't already there, append
the day's date in a new column.

With the data structured like this it is a breeze to create Pivot tables
that can summarize by fault type and by date range - weeks, months, quarters,
years.

Moreover, by storing ALL the data you have some hope for data integrity. If
you simply update totals on a daily basis, there's no way of fixing mistakes
(like running the process twice on Monday, or forgetting to do it on Friday).

 

[Rochelle]

Duke,

Thanks for replying! I'm sure that I can get Access installed, but it may
take a few days... May I count on further help once I'm ready to set it all
up? I will do another "reply" to this post in order to let you know when I'm
back.

Thanks in advance,

 

[Dave Peterson]

If you do the work in Access, I bet you'll get better responses in a
newsgroup/community dedicated to Access--not Excel.

 

[Duke Carey]

Rochelle -

Post back, but it's probably better to start a new thread at that point. In
the meantime, here's a couple of links with great information for your goal:

Nick Hidge has an excellent article about pulling data from Access into Excel
http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

Debra Dalgliesh is an expert at Pivot tables and has intro information here
http://peltiertech.com/Excel/Pivots/pivotstart.htm

and a good website of her own here:
http://www.contextures.com

 

[Rochelle]

Duke and Dave,

Thanks for the links Duke and yes, I will start a new thread in the Access
user group.

Thanks and all of the best!