Distribute numbers into required sets that maximize their totals.

[aceshigh123]

Hi all,

New Member here. I hope you guys can help me. Ive asked this on another forum and didnt get a useable answer. https://www.excelforum.com/excel-pr...ts-that-maximize-their-total.html#post5016154
Im hoping someone here can help. I have the following problem to tackle.


Lets say i have 30 pieces of wire of random length.
I want to take that set of numbers (length) in column B7:B36, split them into required number of combinations/batches (column F7:07), number of columns to split in is entered in cell C4.

I want to maximize the total (length) of each set of values to the point where they are AS CLOSE AS POSSIBLE to the average with NO DUPLICATES.
However, If a number appears twice in the list, it should show up twice in the combinations, once a number is used, it cant be used again

The quantity of pieces in a set does not need to be equally split, rather the TOTAL of combination must be similar.
Combination 1 can have 10 pieces of wire and 2 can have 5 pieces, but the total length of both should be as close as possible and all pieces must be used

I hope im explaining this right.

Thanks in Advance.

Attached is a pic of the spreadsheet.

 

[ravishankar]

Re: Help Solve. Distribute numbers into required sets that maximize their totals.

how much of deviation are you ready to accept? if your total length is 1500 mm and split into 8 groups, mean is 187.5 mm. 187+/- 2 mm, 5 mm, 20 mm
ravi shankar

 

[aceshigh123]

Re: Help Solve. Distribute numbers into required sets that maximize their totals.

Hi ravi.

+ or - 5% is acceptable.