Combine data into limited quantities efficiently

[andi1987uk]

Hi All,
Going to do my best to explain what I’m looking for here .
So I’ve created a table that calculates a quantity of an item (eg the amount of pipe required for a project by room) this results in a range of pipe lengths. What I’m hoping for is a formula to group the data (as efficiently as possible) into the rolls the pipe is purchased in. So an example would be that I have 5 rows of data of metres of pipe lets say 50, 50, 100, 150 & 150 totalling 500 now let’s say the pipe is purchased in rolls of 100, 200 & 300 metres. To me the most efficient combination would be 1x 200 and 1x 300 rolls. Is there a formula that can help with this?

Thanks in advance

Andrew

 

[RasGhul]

Can you post some sample data?

 

[andi1987uk]

Hi, first of all so sorry it's taken so long to post - this project had to be put on hold due to some other work.

Here is the sample data - I hope it is clear

 

[andi1987uk]

So to the left is the data that needs to be organised and to the right are the quantities each type comes in.

 

[andi1987uk]

Any help would be really appreciated