Solver spreadsheet setup help needed

Hello,

I need to solve a question related to optimizing the allocation of meters/risers among modules. relationship among the three is:

each riser has multiple meters attached to it, and one riser can hold max 128 meters, the number of meters each riser has will be given (not variable)

each module can hold max 128 meters.


goal is to minimize the number of modules needed while assigning all of the risers and the related meters to a module. and the number of meters in a riser can't be split.

i.e.
Riser# No.Of meters
1 99
2 111
3 71
4 18
5 5
6 128

so in this case
one solution would be
module 1 will hold riser 6 (total number of meters 128)
module 2 will hold riser 1, 4, 5 (total number of meters 122)
module 3 will hold riser 2 (number of meters 111)
module 4 will hold riser 3 (number of meters 71)
this solution requires 4 modules.

I'm not sure how to set this up properly using solver, attached is not working, can someone please help?

Perhaps a model like this?

There are two different solvers. The first sheet uses the Excel solver and the second sheet uses the freebie OpenSolver developed by the University of Auckland NZ.

This freebie can be downloaded from this link: https://opensolver.org/

Alf

oh nvm I saw it is under Data...thank you so much again!

You are welcome

I see you have sorted your problem out good!

Alf

Ps Thanks for the rep.