lookup

[Craig]

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

 

[JMMach]

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]

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

 

[STEVE BELL]

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
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

 

[Craig]

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]

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