random generator for a schedule

[dcstall]

I have 27 players.

I need 19 of the 27 randomly chosen each week (for a 10 week schedule)

this should allow each player 6 or 7 games over the 10 week period.

[LewisJ]

It would be helpful to attach the file.

[Logit]

Here is one way - I am certain there are many more ...

Please Login or Register  to view this content.

[dcstall]

Thank you! this close. BUT...each week i need a random set of names from an original list of 27......and no one name can or should repeat more than 7 times. make sense ?

meaning, i have 27 players in total....but i can only play 19 each week. i play for 10 weeks

[shg]

Eight people are left out each week; over 10 weeks that's 80 people. So everyone should be left out three times, except for one lucky person that gets left out only twice. No?

Conversely, there are 190 people that participate over the 10 weeks; so everyone participates in 7 games (7 * 27 = 189), and that one lucky person participates in eight.

[dcstall]

yes, that sounds right! :-)

[dcstall]

any luck with furthering your macro to help with my issue? randomly selecting the 19 out of 27 for each of 10 weeks. No one should play more than 7 times in the 10 week period...except for 1

[Alf]

You could use "OpenSolver" for this as the Excel solver is not powerful enough since the number of variable cells exceeds capacity of Excel solver.

opensolver.jpg

If image is to small to be readable right click on image and open it in a separate tab.

Alf