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

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

<tbody>
</tbody>

 

[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