Looking for logic to develop macro

[vishpuj]

I am looking for an solution in which user inputs below details n I will get following output as Table-2.

I have 4 parts name - A,B,C,D with priority as 1,2,4,3.
Rule is that if we load part name - A on a machine then it can maximum produce 50 qty, after that compulsory batch needs to be changed, because Tools/ Die for Part Name - A needs to be reconditioned.
So we will switch over to next priority i.e. part name - B, again it will produce 50 qty after that it will be take for reconditioned.
Till that time Part Name - A tool will be ready, so will manufacture part name - A.

Refer below table 2 for more details.


INPUT by user ( TABLE - 1 )
Part Name Total Order Qty Priority Maxi. Batch Qty.

A 100 1 50
B 150 2 50
C 250 4 50
D 175 3 50


OUTPUT ... Table - 2
Priority Part Name Produced Total Manufactured Total Balance
1 A 50 50 50
2 B 50 50 100
1 A 50 100 0
2 B 50 100 50
3 D 50 50 125
2 B 50 150 0
3 D 50 100 75
4 C 50 50 200
3 D 50 150 25
4 C 50 100 150
3 D 25 175 0
4 C 50 150 100
& above cycle continues.

[mike7952]

Best to upload an example of want your wanting. Dont need the real data but need the exact layout of the real data.

[vishpuj]

Please find attached file

[vishpuj]

Can anybody help me ????

[vishpuj]

Please help

[mike7952]

I'm not getting what your request is. Can you add some notes to the output sheet that shows the logic

[vishpuj]

The selection of the part name should be based on priority.
For e.g. For part name "A" - priority is 1, so it will be produced first.
Then for part name - "B" - priority is 2, so it will be produced after "A" is finished.

Secondly,
During each line item, maximum it can produce is 50 Qty.
e.g. Suppose we start from part "A" is will produce 50 Nos. so Total remaining will be "50 - 100 = 50 Qty."
After part "A" finished it will automatically select part "B", because we have set priority "B" as 2.

[vishpuj]

If you required any graphical / schematic presentation then i can prepare n give you.

[mike7952]

That would help.

[vishpuj]

Please find simple layout.

[mike7952]

I will look at this as soon as I can, when I get home. Getting ready for a 12hr work day. Or if anyone else wants to chime in feel free.

[vishpuj]

Can anybody help to solve this.....

[Loganeb]

Hello vishpuj,

I still don't exactly understand your request. If you can walk through the logic step by step, I'm sure that I or someone else can program the macro.