Team Picker

**noelnoone** · 06-24-2010, 07:51 AM

Hi,
Was wondering if someone could help me? I have been asked by the local golf society to create a Team pairing / generator.
I want to be able to list all the men - approx 35 members on 1 list
a second list would contain the the 7 Female members.

The idea is to pair 3 men and 1 woman idealy based on who turns up on the outing.

I did find the following spreadsheet online and it generates random team but i have no way of allowing only 1 woman per team.

I have not got a great deal of VBA / Macro knowledge.

Thanks,

**masteff** · 06-24-2010, 05:45 PM

Based on the way this spreadsheet works, the woman should be listed first on the data sheet, which would result in them being assigned 1 per team (unless # of women > # of teams in which case a few would have 2 women randomly assigned).

**MarvinP** · 07-23-2010, 11:28 AM

Instead of using a UDF (user defined function) for number of 3 man teams and 4 man teams you could use the following formulas:
Number of players is in cell C1
Number of 3 man teams in cell C3: =MOD(4-MOD(C1,4),4) then
Number of 4 man teams in cell C4: =(C1-(C3*3))/4

**MarvinP** · 07-23-2010, 07:35 PM

I play a lot of golf and created a spreadsheet and some code that should handle this problem.
See the attached. - I hope the attach file worked...

**ballesdegolf** · 12-10-2010, 03:31 PM

Originally Posted by MarvinP

I play a lot of golf and created a spreadsheet and some code that should handle this problem.
.

Hi MarvinP,

Do you have any good ideas on how to adapt your code to increase the number of groups? Ideally we want to create 4 groups Ladies, Div1, Div2, Div3 so that Ladies would be distributed evenly (as in your original) and then the remaining places filled with respect to Div - so that groups would be filled Lady1, Div3a, Div2a, Div2b then Lady2, Div3b, Div2c, Div2d and Lady3, Div2e,Div1a, Div1b etc so that players generally play within similar standards to each other whilst maintaining the random aspect within the overall structure?

I have had a look at the code in VB but I am struggling to see where to start,

Sorry if this seems an impossible question!!

Any thoughts greatly appreciated.

Regards,

**Rockytfox** · 04-16-2015, 10:21 AM

Hi Marvin, I understand this post is almost 5 years old at this point so I hope you are still around or someone else could help. I'd like to modify the formula/vba to prefer threesomes over foursomes. The reason is that I am trying to adapt this Team Picker for assigning teams in my office for random happy hour events and we need an odd number of players. We are a small company so teams of 3 is more conducive to our small team.

Again, I know this post is almost 5 years old so I am looking for anyones help. Thanks in advance.

**Marcol** · 12-10-2010, 04:01 PM

Try this one I made earlier. Click the yellow cells to generate a random selection, no need to delete old selections.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim LastRow As Long, RowNo As Long, n As Long
    Dim isect As Range
    
    Application.ScreenUpdating = False
    
    Set isect = Intersect(Target, Range("A1:C1"))
        
    If Not isect Is Nothing Then
        LastRow = UsedRange.Find(What:="*", _
                     SearchDirection:=xlPrevious, SearchOrder:=xlByRows, _
                     LookIn:=xlFormulas).Row
        For RowNo = 3 To LastRow
            Range("D" & RowNo).Formula = Rnd()
        Next
        Range("B2:D" & LastRow).Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlYes, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
        Range("A2").Select
        Range("D3:G" & LastRow).ClearContents
        n = 2
        For RowNo = 3 To LastRow Step 2
            n = n + 1
            Range("E" & n) = n - 2
            Range("F" & n) = Range("B" & RowNo)
            Range("G" & n) = Range("B" & RowNo + 1)
        Next
    End If
    
    Application.ScreenUpdating = True
    
End Sub

Hope this helps

**ballesdegolf** · 12-10-2010, 04:31 PM

Hi Marcol

Thanks for your reply. I am really trying to expand on MarvinP original idea so that, rather than just randomly assigned teams there is more of an element of 'seeding' but without just filling groups from the bottom (lowest handicap) up. Since MarvinP's spreadsheet allows for the Ladies to be treated separately, I am looking for a way to expand the idea so that all the ladies would be spread out across the teams, them Div3 players would primarily be grouped with Div3 and Div2 players and the remaining Div2 players grouped with Div1 players (whilst maintaining 1 Lady per group where there are that many playing) so that groups would look a bit like this:

Group1:Lady1,Div3a,Div2a,Div2b
Group2:Lady2,Div3b,Div2c,Div2d
Group3:Lady3,Div3c,Div2e,Div2f
Group4:Lady4,Div2g,Div2h,Div1a
Group5:Div2i,Div2j,Div1b,Div1c (as there are only 4 ladies in this example)
etc

where the selections are random from within each of the 4 pools of players (Ladies, Div1, Div2 + Div3)

Thanks again for your input, much appreciated - if I have misunderstood your post please could you explain a little of how your code would help me to this end.

Regards

Jim

**Marcol** · 12-10-2010, 04:40 PM

No you haven't misunderstood, this was just a simple example.

I'll look through some others I have later, unfortunately they are on a different machine, so I can't look now.

**ballesdegolf** · 12-10-2010, 05:06 PM

OK thanks!

Jim

**Marcol** · 12-13-2010, 02:47 PM

How many names would you typically have in each "division" ?

i.e.
Ladies = ?
Division 1 = ?
Division 2 = ?
Division 3 = ?

Would any division be consistantly greater than the others?

[EDIT]
Just noticed.

Whos' thread is this noelnoone or ballesdegolf, the OP doesn't seem to have reurned any posts.

I'm a bit confused....

**1rtom** · 01-28-2013, 01:58 AM

This approach just helped me solve a similar (enough) challenge that required random selection from a list without duplicating any selections. Seems so elegantly simple now, after seeing your solution (macro) play out, but it's bugged me for a while! Thank you!

Team Picker

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Team Picker

Re: Team Picker

Re: Team Picker

Re: Team Picker

Re: Team Picker

Re: Team Picker

Re: Team Picker

Re: Team Picker

Re: Team Picker

Re: Team Picker

Re: Team Picker

Re: Team Picker

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions