Optimization of module selection based on spares.

[vilasdesai]

Dear Team,
Its been long I have not been here. I hope you all had a great Christmas and Best wishes for the new year.
My current project is getting complex beyond my limited capability. And more complex is the explanation itself. Trying my best to make it simpler.
Please see attached word file for description and excel file for Use Cases.
Best regards
Vilas Desai

[HansV]

Best wishes to you too.

In my opinion, your request it beyond the scope of a forum such as this one; you're asking for a complete solution to a problem.

[vilasdesai]

Oh, that was not intended, Hans, yes, this is a real problem I faced in my controls project.
Can you please guide me how would I be able to minimise your efforts? Actually, I tried to
do some basic formulas in the use case which I am currently doing all manually. I gave up
when I realised this can be only done with Macros. Can we do only Option B or C? Then I will
try other options myself.

Best wishes
Vilas Desai

[HansV]

Here is a formula solution for usecase B.

Optimization USecases.xlsx

[vilasdesai]

Thanks, Hans. Am I looking at something wrong. The doc is word. Best regards, Vilas

[HansV]

Sorry, I attached the wrong file. I have replaced it in my previous reply.

[vilasdesai]

Thanks, Hans. You made it so easy, I am still trying to understand how you solved it.

[HansV]

Actually. my solution isn't optimal. I see that it sometimes assigns 2x 4-module instead of 1 8-module.

[vilasdesai]

Thanks Hans. I did not notice the inconsistency. Can you please recollect the instance where you find this.
I did not notice though that I don't see negative excess spares. It shows either 0 or positive numbers.
I was expecting a macro because i could modify it for other use cases / options and also try building it
to my limited capability

[vilasdesai]

Correction
Thanks Hans. I did not notice the inconsistency. Can you please recollect the instance where you find this.
I did notice though that I don't see negative excess spares. It shows either 0 or positive numbers.
I was expecting a macro because i could modify it for other use cases / options and also try building it
to my limited capability

[HansV]

It's too complicated, sorry.

[vilasdesai]

Ok, thanks for your efforts. Best regards,Vilas

[ChrisGreaves]

Vilas
As Hans has pointed out, (and the absence of any other responses confirms) such a response is beyond the scope of a forum such as this one; you're asking for a complete solution to a problem. In my estimation a solution is possible, but such a contribution would produce a volume of conversation exceeding 20,000 words and would probably be unintelligible to anyone not engaged in the conversation.
A response to your post is the equivalent of a response to a request from Senior Management to the Information Technology Department.

If you are willing, you can be the Senior Management and I will adopt the role of the Information Technology Department. We can process your request by email until we reach the point where you need help in specific aspects of Excel or VBA, at which point we can return to this thread with manageable requests.
At that time we can offer to Eileen's Lounge a better-defined problem and a summary of our findings to date.

If you agree, please send me an email, or a Private Message with your email address. We can then continue the conversation between us through email without turning Eileen's Lounge into a course in Project Management.
I offer this as a member of, and in the spirit of, Eileen's Lounge. I volunteer my services with no charges.
I am retired, with spare time, and have developed programming solutions for many years.
Best Wishes, Chris