Trying to use the least number of packages (cartons)

[twelchone]

I need to take information from 2 data fields,(an ID and Quantity field), from multiple records, (may be several hundred records) and find the most efficient way to combine the quantities to total a number that would be as close as possible or equal to 1000 without going over. I need to add a NewID to these combined quantities.

example: The result should look like this:
ID Qty ID Qty NewID
1 420 1 420 P1
2 280 2 280 P1
3 120 3 120 P2
4 300 4 300 P1
5 875 5 875 P2

You will note that the quantities of the NewID-P1 total exactly 1000. The quantities of the NewID-P2 total 995. This would allow me to see which combination of quantities will use of least number of cartons. Cartons that will hold a maximum of 1000 pieces. I would greatly appreciate any ideas that may solve this for me. Thanks.

 

[BNPMike]

This is an Operations Research type problem. I would search the internet to get the algorithm. You should be able to implement it in VBA which is a powerful programming language.