Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup
I am new to macros and can get by with basic programming, but struggle with
loops etc. I currently receive summary date of jobs (circa 100 per month) and their estimated Work in Progress values on a monthly basis. The estimated % completion on each job is an estimate based on the relevant individuals "Gut Feel" based on how the job is progressing, rather than a more "mathematical" approach. I have access to the detail which makes up the summary information and would like to apply a macro to test the "reasonability" of the summary figures I am receiving. The summary info is in the following format: Job No Job value Sales Value of work to date % Complete Division WIP 10256 50,000 15,000 40% 1 20,000 These are not broken down by Department 10257 100,000 15,000 10% 2 10,000 The Value of Work to date is Sales price x Time spent and would not take into account efficiencies / inefficinecies in production, so job 10256 is 40% complete despite the value of work to date only amounting to 30% of the job value. The detailed info would be as follows (circa 20,000 lines per month): Job No Description Division Department Quantity Unit Sale Price Total Sale Price Unit Standard Cost Total Std Cost 10256 Machine 1 1 1 5 500 2500 260 1300 10256 Machine 2 1 2 3 200 600 160 480 10256 Machine 1 1 1 1 500 500 260 260 10256 Machine 3 1 3 4 300 1200 200 800 10256 etc What I think I need to do is the following: 1. Name the range in which the summary info is held. 2. Get the first job no. in the summary info Filter the detailed information by this job number and then subtotal the Total Sales Price column Lookup the Value of work to date on the summary sheet and apply the value to a cell. Divide the Detailed Total Sales Price by the lookup value. Apply the % achieved by this formula to all the cells in the in the Filtered Detail page (This would then give me a departmental WIP as the % would be applied to each detailed line) Loop to the next job in the summary information I am currently doing this manually and it is extremely slow. Any help would be appreciated. Please let me know if you need any of this needs further clarification. I am interested in the macro to achieve the above. The logic of what is being done is difficult to explain as there are further levels of detail which would need analysis, but sorting the macro out would clear a lot of them up, as well as providing me with the model for further analysis. Thanks in advance. Craig |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup
It would appear your first task is to rearrange how your data is recorded;
you need a list or a table of your Data. That is, list the categories of information as headings in ONE ROW - Field Names - and keep all related information about a particular job in ONE ROW - a Record. JobNo should be the first Field Name if you plan on using VLOOKUPs and the like. Only then will your stated task become reasonably doable. I hope that helps. TTFN JMMach "Craig" wrote in message ... I am new to macros and can get by with basic programming, but struggle with loops etc. I currently receive summary date of jobs (circa 100 per month) and their estimated Work in Progress values on a monthly basis. The estimated % completion on each job is an estimate based on the relevant individuals "Gut Feel" based on how the job is progressing, rather than a more "mathematical" approach. I have access to the detail which makes up the summary information and would like to apply a macro to test the "reasonability" of the summary figures I am receiving. The summary info is in the following format: Job No Job value Sales Value of work to date % Complete Division WIP 10256 50,000 15,000 40% 1 20,000 These are not broken down by Department 10257 100,000 15,000 10% 2 10,000 The Value of Work to date is Sales price x Time spent and would not take into account efficiencies / inefficinecies in production, so job 10256 is 40% complete despite the value of work to date only amounting to 30% of the job value. The detailed info would be as follows (circa 20,000 lines per month): Job No Description Division Department Quantity Unit Sale Price Total Sale Price Unit Standard Cost Total Std Cost 10256 Machine 1 1 1 5 500 2500 260 1300 10256 Machine 2 1 2 3 200 600 160 480 10256 Machine 1 1 1 1 500 500 260 260 10256 Machine 3 1 3 4 300 1200 200 800 10256 etc What I think I need to do is the following: 1. Name the range in which the summary info is held. 2. Get the first job no. in the summary info Filter the detailed information by this job number and then subtotal the Total Sales Price column Lookup the Value of work to date on the summary sheet and apply the value to a cell. Divide the Detailed Total Sales Price by the lookup value. Apply the % achieved by this formula to all the cells in the in the Filtered Detail page (This would then give me a departmental WIP as the % would be applied to each detailed line) Loop to the next job in the summary information I am currently doing this manually and it is extremely slow. Any help would be appreciated. Please let me know if you need any of this needs further clarification. I am interested in the macro to achieve the above. The logic of what is being done is difficult to explain as there are further levels of detail which would need analysis, but sorting the macro out would clear a lot of them up, as well as providing me with the model for further analysis. Thanks in advance. Craig |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup
Sorry, the formatting seems to change when I send it. Will try in RTF and hopefully it stays the same.
Summary date : Job No Job value Sales Value of work to date % Complete Division WIP 10256 50,000 15,000 40% 1 20,000 These are not broken down by Department 10257 100,000 15,000 10% 2 10,000 The detailed info would be as follows (circa 20,000 lines per month): Job No Description Division Department Quantity Unit Sale Price Total Sale Price Unit Standard Cost Total Std Cost 10256 Machine 1 1 1 5 500 2500 260 1300 10256 Machine 2 1 2 3 200 600 160 480 10256 Machine 1 1 1 1 500 500 260 260 10256 Machine 3 1 3 4 300 1200 200 800 10256 etc What I think I need to do is the following: 1. Name the range in which the summary info is held. 2. Get the first job no. in the summary info a.. Filter the detailed information by this job number and then subtotal the Total Sales Price column b.. Lookup the Value of work to date on the summary sheet and apply the value to a cell. c.. Divide the Detailed Total Sales Price by the lookup value. d.. Apply the % achieved by this formula to all the cells in the Filtered Detail page (Giving me a departmental WIP as the % would be applied to each detailed line) 3. Loop to the next job in the summary information Craig "JMMach" wrote in message ... It would appear your first task is to rearrange how your data is recorded; you need a list or a table of your Data. That is, list the categories of information as headings in ONE ROW - Field Names - and keep all related information about a particular job in ONE ROW - a Record. JobNo should be the first Field Name if you plan on using VLOOKUPs and the like. Only then will your stated task become reasonably doable. I hope that helps. TTFN JMMach "Craig" wrote in message ... I am new to macros and can get by with basic programming, but struggle with loops etc. I currently receive summary date of jobs (circa 100 per month) and their estimated Work in Progress values on a monthly basis. The estimated % completion on each job is an estimate based on the relevant individuals "Gut Feel" based on how the job is progressing, rather than a more "mathematical" approach. I have access to the detail which makes up the summary information and would like to apply a macro to test the "reasonability" of the summary figures I am receiving. The summary info is in the following format: Job No Job value Sales Value of work to date % Complete Division WIP 10256 50,000 15,000 40% 1 20,000 These are not broken down by Department 10257 100,000 15,000 10% 2 10,000 The Value of Work to date is Sales price x Time spent and would not take into account efficiencies / inefficinecies in production, so job 10256 is 40% complete despite the value of work to date only amounting to 30% of the job value. The detailed info would be as follows (circa 20,000 lines per month): Job No Description Division Department Quantity Unit Sale Price Total Sale Price Unit Standard Cost Total Std Cost 10256 Machine 1 1 1 5 500 2500 260 1300 10256 Machine 2 1 2 3 200 600 160 480 10256 Machine 1 1 1 1 500 500 260 260 10256 Machine 3 1 3 4 300 1200 200 800 10256 etc What I think I need to do is the following: 1. Name the range in which the summary info is held. 2. Get the first job no. in the summary info Filter the detailed information by this job number and then subtotal the Total Sales Price column Lookup the Value of work to date on the summary sheet and apply the value to a cell. Divide the Detailed Total Sales Price by the lookup value. Apply the % achieved by this formula to all the cells in the in the Filtered Detail page (This would then give me a departmental WIP as the % would be applied to each detailed line) Loop to the next job in the summary information I am currently doing this manually and it is extremely slow. Any help would be appreciated. Please let me know if you need any of this needs further clarification. I am interested in the macro to achieve the above. The logic of what is being done is difficult to explain as there are further levels of detail which would need analysis, but sorting the macro out would clear a lot of them up, as well as providing me with the model for further analysis. Thanks in advance. Craig |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup
You may want to check out Pivot Tables.
This can be done in Excel without code. Or you can creat them in code. You can add calculation columns to the table(s) -- steveB Remove "AYN" from email to respond "Craig" wrote in message ... Sorry, the formatting seems to change when I send it. Will try in RTF and hopefully it stays the same. Summary date : Job No Job value Sales Value of work to date % Complete Division WIP 10256 50,000 15,000 40% 1 20,000 These are not broken down by Department 10257 100,000 15,000 10% 2 10,000 The detailed info would be as follows (circa 20,000 lines per month): Job No Description Division Department Quantity Unit Sale Price Total Sale Price Unit Standard Cost Total Std Cost 10256 Machine 1 1 1 5 500 2500 260 1300 10256 Machine 2 1 2 3 200 600 160 480 10256 Machine 1 1 1 1 500 500 260 260 10256 Machine 3 1 3 4 300 1200 200 800 10256 etc What I think I need to do is the following: 1. Name the range in which the summary info is held. 2. Get the first job no. in the summary info Filter the detailed information by this job number and then subtotal the Total Sales Price column Lookup the Value of work to date on the summary sheet and apply the value to a cell. Divide the Detailed Total Sales Price by the lookup value. Apply the % achieved by this formula to all the cells in the Filtered Detail page (Giving me a departmental WIP as the % would be applied to each detailed line) 3. Loop to the next job in the summary information Craig "JMMach" wrote in message ... It would appear your first task is to rearrange how your data is recorded; you need a list or a table of your Data. That is, list the categories of information as headings in ONE ROW - Field Names - and keep all related information about a particular job in ONE ROW - a Record. JobNo should be the first Field Name if you plan on using VLOOKUPs and the like. Only then will your stated task become reasonably doable. I hope that helps. TTFN JMMach "Craig" wrote in message ... I am new to macros and can get by with basic programming, but struggle with loops etc. I currently receive summary date of jobs (circa 100 per month) and their estimated Work in Progress values on a monthly basis. The estimated % completion on each job is an estimate based on the relevant individuals "Gut Feel" based on how the job is progressing, rather than a more "mathematical" approach. I have access to the detail which makes up the summary information and would like to apply a macro to test the "reasonability" of the summary figures I am receiving. The summary info is in the following format: Job No Job value Sales Value of work to date % Complete Division WIP 10256 50,000 15,000 40% 1 20,000 These are not broken down by Department 10257 100,000 15,000 10% 2 10,000 The Value of Work to date is Sales price x Time spent and would not take into account efficiencies / inefficinecies in production, so job 10256 is 40% complete despite the value of work to date only amounting to 30% of the job value. The detailed info would be as follows (circa 20,000 lines per month): Job No Description Division Department Quantity Unit Sale Price Total Sale Price Unit Standard Cost Total Std Cost 10256 Machine 1 1 1 5 500 2500 260 1300 10256 Machine 2 1 2 3 200 600 160 480 10256 Machine 1 1 1 1 500 500 260 260 10256 Machine 3 1 3 4 300 1200 200 800 10256 etc What I think I need to do is the following: 1. Name the range in which the summary info is held. 2. Get the first job no. in the summary info Filter the detailed information by this job number and then subtotal the Total Sales Price column Lookup the Value of work to date on the summary sheet and apply the value to a cell. Divide the Detailed Total Sales Price by the lookup value. Apply the % achieved by this formula to all the cells in the in the Filtered Detail page (This would then give me a departmental WIP as the % would be applied to each detailed line) Loop to the next job in the summary information I am currently doing this manually and it is extremely slow. Any help would be appreciated. Please let me know if you need any of this needs further clarification. I am interested in the macro to achieve the above. The logic of what is being done is difficult to explain as there are further levels of detail which would need analysis, but sorting the macro out would clear a lot of them up, as well as providing me with the model for further analysis. Thanks in advance. Craig |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup
Thanks Steve,
I am trying to learn VBA, and this exercise, or something simlar, is bound to occur again in future so I want to learn how to run the suggested loop. I have a good understanding of Pivot tables, but they only analyse what you put in (other than formulas etc that you can put in the tables). I am looking to automate the population of a large amount of detail from a summary sheet so that I have more data to work with to check the validity of the summary data. I have bought John Walkenbach's Power Programming book, but am struggling to put the code into context, and this exercise will help significantly. Regards Craig "STEVE BELL" wrote in message news:x14re.4178$9a1.692@trnddc01... You may want to check out Pivot Tables. This can be done in Excel without code. Or you can creat them in code. You can add calculation columns to the table(s) -- steveB Remove "AYN" from email to respond "Craig" wrote in message ... Sorry, the formatting seems to change when I send it. Will try in RTF and hopefully it stays the same. Summary date : Job No Job value Sales Value of work to date % Complete Division WIP 10256 50,000 15,000 40% 1 20,000 These are not broken down by Department 10257 100,000 15,000 10% 2 10,000 The detailed info would be as follows (circa 20,000 lines per month): Job No Description Division Department Quantity Unit Sale Price Total Sale Price Unit Standard Cost Total Std Cost 10256 Machine 1 1 1 5 500 2500 260 1300 10256 Machine 2 1 2 3 200 600 160 480 10256 Machine 1 1 1 1 500 500 260 260 10256 Machine 3 1 3 4 300 1200 200 800 10256 etc What I think I need to do is the following: 1. Name the range in which the summary info is held. 2. Get the first job no. in the summary info Filter the detailed information by this job number and then subtotal the Total Sales Price column Lookup the Value of work to date on the summary sheet and apply the value to a cell. Divide the Detailed Total Sales Price by the lookup value. Apply the % achieved by this formula to all the cells in the Filtered Detail page (Giving me a departmental WIP as the % would be applied to each detailed line) 3. Loop to the next job in the summary information Craig "JMMach" wrote in message ... It would appear your first task is to rearrange how your data is recorded; you need a list or a table of your Data. That is, list the categories of information as headings in ONE ROW - Field Names - and keep all related information about a particular job in ONE ROW - a Record. JobNo should be the first Field Name if you plan on using VLOOKUPs and the like. Only then will your stated task become reasonably doable. I hope that helps. TTFN JMMach "Craig" wrote in message ... I am new to macros and can get by with basic programming, but struggle with loops etc. I currently receive summary date of jobs (circa 100 per month) and their estimated Work in Progress values on a monthly basis. The estimated % completion on each job is an estimate based on the relevant individuals "Gut Feel" based on how the job is progressing, rather than a more "mathematical" approach. I have access to the detail which makes up the summary information and would like to apply a macro to test the "reasonability" of the summary figures I am receiving. The summary info is in the following format: Job No Job value Sales Value of work to date % Complete Division WIP 10256 50,000 15,000 40% 1 20,000 These are not broken down by Department 10257 100,000 15,000 10% 2 10,000 The Value of Work to date is Sales price x Time spent and would not take into account efficiencies / inefficinecies in production, so job 10256 is 40% complete despite the value of work to date only amounting to 30% of the job value. The detailed info would be as follows (circa 20,000 lines per month): Job No Description Division Department Quantity Unit Sale Price Total Sale Price Unit Standard Cost Total Std Cost 10256 Machine 1 1 1 5 500 2500 260 1300 10256 Machine 2 1 2 3 200 600 160 480 10256 Machine 1 1 1 1 500 500 260 260 10256 Machine 3 1 3 4 300 1200 200 800 10256 etc What I think I need to do is the following: 1. Name the range in which the summary info is held. 2. Get the first job no. in the summary info Filter the detailed information by this job number and then subtotal the Total Sales Price column Lookup the Value of work to date on the summary sheet and apply the value to a cell. Divide the Detailed Total Sales Price by the lookup value. Apply the % achieved by this formula to all the cells in the in the Filtered Detail page (This would then give me a departmental WIP as the % would be applied to each detailed line) Loop to the next job in the summary information I am currently doing this manually and it is extremely slow. Any help would be appreciated. Please let me know if you need any of this needs further clarification. I am interested in the macro to achieve the above. The logic of what is being done is difficult to explain as there are further levels of detail which would need analysis, but sorting the macro out would clear a lot of them up, as well as providing me with the model for further analysis. Thanks in advance. Craig |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup
You're welcome. Always happy to help.
Just remember that if you can do it manually - you can build code to do it. And more... (often Recording your moves can get you started on code) Suggest you also get a book on Excel. Walkenbach has a starter "Dummies" version that I recommend to everyone - either Beginner or more advanced. Monitor this forum and try out the stuff you see. Make note of all the sites and visit some of them. Download Ron de Bruin's "Google Search" add-in. This thing is awesome in researching your questions. http://www.rondebruin.nl/Google.htm -- steveB Remove "AYN" from email to respond "Craig" wrote in message ... Thanks Steve, I am trying to learn VBA, and this exercise, or something simlar, is bound to occur again in future so I want to learn how to run the suggested loop. I have a good understanding of Pivot tables, but they only analyse what you put in (other than formulas etc that you can put in the tables). I am looking to automate the population of a large amount of detail from a summary sheet so that I have more data to work with to check the validity of the summary data. I have bought John Walkenbach's Power Programming book, but am struggling to put the code into context, and this exercise will help significantly. Regards Craig "STEVE BELL" wrote in message news:x14re.4178$9a1.692@trnddc01... You may want to check out Pivot Tables. This can be done in Excel without code. Or you can creat them in code. You can add calculation columns to the table(s) -- steveB Remove "AYN" from email to respond "Craig" wrote in message ... Sorry, the formatting seems to change when I send it. Will try in RTF and hopefully it stays the same. Summary date : Job No Job value Sales Value of work to date % Complete Division WIP 10256 50,000 15,000 40% 1 20,000 These are not broken down by Department 10257 100,000 15,000 10% 2 10,000 The detailed info would be as follows (circa 20,000 lines per month): Job No Description Division Department Quantity Unit Sale Price Total Sale Price Unit Standard Cost Total Std Cost 10256 Machine 1 1 1 5 500 2500 260 1300 10256 Machine 2 1 2 3 200 600 160 480 10256 Machine 1 1 1 1 500 500 260 260 10256 Machine 3 1 3 4 300 1200 200 800 10256 etc What I think I need to do is the following: 1. Name the range in which the summary info is held. 2. Get the first job no. in the summary info Filter the detailed information by this job number and then subtotal the Total Sales Price column Lookup the Value of work to date on the summary sheet and apply the value to a cell. Divide the Detailed Total Sales Price by the lookup value. Apply the % achieved by this formula to all the cells in the Filtered Detail page (Giving me a departmental WIP as the % would be applied to each detailed line) 3. Loop to the next job in the summary information Craig "JMMach" wrote in message ... It would appear your first task is to rearrange how your data is recorded; you need a list or a table of your Data. That is, list the categories of information as headings in ONE ROW - Field Names - and keep all related information about a particular job in ONE ROW - a Record. JobNo should be the first Field Name if you plan on using VLOOKUPs and the like. Only then will your stated task become reasonably doable. I hope that helps. TTFN JMMach "Craig" wrote in message ... I am new to macros and can get by with basic programming, but struggle with loops etc. I currently receive summary date of jobs (circa 100 per month) and their estimated Work in Progress values on a monthly basis. The estimated % completion on each job is an estimate based on the relevant individuals "Gut Feel" based on how the job is progressing, rather than a more "mathematical" approach. I have access to the detail which makes up the summary information and would like to apply a macro to test the "reasonability" of the summary figures I am receiving. The summary info is in the following format: Job No Job value Sales Value of work to date % Complete Division WIP 10256 50,000 15,000 40% 1 20,000 These are not broken down by Department 10257 100,000 15,000 10% 2 10,000 The Value of Work to date is Sales price x Time spent and would not take into account efficiencies / inefficinecies in production, so job 10256 is 40% complete despite the value of work to date only amounting to 30% of the job value. The detailed info would be as follows (circa 20,000 lines per month): Job No Description Division Department Quantity Unit Sale Price Total Sale Price Unit Standard Cost Total Std Cost 10256 Machine 1 1 1 5 500 2500 260 1300 10256 Machine 2 1 2 3 200 600 160 480 10256 Machine 1 1 1 1 500 500 260 260 10256 Machine 3 1 3 4 300 1200 200 800 10256 etc What I think I need to do is the following: 1. Name the range in which the summary info is held. 2. Get the first job no. in the summary info Filter the detailed information by this job number and then subtotal the Total Sales Price column Lookup the Value of work to date on the summary sheet and apply the value to a cell. Divide the Detailed Total Sales Price by the lookup value. Apply the % achieved by this formula to all the cells in the in the Filtered Detail page (This would then give me a departmental WIP as the % would be applied to each detailed line) Loop to the next job in the summary information I am currently doing this manually and it is extremely slow. Any help would be appreciated. Please let me know if you need any of this needs further clarification. I am interested in the macro to achieve the above. The logic of what is being done is difficult to explain as there are further levels of detail which would need analysis, but sorting the macro out would clear a lot of them up, as well as providing me with the model for further analysis. Thanks in advance. Craig |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |