Solved: Tool allocation forecast with multiple IF statements

[jolivanes]

I have a Job forecast sheet which requires tool allocations for different job types.
There are 5 different job types of which 2 can be neglected (No tools required).
Of the remaining 3 job types there are several possibilities which tools will be needed.

These are the criteria:
If Job type = D and Toolsize = Big, then 2 Big Wrenches + 2 Big Screwdrivers
If Job type = D and Toolsize = Medium, then 2 Medium Wrenches + 2 Big Screwdrivers
If Job type = D and Toolsize = Small, then 2 Small Wrenches + 2 Small Screwdrivers
If Job type = D and Toolsize = Big and Toolsize = Medium, then 2 Big Wrenches + 2 Medium Wrenches + 2 Big Screwdrivers
If Job type = D and Toolsize = Medium and Toolsize = Small, then 2 Medium Wrenches + 2 Small Wrenches + 2 Medium Screwdrivers + 2 Small Screwdrivers
If Job type = L and Toolsize = Big, then 2 Big Wrenches + 1 Big Screwdriver
If Job type = L and Toolsize = Medium, then 2 Medium Wrenches + 1 Big Screwdriver
If Job type = L and Toolsize = Small, then 2 Small Wrenches + 1 Small Screwdriver
If Job type = L and Toolsize = Big and Toolsize = Medium, then 2 Big Wrenches + 2 Medium Wrenches + 1 Big Screwdriver
If Job type = L and Toolsize = Medium and Toolsize = Small, then 2 Medium Wrenches + 2 Small Wrenches + 1 Medium Screwdriver + 1 Small Screwdriver
If Job type = H and Toolsize = Big, then 2 Big Wrenches + 3 Big Screwdrivers
If Job type = H and Toolsize = Medium, then 2 Medium Wrenches + 3 Big Screwdrivers
If Job type = H and Toolsize = Small, then 2 Small Wrenches + 3 Small Screwdrivers
If Job type = H and Toolsize = Big and Toolsize = Medium, then 2 Big Wrenches + 2 Medium Wrenches + 3 Big Screwdrivers
If Job type = H and Toolsize = Medium and Toolsize = Small, then 2 Medium Wrenches + 2 Small Wrenches + 3 Medium Screwdrivers + 3 Small Screwdrivers

These are the criteria for one job but there could be as many as 50 jobs going in one day. Is it possible at all to get a total of different tools needed for each day with a formula or is VBA needed?
In the attachment I have calculated tools required manually but I would not know where to start with a formula or with VBA.
Any help is greatly appreciated.

Thanks and regards.

John

[gwkenny]

Take a look at what I did.

Columns C through E becomes redundent.

I changed the various combinations of small/medium/large into numbers 1, 2, 3, 4, and 5

Thus, when you define a job in cells F3..AJ53, you now give it a letter and number designation.

I setup a table of tools per job type in "Tools per Job" worksheet.

I used sumproduct from Tools per Job versus job type to get you the number of tools needed per day.

You need to double check all my entries. I started doing everything exactly per your sheet, but it was taking up too much time. So you'll need to check all the definitions (Tools per Job table) and all the entries in your raw data (F3..AJ53).

Can this be done via code? Sure, but not necessary

:)

This is just one solution, many others out there :)

[jolivanes]

Good morning gwkenny.
Thank you very much for your help. I've saved your workbook and now I'll have to work at it to see how it works.
Looks great though.

Regards

John

[jolivanes]

gwkenny
All is great. I goofed because there are no Medium Screwdrivers but that is fixed.
You mentioned that this also would be possible with VBA. Would one use Case for that and do you also need to change the selections in cells AV2:AV6 or could you leave the 5 selections as is in the original?

Again, thank you very much for your help.

John

[gwkenny]

John:

I do not know your business, the only information I get is from the spreadsheet you gave and the layout was limiting. Which is why I expanded the selections from the original five in AV2:AV6, irrespective if you tally information with formulas or functions.

If I used your original layout, and a company has a small job on the 3rd and a big job on the 15th, you would need two rows to represent this information. In the layout I provided, you only need one row.

Could I have provided a solution using your original layout? Yes, but the formulas would be long. The nice thing about the layout I provided is that one SUMPRODUCT formula is really doing all the work and it is relatively simplistic than the alternatives.

Regarding coding? It could be done with your original format or the one slightly modified one I provided.

You could press a button, and the code would go loop through each cell, look up the job type and size, and then add the resulting tools necessary into an array variable that would keep track of the totals. When the code loops down to the last Company of that specific date, it then writes the totals for that date in the area specified, then it loops to the next date until all dates are processed.

You COULD use Select Case in your code and in this case, would be probably one of the commands of choice, but it all depends on how one prefers to code. To me, as long as it is logically sound, easy to follow, and well documented, it's all good.

And that's only one way to code it. Another solution would be a custom function can be defined where you give it the arguments and it will loop through all the data necessary and come up with your totals as well.

****************

When there are multiple solutions to a problem, in general it is much faster to give a function/spreadsheet based solution rather than coding.

Giving you a coded solution to the problem you posed would take me multiples longer, but the end product would look "cleaner" because you would not need an intermediate area for calculations.

[matthewspatrick]

I know you asked for an Excel solution, but in all bluntness, this is the sort of thing that a spreadsheet will struggle to do, yet what a database is born for.

Have you looked at Access for doing this work?

[jolivanes]

gwkenny / matthewspatrick
Thank you both for your replies.
The spreadsheet is from a friend who started a small business recently and is spending all his time working to make it go. He asked me if I could look into it for him. I am also not very computer savvy (age related?) but I told him I would try with help from people like you. So far, so good. The way I see it, the spreadsheet you made gwkenney works perfect and with a few minor changes, wrenches and screwdrivers to be named to the actuals he wants etc, he should have a tremendous help in it. Thanks again.
As for Access, I don't have it on my computer and I don't know if he has it on his. If he does have it I am sure he'll have a hard time with this also but I'll suggest it anyway.
Thank you gentlemen both for the help and if he needs anymore help, I hope you don't mind me coming back to this thread.

Regards

John