VBA to randomly select teams from list of players

E

EinarG

New Member
Joined
Jul 3, 2014
Messages
8
Excel 2013, Win 7 Pro

Starting with column A (Player Names) and column B (Player rating - value between 0-10), I need a macro to divide the players into either 2, 4 or 6 teams randomly. Column A would have less than 100 names. Teams are named TeamA, TeamB...TeamF. TeamA competes against TeamB, etc.

I have two parameters: NumTeams = Number of teams (2,4 or 6) and MaxRatingDiff = Maximum allowed Team Rating Difference between competing teams

Each team has a rating defined as the average of the ratings of the players on that team.

Competing teams either have the same number of players or one of them (B, D or F) can have an extra player when there is an odd number of players. Example: 23 players, 4 teams would result in player counts of TeamA=6, TeamB=6, TeamC=5, TeamD=6.

I need a VBA Macro to generate approximately even strength teams with the constraint that the difference in team rating between any two competing teams does not exceed MaxRatingDiff. Alternatively, a macro that can be re-run manually (using a command button for example) by the user until he/she is satisfied with the teams. The generated teams should be in columns (Name and Rating) sorted descending by the player rating.

Thanks for help on this or any portion of it!
 
Sort by date Sort by votes

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Eric W said:
Here's a version I came up with. It will handle up to 200 players, and 2-10 teams. Starting with a sheet looking like:

...
Click to expand...

Great work on this Eric W.!

Sorry to revive this dead post, but I need a similar program for a blind draft for co-ed softball league. Hopefully you or some other kind person can help me!


I need something very similar to this program but with a few important changes. Each Player has a ranking of either A, B, C, or D and they are also categorized as male or female (M or F). I need the program to randomize the list and then go through the different teams and assign 1 A-rank male and 1 A-rank female to each team in succession. If there are A ranked players left over, then it needs to go through the list of teams all over again. I need this same thing done for rank B, C and D with both male and female players. Also, players have the option of linking with one another. This means that they will always need to placed on the same team together, while still needing their rank and gender being considered. There are also people (coaches, managers, sponsors) who are preassigned to certain teams. They will already be locked on a team list and will also need to have their rank and gender considered in the overall placement.

I do like how this program assigns rankings based on numbers, and calculating the max rating difference. Using something like this could work, but it's more important for the teams to have around the same number of A, B, C and D ranked males and females.

Can you or anyone help me? It would be so much appreciated and it would save the Softball League Board a lot of headaches.
 
Upvote 0
Welcome to the forum!

You're a bit lucky I spotted your message. I stopped following this thread over a year ago. For future reference, a PM is also a good way to get my attention! :)

As far as your question, I could probably come up with something. I'm a bit busy right now, so it could take a few days. If someone else wants to take a stab at it, go ahead!
 
Upvote 0
Eric W said:
Welcome to the forum!

You're a bit lucky I spotted your message. I stopped following this thread over a year ago. For future reference, a PM is also a good way to get my attention! :)

As far as your question, I could probably come up with something. I'm a bit busy right now, so it could take a few days. If someone else wants to take a stab at it, go ahead!
Click to expand...

Thanks Eric W.!
 
Upvote 0
OK, I came up with something. Very rough, but it seems to work. I might clean it up a bit after you have the chance to try it out.
First, set up a blank sheet like this:
Excel 2012
A
B
C
D
E
F
G
H
I
Player
Gender
Rating
Grouping
Totals
# of teams
A1
M
D
F - A
A2
F
B
M - A
A3
M
D
F - B
A4
M
A
M - B
A5
M
A
F - C
A6
M
A
A
M - C
A7
M
C
A
F - D
A8
M
B
A
M - D
A9
F
C
A10
M
D
A11
M
A
A12
F
C
A13
M
B
B
A14
F
B
B
A15
M
B
A16
M
B
A17
M
D
A18
F
B
A19
F
A
A20
M
A
A21
M
A
A22
F
D
A23
F
D
A24
M
D
A25
F
A
A26
M
A
A27
F
D
C
A28
F
A
A29
M
B
C
A30
F
B
A31
F
D
A32
F
B
C
A33
M
C
A34
M
D
A35
F
B
A36
M
A
A37
F
C
D
A38
F
D
D
A39
F
D
D
A40
F
D

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]13
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]

