Formula or Macro to calculate combinations which produce whole numbers

**j.farr3ll** · 03-30-2012, 06:22 AM

I have a dataset where there is a column which identifies certain records. i.e. the cell takes a value 1 if the record needs including and 0 if not.
There is also a column which gives a number of parts needed. i.e. 1.56 or 2.73 etc.

What I'm trying to do is split the records into batches of 4 to minimise wastage. So I need a formula or macro to look at the identifier column and then group together those records identified in the groups which minimise wastage according to the number of parts column.

So ideally the output would be something like:

group 1: A D E P
group 2: B C F G

etc...

I'm sure this must be possible, but can't figure it out. Any help would be greatly appreciated.

(this is similar to a previous post, but I have simplified the process as what I asked for before was overly complicated I think).

**darknation144** · 03-30-2012, 07:32 AM

This is called the bin packing problem and you would have to do it with VBA google the problem and there will be various tutorials.

**j.farr3ll** · 03-30-2012, 09:32 AM

Thanks for this. looks like a good place to start. I was thinking it would probably need VBA using a loop

**j.farr3ll** · 04-02-2012, 06:49 AM

I'm still struggling on this. I can't modify the examples I can find on the bin packing problem online as they're all tailored to a given value as the decison criteria, whereas I'm looking for any whole number or closest value to a whole number.

At the moment this looks to me to be unmanageable.