Formula to bundle

Sorry I probably didn’t explain clearly. In the top row (type 1) example I require 1231m of pipe, however this type of pipe comes in coil quantities of 600m, 200m, 120m or 80m therefore the best bundle combination would be for 2x 600m coils and 1x 80m coil.

Hope that makes more sense :-)

The format of the output isn’t important, more so the ability to automate the bundling. If this can be done using additional cells that is fine.

Thanks

Andrew

That works perfectly, thanks very much :-)

Hi Olly,

Apologies I know I said it works perfectly but after more use I've noticed the code isn't doing exactly what I need. EG If I have 250 in cell B2 - rather than skipping back the 600 bundle and selecting a 200 then skipping the 120 and also selecting an 80 it just selects 1x 600, which would be too much.
Any other ideas?

Thanks

Andi

Also how do I revert this back to "unsolved" :-D

Ok marked as unsolved hopefully someone can help

Hi Greg,
Thanks for having a look :-)

"Let's say your last 2 Bundle Qty's for Type 1 were 130 and 25 and your req. qty was 140. Should the answer be 1x130 and 1x25; or 6x25?" I'm leaning towards this being 6 x 25 therefore the closest to the actual required

"Also, for your type 3, your Req Qty is 613 and your expected result is 3x240 (720 total)." My mistake here, apologies - it should have been as you suggest 2 x 240 & 2 x 80

To give more clarity I'm not looking for the lowest # of coils but more so the closest combination to the required quantity.

Thanks

Andi

Anyone have any idea’s?

This one is complicated I think because of all the combinations that will need to be checked. It will take some time for me to look at it though. Hopefully either someone else can come up with a nice solution or you can wait a little bit - not sure how long I can spend on it or how long it will take me, but I'll try putting some time into it soon.

Hi Greg,

Happy to wait :-)

Thanks for your help