[TD="align: right"][/TD]

[TD="align: right"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]

[TD="align: right"][/TD]

[TD="align: right"]16
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]

[TD="align: right"][/TD]

[TD="align: right"]19
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G2
[/TH]
[TD="align: left"]=COUNTIFS(B:B,LEFT(F2),C:C,RIGHT(F2))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Put the values in column F, they are the 8 categories of players. Put the G2 formula in and drag down to G9. Put the names in column A as far down as you need. Columns B and C should be obvious. Column D is how you link groups of people. If you have 3 people who need to be on the same team, give them a unique identifier. I just used letters, but it could be "Group1", "Team Fox", or whatever, as long as it's the same for all of them. As you can see with "C", they don't even need to be adjacent.

Next, add the VBA. Right-click on the sheet tab on the bottom and select "View Code". This will open the VBA editor. From the menu, select Insert > Module. On the sheet that opens, paste this code:
Code: 
Sub GetCounts()
Dim cts() As Long

    w = Array(0, 4, 4, 3, 3, 2, 2, 1, 1)
    t = Range("I2").Value
    tots = Range("g2:g9").Value
    i = 1
    ReDim cts(1 To t, 1 To 8)
    r = 1
    c = 1
    Do
CkAgain:
        If tots(i, 1) = 0 Then
            i = i + 1
            c = c + 1
            If i > 8 Then Exit Do
            GoTo CkAgain:
        End If
        cts(r, c) = cts(r, c) + 1
        tots(i, 1) = tots(i, 1) - 1
        r = r + 1
        If r > t Then r = 1
    Loop
    
    Columns("K:ZZ").ClearContents
    Cells(11, "J") = "Players/team"
    Cells(13, "J") = "Avg team rating"
    
    For i = 1 To t
        Cells(1, i + 10) = "Team " & i
        For r = 1 To 8
            Cells(r + 1, i + 10) = cts(i, r)
            Cells(11, i + 10) = Cells(11, i + 10) + cts(i, r)
            Cells(12, i + 10) = Cells(12, i + 10) + w(r) * cts(i, r)
        Next r
        Cells(13, i + 10) = Cells(12, i + 10) / Cells(11, i + 10)
    Next i
    
End Sub

Sub PlacePlayers()
Dim wkcts(1 To 8)
    classes = Array("FA", "MA", "FB", "MB", "FC", "MC", "FD", "MD")
    
    t = Range("I2").Value
    cts = Range(Cells(2, "K"), Cells(9, t + 10)).Value
    For i = 1 To 8
        If WorksheetFunction.Sum(WorksheetFunction.Index(cts, i, 0)) <> Cells(i + 1, "G") Then
            MsgBox "Counts do not add up properly.  Rerun 'GetCounts' and adjust accordingly."
            Exit Sub
        End If
    Next i
    
    numplayers = Cells(Rows.Count, "A").End(xlUp).Row - 1
    If numplayers <> WorksheetFunction.Sum(Range("G2:G9")) Then
        MsgBox "The totals in G don't sum up to the number of players.  Make sure each player" & _
               " has a gender and a rating."
        Exit Sub
    End If
    
    tries = 0
    
BigLoop:
    cts = Range(Cells(2, "K"), Cells(9, t + 10)).Value
    numplayers = Cells(Rows.Count, "A").End(xlUp).Row - 1
    players = Range("A2:D" & numplayers + 1).Value
    numgroups = Evaluate("COUNTA(D2:D" & numplayers + 1 & ")")
    
    ReDim teams(0 To WorksheetFunction.RoundUp(numplayers / t, 0), 1 To t)
    
