Generate Random Teams

**rwhite713** · 07-24-2010, 10:45 AM

Hello everyone, I found a thread from a member here (MarvinP) and it's almost exactly what I am looking for. However, I am looking to generate 4-man and/or 5-man teams.

Where column A is my Player ID
E1 would be total # of players
E3 would be number of 5-somes
E4 would be number of 4-somes

Column I-M would the teams

What is the VB code for making random teams with 4-somes being the most desired and 5-somes would be only used when necessary to fill the field. i.e 22 players would be (3) 4-somes and (2) 5-somes. (20) players would be (5) 4-somes and (0) 5-somes, 21 players would be (4) 4-somes and (1) 5-some, etc... The formula I have in E3 & E4 is not working.

I also want to be able to run the macro by clicking "make teams" and having the teams populated in columns I-M. Thank you in advance for your help. Spreadsheet is attached.[ATTACH]Attachment 79776[/ATTACH]Attachment 79776

**shg** · 07-24-2010, 02:02 PM

I did this a while ago. It's designed for shotgun starts, and assigns teams to have about equal total handicaps. You could instead sort the players in random order for random assignments.

The size of each team depends on the number of players and number of holes.

**rwhite713** · 07-24-2010, 04:53 PM

Thank you shg, I might have known you would help!

I'm trying to understand your program. I would need to have two tabs for this, correct? I actually want to do it on one tab and with a macro button. Does the RAND formula work that way? I'm going to cut and paste your formulas onto my worksheet and play with it to try and better understand it.

I never know from day to day who will be in the game (may be 12 or 40 players sometimes 60-100 on our "major" events).

I had hope that I could make the teams on one tab by simply inserting the player ID in column A. Then click the macro button and have the teams populated right over to the right. Is that too much of a hassle to write that macro? I really liked marvinp's thread/spreadsheet - but he had it for men and women - I just need it for men and 4somes and 5somes.