Macro for making golf teams randomly

Hey Randy,
Try the attached to see if it does what you want.

After looking at your workbook and seeing the Stableford point formula I remembered an easier method.
The Choose function is much easier to deal with. Here is my suggestion,

=Choose(Score - Par +4 , 7,5,3,2,1,0,-1,-1,-1,-1,-1,-1) should give your correct Stableford points.

Example: Player shoots 4 on a par 5 hole. So the Score - Par + 4 = 3rd number in choose list = 3

The cool part about using Choose is that if you want to make an Eagle worth 4 instead of 5 points you would simply need to change the 5 to a 4 in the Choose list.

Marvin, it almost works. The number of 4-somes and 5-somes in cells D5 and D6 need to manipulate making the teams. If we have 20 players that would be (4) 5-somes, but when I hit the make the teams button it has (2) 5-somes and (2) 4-somes for a total of 18 players not 20. And some of the players are duplicated.

So how can I get the macro to look over at column B and pull the names then make the teams according to how many 4-somes and/or 5-somes are in cells D5 & D6?

PERFECT!!! Thank you very much for this. I just don't see how you guys think of this stuff.

Hey Randy,
I used tricks from dealing a deck of cards and a little Diophantine Equation help for the answer. Also, you taught me something about conditional formatting on your sheets. I've always wanted to color Birds Green, Pars No Color and Boggys Red. I found that conditional formatting using formulas and relative formatting really did what I wanted. See the attached sheet and look for conditional formatting rules...

Glad it worked for you. Going back to your TeamPicker, what would be really cool is if you had 3 macros for team picking. The first would be 4-somes and 5-somes just like I have it. Then have another button for 2-somes and one for 3-somes. We have a draw sometimes at the end of a round for 2-somes and 3-somes as side bets.

The next thing I will be working on is a spreadsheet for settling bets. Mainly 2-downs and 1-downs. Do you by chance have those formulas?

I am impressed with the Team picker spreadsheet and associated coding.
I have recently returned to teaching High School students and have a need to generate groups of 4 & 3 as well as 4 & 5. I have modified the Golf Teampicker spreadsheet to do the 4 and 5 groups, and this with the exception of one addition is working superbly.
My coding knowledge is poor in VB as I have not coded much since MSDos Basic days. I have managed to make changes to the original code but this is cosmetic only and I can not get the code to populate the class list correctly. I have included some excel comments in key cells to explain the problem. At the moment I can get the code to make the correct number of rows but I can not get it to only fill the correct items per row. I have generated the number of foursomes and threesomes based on a matrix sheet.

Also an extra bonus would be to be able to select only those present to generate the groups from. I have put a tick control box in on sheet to show the chosen students to make the class list for inclusion into the groups.
Each Groupsheet would load the ID number's next to the ticked boxes only and then contiguously populate the first column with present students ID numbers.

This is awesome. You guys rock! Glad I found this post.

I am really new to macros but this is brilliant but I would like to have teams of 3 of 4 as we play weekly( 21 persons) but no duplicates together until necessary !.

How would I do that ?

Cheers,
Mary

Hi Marv,

Thanks, didn't know that, will do as you advise.

cheers,
Mary