SmallLoop:
    Erase wkcts
    r = Int(Rnd() * numplayers) + 1
    np = 1
    If numgroups > 0 Then
        While players(r, 4) = ""
            r = (r Mod numplayers) + 1
        Wend
        Grp = UCase(players(r, 4))
        np = 0
        For i = 1 To numplayers
            If UCase(players(i, 4)) = Grp Then
                np = np + 1
                j = WorksheetFunction.Match(players(i, 2) & players(i, 3), classes, 0)
                wkcts(j) = wkcts(j) + 1
            End If
        Next i
    Else
        np = 1
        j = WorksheetFunction.Match(players(r, 2) & players(r, 3), classes, 0)
        wkcts(j) = wkcts(j) + 1
    End If
    
    r2 = Int(Rnd() * t) + 1
    For i = 1 To t
        For j = 1 To 8
            If wkcts(j) > cts(j, r2) Then GoTo Nope:
        Next j
        GoTo GotOne:
Nope:
        r2 = (r2 Mod t) + 1
    Next i
    tries = tries + 1
    If tries > 1000 Then
        MsgBox "Can't find a set of teams in 1000 tries.  Check to see that you don't have any unworkable groups, " & _
               "such as a group containing 4 male A players, when the maximum number of male A players on any " & _
               "team is 3."
        Exit Sub
    End If
    GoTo BigLoop:
    
GotOne:
    For j = 1 To 8
        cts(j, r2) = cts(j, r2) - wkcts(j)
    Next j
    
    If numgroups = 0 Then
        teams(0, r2) = teams(0, r2) + 1
        k = teams(0, r2)
        teams(k, r2) = players(r, 1)
        
        For j = 1 To 4
            players(r, j) = players(numplayers, j)
        Next j
        numplayers = numplayers - 1
    Else
NextGrp:
        For i = 1 To numplayers
            If UCase(players(i, 4)) = Grp Then
                teams(0, r2) = teams(0, r2) + 1
                k = teams(0, r2)
                teams(k, r2) = players(i, 1)
                For j = 1 To 4
                    players(i, j) = players(numplayers, j)
                Next j
                numplayers = numplayers - 1
                numgroups = numgroups - 1
                GoTo NextGrp:
            End If
        Next i
    End If
    
    If numplayers > 0 Then GoTo SmallLoop:
    
    Range(Cells(1, t + 12), Cells(UBound(teams) + 1, 2 * t + 11)).Value = teams
    For i = 1 To t
        Cells(1, t + 11 + i) = "Team " & i
    Next i
    
End Sub
Now, close the VBA editor with Alt-Q, or just switch back to the Excel window. Press Alt-F8, choose GetCounts, and click Run. You should see something like this:
Excel 2012
J
K
L
M
N
O
P
Q
R
Team 1
Team 2
Team 3
Team 4
Team 5
Team 6
Team 7
Team 8
Players/team
Avg team rating

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]

[TD="align: right"]13
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]12
[/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"]31
[/TD]
[TD="align: right"]31
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]29
[/TD]

[TD="align: center"]13
[/TD]

[TD="align: right"]2.538462
[/TD]
[TD="align: right"]2.384615
[/TD]
[TD="align: right"]2.384615
[/TD]
[TD="align: right"]2.307692
[/TD]
[TD="align: right"]2.416667
[/TD]
[TD="align: right"]2.416667
[/TD]
[TD="align: right"]2.416667
[/TD]
[TD="align: right"]2.416667
[/TD]

</tbody>
Sheet1​


This basically figures out how many of each type of player should be on each team. The numbers line up with the categories in column F. So in this example, Team 1 should have 2 female A players, 2 male A players, 1 female B player, etc. Row 11 shows the total number of players on the team, row 12 is a weighted total, and row 13 is the weighted team average rating. All the averages should be pretty close to each other. You won't be able to minimize the high average minus the low average, like in the other macro, since you thought having roughly equal numbers of the types of players was more important.

