Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro project incl dcount, vlookup, etc?? help required
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. -- Kind regards, Rochelle |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro project incl dcount, vlookup, etc?? help required
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" wrote: 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. -- Kind regards, Rochelle |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro project incl dcount, vlookup, etc?? help required
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, -- Kind regards, Rochelle "Duke Carey" wrote: 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" wrote: 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. -- Kind regards, Rochelle |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro project incl dcount, vlookup, etc?? help required
If you do the work in Access, I bet you'll get better responses in a
newsgroup/community dedicated to Access--not Excel. Rochelle wrote: 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, -- Kind regards, Rochelle "Duke Carey" wrote: 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" wrote: 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. -- Kind regards, Rochelle -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro project incl dcount, vlookup, etc?? help required
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/datam...taexamples.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 he http://www.contextures.com "Rochelle" wrote: 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, -- Kind regards, Rochelle "Duke Carey" wrote: 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" wrote: 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. -- Kind regards, Rochelle |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro project incl dcount, vlookup, etc?? help required
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! -- Kind regards, Rochelle "Dave Peterson" wrote: If you do the work in Access, I bet you'll get better responses in a newsgroup/community dedicated to Access--not Excel. Rochelle wrote: 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, -- Kind regards, Rochelle "Duke Carey" wrote: 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" wrote: 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. -- Kind regards, Rochelle -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help required about vlookup formula | Excel Worksheet Functions | |||
help required about vlookup formula | Excel Worksheet Functions | |||
VLOOKUP - more than one return required | Excel Discussion (Misc queries) | |||
vlookup vs if, help required | Excel Worksheet Functions | |||
Vlookup help required | Excel Discussion (Misc queries) |