random numbers to equal a total

Hello all,

I am trying to put together teams for a golfing outing and i would appreciate your help.
In attached i have players in colA and there rating in colB.
What i was hoping to do in G17 if i write in a total,i would like col G to pull 10 random numbers from colB that equall that total in G17 and col F will show there matching players.
If you can help,thank you.

See the Attached.

It does not work exactly as you need it so hopefully someone else can help improve what I have done. Anyhow, this works to the extent that in cell F32 you can type in the # of golfers you want on a team. Once you type that in, just keep hitting the refresh button that I've embeded until you get to the total Start rating for the team that you want. When you open the sheet, you will have to enable macros for this to work.

For example, if you enter 10 team members in cell F32 and want a total star rating of 38, then just keep hitting the refresh button until you get to the number 38 in cell G32. Once you have that, you should double check to make sure none of the players are duplicates. If they are, then just keep hitting the refresh button again until you get to 38 once more and hopefully that time around you won't have duplicates.

That's what I've got for now. I'll be interested to see what other might be able to add to this, but at least it accomplishes what you need for the time being.

Can't help thinking you might have to press the refresh button a lot of times ...

This modification loops around until the sum in G32 matches a target in H32 ... obviously the cells can be switched around.

Please Login or Register  to view this content.

Regards

A combined effort ... your workbook, The Exceller's idea and formulae (slightly modified) and my code (again, based on The Exceller's suggestion)

See the attached workbook.

Still the caveat that you might get duplicates ...

Regards

@dealer: just beware, because of the nature of this solution, the player list will regenerate if you do *anything* on the spreadsheet, for example, enter a character in a cell and press enter. The new list may well not meet the criteria. You can press the Refresh button but then whatever you keyed in may not be relevant.

There's no way to suppress this, AFAIK. I would recommend that, once you have a list of players that matches your star ratings, you copy the data and Paste Special | Values to another location to fix (?) the results.

Regards

Good Call TMShucks. That is definitely something to be aware of. Also, great work on the code you suggested. I'm impressed and learned something from it as well. I hope it's working for you Dealer.