The method to assign counts is pretty straightforward. In this example, we have 12 FA players and 8 teams. So assign 1 to team 1, 1 to team 2, etc. After we go through all 8 teams, we still have 4 to assign, so we wrap back around and assign a 2nd FA to teams 1-4. Then starting at team 5, we start assigning the MAs in the same fashion, all the way to MD. Unless the numbers work out exactly even, you won't be able to assign the exact number of each group to each team. But this way, the number of each group (FA) will be +/- 1 for each team. Even better, the total number of As (and each other rating) will also be +/- 1 for each team.

After getting the numbers, press Alt-F8 again and select PlacePlayers. This uses the counts we just created and starts randomly assigning players to each team. It starts with the groups first, then the individuals. You should end up with something like this:
Excel 2012
T
U
V
W
X
Y
Z
AA
Team 1
Team 2
Team 3
Team 4
Team 5
Team 6
Team 7
Team 8
A45
A27
A60
A78
A74
A37
A13
A6
A12
A29
A26
A2
A42
A38
A14
A7
A75
A32
A84
A72
A54
A39
A11
A8
A16
A40
A95
A81
A63
A69
A70
A91
A46
A10
A94
A18
A55
A51
A49
A100
A58
A35
A68
A4
A30
A71
A96
A90
A47
A34
A24
A87
A86
A88
A17
A92
A48
A85
A76
A3
A31
A67
A80
A62
A53
A82
A1
A65
A66
A44
A61
A5
A41
A73
A25
A64
A23
A97
A77
A79
A56
A83
A19
A43
A99
A36
A33
A22
A21
A28
A50
A15
A52
A98
A89
A57
A20
A93
A9
A59

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: center"]4
[/TD]

[TD="align: center"]5
[/TD]

[TD="align: center"]6
[/TD]

[TD="align: center"]7
[/TD]

[TD="align: center"]8
[/TD]

[TD="align: center"]9
[/TD]

[TD="align: center"]10
[/TD]

[TD="align: center"]11
[/TD]

[TD="align: center"]12
[/TD]

[TD="align: center"]13
[/TD]

[TD="align: center"]14
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​


The teams should all have the right number of each class, and each group will all be in the same team. You can rerun it as many times as you want, and get another set of teams.

There is a possibility that no set of teams can be created. An example would be if you have a set of 3 linked players that all all MA, but as you see here, the maximum number of MAs on any team is 2. This is why I separated the two macros. If you really want to keep that group together, run the GetCounts first. Then pick any team that has a count of 2 MAs, like team 5, and add 1 to it, then subtract 1 from team 6 so that the totals still add up. Then give team 6 an MB player from team 5 so that the total number of players doesn't change. Then team 5's average rating increased, and 6's went down, so you may want to swap a C and a D player to make up for it. But this gets complicated fast! :) Try to stick with the original numbers if possible.

There were things I thought of changing, like making the formulas in K11:R13 live, so that if you do make changes to the counts, you can immediately see the impact. Or adding the gender/rating to the lists in T:AA. But try it out, let me know.

And it just occurred to me that you can group (coaches, managers, sponsors) this way, but there is a possibility of having 2 groups like that show up on the same team, space permitting. For now, just run the PlacePlayers macro again, and you should come up with a suitable set of teams pretty quickly. If not, I may have to come up with a programmatic method to accomplish that.

Let me know!
 
Upvote 0
Eric,

This is truly phenomenal! Thank you so much for all of your work on this. I am going to do some test runs and see what The Board thinks. Thanks again!
 
Upvote 0
Would it be possible to find a way to pre-assign people in the roster (like coaches, managers, sponsors) to certain teams? The way it is now, teams are having more than one coach assigned to them too often. Maybe have a fifth column on the player roster that can accept a team number, and all players that have that team number will always be assigned to that team? Of course, the rank and gender of those players will also have to be taken into account for the overall team rating.
 
Upvote 0
I'll take a look at it when I can. My thought is to use column D. Use "Team 1", "Team 2", etc. for the grouping name. Then I can change the code to only allow one group starting with "Team" on each output team. Don't use "Team" in the regular groups. Let me know if that sounds workable.

