1

I have the following problem which i'd like to solve with the excel solver or any other tool (any suggestion is welcome) but I would like not to write code.

I have several items (around 40) to put in several backpack (around 5). Every item has a different weight but every backpack has the same space.

The sum of the items' weight is much less then the capacity of the backpacks.

What i need to do is to allocate the items in the backpack filling all of them with more or less the same weight. In other words reducing the variance.

There is a constraint: some items cannot go together. I have a list (or adjacency matrix) of the items that can or can't go together.

Of course once one item is in a backpack cannot go in a second one (there is only one item for each king of items).

I'm trying to solve this with the excel solver but all 3 of the algorithms say they cannot find solutions, but manually i can find them so I think I'm not configurating properly.

Anyway I could configurate in excel only the part of the problem concerning weights but i cannot set up the part of the problem concerning the incompatibility between items.

Thank you for your help

AndreA
  • 779
  • 9
  • 22
  • ```but i would like not to write code```. Hmmm. SO is about programming questions! – sascha Sep 19 '16 at 13:43
  • This is the reason I wrote "I'd like" and not "I won't" ;) – AndreA Sep 19 '16 at 14:14
  • I know I'm pretty late a year and a half later, but I had to answer this as this is exactly the problem I've been working on (http://cerc-datascience.polymtl.ca/person/philippe-olivier). I could upload my model to Github if you still need this. – Philippe Olivier Mar 19 '18 at 12:35

1 Answers1

1

This is more multiprocessor scheduling with side constraints than knapsack.

You can try a naive formulation like so. For each item, there are [number of backpacks] 0-1 variables indicating which backpack the item is in, and a constraint that those variables sum to 1. The objective is to minimize the maximum total weight in a backpack. For each pair of items that can't go together, there are [number of backpacks] constraints that the sum of the corresponding indicator variables is less than or equal to 1.

Here's a worked example with two backpacks (A and B), three items (x, weight 3; y, weight 1; and z, weight 4), and one conflict (x cannot be with y).

minimize C
over 0-1 variables Ax, Ay, Az, Bx, By, Bz and real variable C
subject to
C >= 3*Ax + 1*Ay + 4*Az  # load in A
C >= 3*Bx + 1*By + 4*Bz  # load in B
Ax + Bx = 1  # one placement of x
Ay + By = 1  # one placement of y
Az + Bz = 1  # one placement of z
Ax + Ay <= 1  # conflict between x and y in A
Bx + By <= 1  # conflict between x and y in B

This formulation is not optimal because there's no symmetry breaking -- in essence, the LP solver's search tree is duplicated by a factor equal to the number of permutations of backpacks. This is only 5! = 120 in your worst case, though, so it might be fine. The way to go is probably column generation with a master problem that amounts to exactly covering the items with the right number of backpacks and a subproblem that amounts to packing one backpack subject to constraints, but this is out of scope for Excel.

David Eisenstat
  • 64,237
  • 7
  • 60
  • 120