Hello,
I have a requirement to be able to allocate certain quantities of product to certain batches of product in the most efficient allocations possible.... for example... I have four quantities of 25 pieces, allocated to a batch quantity of 100.. simple... However....
In my case we have hundreds, sometimes thousands of individual quantities, allocated against possibly three or four batches... i need to identify a formula or a macro that can evaluate that quantity data and organise it into the most efficient allocations under each batch... and feed that data back to me informing me which qty's are allocated to which batch...
Secondly we have a contingency value applied to the total quantities, which is reflected in the individual batch quantities, for example if this contingency is 10% of the sum of all the individual quantities then value of the 10% (in quantity) is spread across the number of batches, if we have four batches they each take 25% of the total contingency value. I therefore need excel allocate the individual quantities to the batches in the most efficient arrangement, but leave the contingency as close to the 10% as possible in each batch in this scenario.
This seems immensely complicated to me and way beyond my capabilities in excel, i am not even sure it is possible..? I very much hope someone can offer some guidance or a potential solution.
I have an excel sheet which displays what i want to achieve, but i need it to be automated as making these calculations with thousands of individual quantities is impossible. I can send through as an example if necessary.
I would appreciate anyone's consideration on this problem and i hope someone can provide some assistance.
Best regards,
Andy
I have a requirement to be able to allocate certain quantities of product to certain batches of product in the most efficient allocations possible.... for example... I have four quantities of 25 pieces, allocated to a batch quantity of 100.. simple... However....
In my case we have hundreds, sometimes thousands of individual quantities, allocated against possibly three or four batches... i need to identify a formula or a macro that can evaluate that quantity data and organise it into the most efficient allocations under each batch... and feed that data back to me informing me which qty's are allocated to which batch...
Secondly we have a contingency value applied to the total quantities, which is reflected in the individual batch quantities, for example if this contingency is 10% of the sum of all the individual quantities then value of the 10% (in quantity) is spread across the number of batches, if we have four batches they each take 25% of the total contingency value. I therefore need excel allocate the individual quantities to the batches in the most efficient arrangement, but leave the contingency as close to the 10% as possible in each batch in this scenario.
This seems immensely complicated to me and way beyond my capabilities in excel, i am not even sure it is possible..? I very much hope someone can offer some guidance or a potential solution.
I have an excel sheet which displays what i want to achieve, but i need it to be automated as making these calculations with thousands of individual quantities is impossible. I can send through as an example if necessary.
I would appreciate anyone's consideration on this problem and i hope someone can provide some assistance.
Best regards,
Andy