I tried sending you a PM a while back, but it said your box was full. The usual limit is 50 messages, so I'm not sure why it was rejected. It may be that you have to have a certain number of posts before you get the 50 limit. Until then, you can PM me, or just add a message to this thread, I'm still following it.
 
Upvote 0
Eric W said:
I'll take a look at it when I can. My thought is to use column D. Use "Team 1", "Team 2", etc. for the grouping name. Then I can change the code to only allow one group starting with "Team" on each output team. Don't use "Team" in the regular groups. Let me know if that sounds workable.

I tried sending you a PM a while back, but it said your box was full. The usual limit is 50 messages, so I'm not sure why it was rejected. It may be that you have to have a certain number of posts before you get the 50 limit. Until then, you can PM me, or just add a message to this thread, I'm still following it.
Click to expand...

That actually sounds like it would work just fine. It seems a lot more simple then what I was thinking too.
 
Upvote 0
OK, starting with the same layout, same headings and formulas as before:

ABCDEFGHI

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Player[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Gender[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Rating[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Grouping[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Totals[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]# of teams[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]F - A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M - A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]F - B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M - B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]F - C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M - C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]F - D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M - D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Team USA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Team USA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A24[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]M[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Team USA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=COUNTIFS(B:B,LEFT(F2),C:C,RIGHT(F2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put your groupings in column D as before. Groups starting with "Team" will not be assigned to a team that already has a "Team" group assigned to it.

Code: 
Option Explicit
' This routine calculates how many of each type of player goes on each team.
' It works by getting the counts from G2:G9, then assigning each type of player
' from left to right, looping back to the first team once we reach the last team.
' Once the count for one type of player is exhausted, the count for the next
' type of player begins at the next team.
'
Sub GetCounts()
Dim Cts() As Long, w As Variant, tots As Variant
Dim i As Long, r As Long, c As Long, t As Long

    Application.ScreenUpdating = False
    
    t = Range("I2").Value                       ' How many teams?
    tots = Range("g2:g9").Value                 ' How many of each type of player?
    
    i = 1                                       ' Counter
    ReDim Cts(1 To t, 1 To 8)                   ' # of teams, # of each type on the team
    r = 1                                       ' row
    c = 1                                       ' column
    Do
        If tots(i, 1) = 0 Then                  ' Exhausted the count of a player type?
            i = i + 1                           '  Go to next player type
            c = c + 1
            If i > 8 Then Exit Do               ' No more types?  Exit
        Else
            Cts(r, c) = Cts(r, c) + 1           ' Add 1 of this type to team r
            tots(i, 1) = tots(i, 1) - 1         ' Decrement # of players of this type
            r = r + 1                           ' Next team
            If r > t Then r = 1                 ' Loop back to team 1 if necessary
        End If
    Loop
    
' Clear output range
    Columns("K:ZZ").ClearContents
' Assign some headings
    Cells(11, "J") = "Players/team"
    Cells(13, "J") = "Avg team rating"
    
' Calculate overall team ratings.  Type A = 4, B = 3, C = 2, D = 1
' Add up the number of each type and divide by the number of players on the team
    w = Array(0, 4, 4, 3, 3, 2, 2, 1, 1)
    For i = 1 To t
        Cells(1, i + 10) = "Team " & i
        For r = 1 To 8
            Cells(r + 1, i + 10) = Cts(i, r)
            Cells(11, i + 10) = Cells(11, i + 10) + Cts(i, r)
            Cells(12, i + 10) = Cells(12, i + 10) + w(r) * Cts(i, r)
        Next r
        Cells(13, i + 10) = Cells(12, i + 10) / Cells(11, i + 10)
    Next i
    Application.ScreenUpdating = True
    
End Sub


' This routine randomly assigns players to teams.  First it assigns groups starting with
' "Team" (1 such group per team), then it assigns the rest of the groups, then it assigns
' individuals.
'
Sub PlacePlayers()
Dim WkCts(1 To 8) As Long, Cts As Variant, Players As Variant, Classes As Variant
Dim NumPlayers As Long, NumGroups As Long, NumTGroups As Long, Teams() As Variant
Dim t As Long, i As Long, j As Long, k As Long, Tries As Long, r As Long, r2 As Long
Dim Grp As String, GTCtr() As Long, TeamSize() As Long, TeamNames As Variant

    On Error GoTo Oops:
    Classes = Array("FA", "MA", "FB", "MB", "FC", "MC", "FD", "MD")
    
    t = Range("I2").Value                                   ' Get number of teams
    Cts = Range(Cells(2, "K"), Cells(9, t + 10)).Value      ' Get team counts
    
' Do some validation of the counts
    For i = 1 To 8
        If WorksheetFunction.Sum(WorksheetFunction.Index(Cts, i, 0)) <> Cells(i + 1, "G") Then
            MsgBox "Counts do not add up properly.  Rerun 'GetCounts' and adjust accordingly."
            Exit Sub
        End If
    Next i
    
    NumPlayers = Cells(Rows.Count, "A").End(xlUp).Row - 1
    If NumPlayers <> WorksheetFunction.Sum(Range("G2:G9")) Then
        MsgBox "The totals in G don't sum up to the number of players.  Make sure each player" & _
               " has a gender and a rating."
        Exit Sub
    End If
    
    Tries = 0                                           ' How many times have we tried?
    
' Initialize all counters.  At this point, we're starting from scratch
BigLoop:
    Cts = Range(Cells(2, "K"), Cells(9, t + 10)).Value                     ' Get all team counts
    NumPlayers = Cells(Rows.Count, "A").End(xlUp).Row - 1                  ' Total # of players
    Players = Range("A2:D" & NumPlayers + 1).Value                         ' Get player list (4 columns)
    NumGroups = Evaluate("COUNTA(D2:D" & NumPlayers + 1 & ")")             ' # of players in a group
    NumTGroups = Evaluate("COUNTIF(D2:D" & NumPlayers + 1 & ",""Team*"")") ' # of players in a "Team" group
        
    ReDim Teams(0 To WorksheetFunction.RoundUp(NumPlayers / t, 0), 1 To t)
    ReDim GTCtr(1 To t)
    ReDim TeamSize(1 To t)
    
' At this point, we're looking for a group/player to add
SmallLoop:
' WkCts countains the count of how many of each type of player is in a group
' A group could be a "Team" type group, a regular group, or an individual
    Erase WkCts
    r = Int(Rnd() * NumPlayers) + 1                             ' Pick a random location in the list
    If NumGroups > 0 Then                                       ' Any groups left unassigned?
        If NumTGroups > 0 Then                                  ' "Team" group unassigned?
            While LCase(Left(Players(r, 4), 4)) <> "team"       ' From the random spot,
                r = (r Mod NumPlayers) + 1                      ' go forward until we
            Wend                                                ' we find the next "Team" group
        Else
            While Players(r, 4) = ""                            ' From the random spot,
                r = (r Mod NumPlayers) + 1                      ' go forward until we
            Wend                                                ' find the next group
        End If
        Grp = Players(r, 4)                     ' Save the group name
        For i = 1 To NumPlayers                 ' Find all players in that group
            If LCase(Players(i, 4)) = LCase(Grp) Then
                j = WorksheetFunction.Match(Players(i, 2) & Players(i, 3), Classes, 0)
                WkCts(j) = WkCts(j) + 1
            End If
        Next i
    Else                                        ' Now we're down to placing individuals
        j = WorksheetFunction.Match(Players(r, 2) & Players(r, 3), Classes, 0)
        WkCts(j) = WkCts(j) + 1
        Grp = Chr$(1)                           ' Create a fake group name for individuals
        Players(r, 4) = Chr$(1)                 ' Make this player look like a 1-player group
    End If
    
' Now we have a group to assign to a team, randomly pick a team, then see if there's
' room on that team for it.  If not, try the next team.  If the group won't fit on any
' team, we have to start over from scratch.


    r2 = Int(Rnd() * t) + 1
    For i = 1 To t
        If GTCtr(r2) = 1 And LCase(Left(Grp, 4)) = "team" Then GoTo Nope:  ' "Team" group?
        For j = 1 To 8
            If WkCts(j) > Cts(j, r2) Then GoTo Nope:
        Next j
        GoTo GotOne:
Nope:
        r2 = (r2 Mod t) + 1
    Next i


' Couldn't find a team that can hold this group
    Tries = Tries + 1
    If Tries > 1000 Then
        MsgBox "Can't find a set of teams in 1000 tries.  Check to see that you don't have any unworkable groups, " & _
               "such as a group containing 4 male A players, when the maximum number of male A players on any " & _
               "team is 3."
        Exit Sub
    End If
    GoTo BigLoop:
    
GotOne:
    If LCase(Left(Grp, 4)) = "team" Then        ' We've assigned a "Team" group
        GTCtr(r2) = 1
        Teams(0, r2) = Grp
    End If


' Update counters for this team
    For j = 1 To 8
        Cts(j, r2) = Cts(j, r2) - WkCts(j)
    Next j
    
' Find all players in this group (or the fake individual group), and add them to the
' team we just found.  Then remove those players from the Players array so we don't
' assign them to anyone else.
    For i = NumPlayers To 1 Step -1                     ' Find the player names to assign
        If LCase(Players(i, 4)) = LCase(Grp) Then
            TeamSize(r2) = TeamSize(r2) + 1             ' # of players on the team
            k = TeamSize(r2)
            Teams(k, r2) = Players(i, 1)                ' Place the name
            For j = 1 To 4                              ' Now replace that name with the
                Players(i, j) = Players(NumPlayers, j)  '  last name on the name list so
            Next j                                      '  we don't consider it again
            NumPlayers = NumPlayers - 1
            If LCase(Left(Grp, 4)) = "team" Then NumTGroups = NumTGroups - 1
            If Grp <> Chr$(1) Then NumGroups = NumGroups - 1
        End If
    Next i
    
    If NumPlayers > 0 Then GoTo SmallLoop:          ' Find the next player to place
    
' Placed everyone, print them out
    Range(Cells(1, t + 12), Cells(UBound(Teams) + 1, 2 * t + 11)).Value = Teams
    
' Check to see if any teams don't have a name.  If not, they'll be randomly assigned
' one from the list below.  You can put your own names here, or even just skip the
' whole next section (up to the Exit Sub which must remain), and teams without a name
' will just be blank in row 1 on the output.
    TeamNames = Array("Maniacs", "Bombers", "Scorpions", "Koalas", "Sharks", "Rednecks", _
                      "Stretchers", "Raptors", "Ninjas", "Racers")
    j = UBound(TeamNames)
    For i = 1 To t
        If Cells(1, t + 11 + i) = "" Then
            k = Int(Rnd() * j)
            Cells(1, t + 11 + i) = TeamNames(k)
            TeamNames(k) = TeamNames(j)
            j = j - 1
        End If
    Next i
    Exit Sub
Oops:
    MsgBox "Some error occured.  You may need to rerun 'GetCounts' before running this again."


End Sub

I cleaned the code up some, and added many comments. You should be able to see what's going on. Let me know how it works.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Generate list of players for four school teams
2
Replies
16
Views
859
imcl75
I
S
  • Question Question
analyze and export based on multiple criteria
Replies
0
Views
526
slash32487
S
Dan Wilson
  • Solved
Help with the Random Function
Replies
7
Views
505
Fluff
Fluff
N
  • Question Question
Selecting multiple random names from a list, with additional criteria
2
Replies
10
Views
404
Peter_SSs
Peter_SSs
starl
  • Question Question
Generate unique random team groupings multiple times
Replies
5
Views
1K
starl
starl

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top