Find multiple unique solutions with Solver

[jvanspro]

Is there a way to find multiple unique solutions with solver. I've been trying to figure out how to do this for some time now. I created a lineup optimizer for the NBA. Unfortunately I can only use solver to find 1 lineup that fits within the parameters. Is there a way to find multiple solutions and paste them to another worksheet? I have a cell in the worksheet to specify the quantity of lineups I'd like solver to run. Attahced is my document. Any tips would be great. Thanks in advance

[TABLE="width: 1086"]
<tbody>[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]George Hill[/TD]
[TD="align: right"]5700[/TD]
[TD="align: right"]25.36479[/TD]
[TD]PG[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Gorgui Dieng[/TD]
[TD="align: right"]4800[/TD]
[TD="align: right"]25.23017[/TD]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SF[/TD]
[TD]SF 1[/TD]
[TD="align: right"]1[/TD]
[TD]Andre Iguodala[/TD]
[TD="align: right"]4900[/TD]
[TD="align: right"]25.17617[/TD]
[TD]SF[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Kent Bazemore[/TD]
[TD="align: right"]5500[/TD]
[TD="align: right"]25.15036[/TD]
[TD]SG[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SF[/TD]
[TD]SF 2[/TD]
[TD="align: right"]1[/TD]
[TD]Omri Casspi[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]25.12288[/TD]
[TD]SF[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Mason Plumlee[/TD]
[TD="align: right"]5300[/TD]
[TD="align: right"]25.10088[/TD]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Elfrid Payton[/TD]
[TD="align: right"]6700[/TD]
[TD="align: right"]0[/TD]
[TD]PG[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Victor Oladipo[/TD]
[TD="align: right"]6700[/TD]
[TD="align: right"]0[/TD]
[TD]SG[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Evan Turner[/TD]
[TD="align: right"]5300[/TD]
[TD="align: right"]24.18087[/TD]
[TD]SF[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]C.J. Miles[/TD]
[TD="align: right"]5400[/TD]
[TD="align: right"]23.94174[/TD]
[TD]SF[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Robert Covington[/TD]
[TD="align: right"]6400[/TD]
[TD="align: right"]23.77627[/TD]
[TD]SF[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Kentavious Caldwell-Pope[/TD]
[TD="align: right"]5900[/TD]
[TD="align: right"]23.70477[/TD]
[TD]SG[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Klay Thompson[/TD]
[TD="align: right"]6900[/TD]
[TD="align: right"]23.49132[/TD]
[TD]SG[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Tim Duncan[/TD]
[TD="align: right"]6200[/TD]
[TD="align: right"]23.44853[/TD]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]DeAndre Jordan[/TD]
[TD="align: right"]7400[/TD]
[TD="align: right"]23.29851[/TD]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Darren Collison[/TD]
[TD="align: right"]5300[/TD]
[TD="align: right"]23.26877[/TD]
[TD]PG[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Ian Mahinmi[/TD]
[TD="align: right"]5300[/TD]
[TD="align: right"]23.15842[/TD]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Jordan Hill[/TD]
[TD="align: right"]4600[/TD]
[TD="align: right"]23.00413[/TD]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Kenneth Faried[/TD]
[TD="align: right"]6600[/TD]
[TD="align: right"]22.83006[/TD]
[TD]PF[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Will Barton[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]22.82795[/TD]
[TD]SG[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Raymond Felton[/TD]
[TD="align: right"]4900[/TD]
[TD="align: right"]22.53249[/TD]
[TD]PG[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]Joe Johnson[/TD]
[TD="align: right"]5300[/TD]
[TD="align: right"]22.40724[/TD]
[TD]SG[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SG[/TD]
[TD]SG 2[/TD]
[TD="align: right"]1[/TD]
[TD]Kevin Martin[/TD]
[TD="align: right"]4300[/TD]
[TD="align: right"]22.38979[/TD]
[TD]SG[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Players[/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Salary[/TD]
[TD="align: right"]60000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]Maximum Salary[/TD]
[TD="align: right"]60000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]# of Lineups[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Points[/TD]
[TD="align: right"]300.2683[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PG 1[/TD]
[TD]Brandon Knight[/TD]
[TD]PHO[/TD]
[TD="align: right"]7600[/TD]
[TD="align: right"]38.20131[/TD]
[TD][/TD]
[TD]Position[/TD]
[TD]# of Players[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PG 2[/TD]
[TD]Tyreke Evans[/TD]
[TD]NOP[/TD]
[TD="align: right"]7200[/TD]
[TD="align: right"]38.05809[/TD]
[TD][/TD]
[TD]PG[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]SG 1[/TD]
[TD]Eric Gordon[/TD]
[TD]NOP[/TD]
[TD="align: right"]4900[/TD]
[TD="align: right"]26.62158[/TD]
[TD][/TD]
[TD]SG[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]SG 2[/TD]
[TD]Kevin Martin[/TD]
[TD]MIN[/TD]
[TD="align: right"]4300[/TD]
[TD="align: right"]22.38979[/TD]
[TD][/TD]
[TD]SF[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="colspan: 3"]

<tbody>
</tbody>

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]SF 1[/TD]
[TD]Andre Iguodala[/TD]
[TD]GSW[/TD]
[TD="align: right"]4900[/TD]
[TD="align: right"]25.17617[/TD]
[TD][/TD]
[TD]PF[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]SF 2[/TD]
[TD]Omri Casspi[/TD]
[TD]SAC[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]25.12288[/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PF 1[/TD]
[TD]Anthony Davis[/TD]
[TD]NOP[/TD]
[TD="align: right"]10500[/TD]
[TD="align: right"]47.6931[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PF 2[/TD]
[TD]Ryan Anderson[/TD]
[TD]NOP[/TD]
[TD="align: right"]6600[/TD]
[TD="align: right"]29.59775[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]C 1[/TD]
[TD]Andre Drummond[/TD]
[TD]DET[/TD]
[TD="align: right"]9000[/TD]
[TD="align: right"]47.4076[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Points[/TD]
[TD="align: right"]300.2683[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Worf]

What option best describes your problem?

1) You know multiple solutions exist for a given set of constraints and Solver stops at the first one. This means one problem has multiple solutions and we are dealing with a single problem.

2) You want to change at least one constraint and run Solver again to find another solution. This means one problem has one solution and we are dealing with multiple problems and solutions.

 

[jvanspro]

First off thank you for replying. I've been struggling with this for awhile. Option 1 best describes my problem. I would like solver to give multiple solutions based on the number I specify. For example, if I have the 10 in cell c6, I would like solver to find 10 solutions and display the results in another worksheet. I have a copy of the file if you'd like to view it to better understand the issue I'm running into. I don't see an attach option.

 

[Worf]

What concerns me is, does ten solutions exist for a fixed set of constraints?

You can upload the workbook to a sharing site like Drop Box and paste a link here. I’ll get back to you if I don´t understand your project.

 

[jvanspro]

Attached is a link to the file. Within the file there are many worksheets. The ones I'm working on are "Optimal Lineup" and "Lineups." They are the 2nd and 3rd on the list. Solver is in the "Optimal Lineup" sheet. There is a button called "Find optimal lineup" that runs solver. Above the button is a call with a number, that I want to tell solver how many unique solutions I'd like it to find. Then copy and paste the solutions into the worksheet "Lineups."

I understand that all solutions will not be perfect and some will have a higher score than others but I'd like to use my judgement to choose one.

thanks again for the help and support as I've been working on this for awhile and haven't been able to figure out how to do it.

https://www.dropbox.com/s/k2k0qpflnepz22n/NBA Projections.xls?dl=0

 

[Worf]

It’s not guaranteed that you will get the specified number of solutions, but running the macro repeatedly will probably produce different solutions:

Dim opt As Worksheet, v%


Sub FindLineupNBA()
Set opt = Sheets("Optimal Lineup")
v = CInt(opt.Range("j204"))
solveroptions 0, 1000, 0.000001, , False, , 1, , 0.1, True, 0.0001, True, 200, 0, False, True, 0.075, v, v, False, 30
opt.Activate
SolverOk SetCell:="$E$206", MaxMinVal:=1, ValueOf:=0, ByChange:="$C$2:$C$201", Engine:=3, EngineDesc:="Evolutionary"
SolverSolve (True), "ShowTrial"
End Sub


Function ShowTrial(Reason%)
Dim r As Range, lu As Worksheet
Set lu = Sheets("Lineups")
MsgBox Reason
opt.UsedRange.Copy
Set r = lu.Cells(lu.Range("a" & Rows.Count).End(xlUp).Row + 2, 1)
r.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False     ' paste intermediate result
ShowTrial = 0
End Function