"Snake Draft" Formula

C

cloyd1980

New Member
Joined
Feb 11, 2013
Messages
19
Hey all,

I am trying to set up "draft boards" for a youth sports organization and we run the drafts in standard snake order (see example below). I have to do this multiple times per year for for varying number of teams (sometimes as few as 8, sometimes as many as 22) and it gets tedious manually typing all those numbers. Does anyone know how to write a formula that would reproduce the below example and could be easily modified to fit however many teams I have?


[TABLE="width: 1854"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]55[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
How do you want this information displayed?
Do you really just want numbers going back and forth across columns like you have shown with no team names or anything like that?
Or is there a header row that list the team names?

In addition to allowing for variable number of teams, I am guessing that you will also want a variable number of rounds, right?
 
Upvote 0
Assuming my assumptions are correct, here is code that assumes that you have one header row, and the numbering begins on the second row:
Code: 
Sub MyMacro()

    Dim teams As Long
    Dim rounds As Long
    Dim c As Long
    Dim r As Long
    Dim i As Long
    Dim h As Long
    
    Application.ScreenUpdating = False
    
'   Indicate number of header rows above first row of data
    h = 1
    
'   Prompt for number of teams
    teams = InputBox("How many teams are there?")
    
'   Prompt for number of rounds
    rounds = InputBox("How many rounds are there?")
   
'   Populate data, starting in row 2
    For r = 1 To rounds
'       Populate columns, starting in column A
        If (r Mod 2) = 1 Then
'           Go forward
            For c = 1 To teams
                i = i + 1
                Cells(r + h, c) = i
            Next c
        Else
'           Go backward
            For c = teams To 1 Step -1
                 i = i + 1
                Cells(r + h, c) = i
            Next c
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
If you have a different number of header rows, than can easily be changed in the code by changing the setting of the initial value of "h".
 
Upvote 0
Joe4 said:
How do you want this information displayed?
Do you really just want numbers going back and forth across columns like you have shown with no team names or anything like that?
Or is there a header row that list the team names?

In addition to allowing for variable number of teams, I am guessing that you will also want a variable number of rounds, right?
Click to expand...

I actually have 5 header rows ( Division name, head coach, asst coach, team name, sponsor name), so the number actually starts in B6. And yes, I have varying number of rounds based on number of kids on each team (typically between 10-12 though).
 
Upvote 0
OK. This should do what you want.
Code: 
Sub MyMacro()

    Dim teams As Long
    Dim rounds As Long
    Dim c As Long
    Dim r As Long
    Dim i As Long
    Dim hr As Long
    Dim hc As Long
    
    Application.ScreenUpdating = False
    
'   Indicate number of header rows and columns
    hr = 5
    hc = 1
    
'   Prompt for number of teams
    teams = InputBox("How many teams are there?")
    
'   Prompt for number of rounds
    rounds = InputBox("How many rounds are there?")
   
'   Populate data, starting in row 2
    For r = 1 To rounds
'       Populate columns, starting in column A
        If (r Mod 2) = 1 Then
'           Go forward
            For c = 1 To teams
                i = i + 1
                Cells(r + hr, c + hc) = i
            Next c
        Else
'           Go backward
            For c = teams To 1 Step -1
                 i = i + 1
                Cells(r + hr, c + hc) = i
            Next c
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Joe4 said:
OK. This should do what you want.
Code: 
Sub MyMacro()

    Dim teams As Long
    Dim rounds As Long
    Dim c As Long
    Dim r As Long
    Dim i As Long
    Dim hr As Long
    Dim hc As Long
    
    Application.ScreenUpdating = False
    
'   Indicate number of header rows and columns
    hr = 5
    hc = 1
    
'   Prompt for number of teams
    teams = InputBox("How many teams are there?")
    
'   Prompt for number of rounds
    rounds = InputBox("How many rounds are there?")
   
'   Populate data, starting in row 2
    For r = 1 To rounds
'       Populate columns, starting in column A
        If (r Mod 2) = 1 Then
'           Go forward
            For c = 1 To teams
                i = i + 1
                Cells(r + hr, c + hc) = i
            Next c
        Else
'           Go backward
            For c = teams To 1 Step -1
                 i = i + 1
                Cells(r + hr, c + hc) = i
            Next c
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
Click to expand...

That works perfect! Is it easy to add a "Supplemental Round" after round 4? For example, if I have 187 players then 7 teams will get a "Supplemental" 11th pick and we always hold this round after the 4th round. So in a 18 team league with 10 rounds and 7 supplemental picks the supplemental round would be picks 73-79 and the next round would start at 80 and continue snaking to 187. Sorry, I forgot to mention that in my first explanation.
 
Upvote 0
Since only some of the teams get a supplemental pick, and I assume that is somewhat arbitrary, I don't know that we can put the supplmental picks into the grid automatically. But we can skip those numbers and continue on.
Try this:
Code: 
Sub MyMacro()

    Dim teams As Long
    Dim rounds As Long
    Dim c As Long
    Dim r As Long
    Dim i As Long
    Dim hr As Long
    Dim hc As Long
    Dim s
    Dim sr As Long
    Dim sp As Long
    
    Application.ScreenUpdating = False
    
'   Indicate number of header rows and columns
    hr = 5
    hc = 1
    
'   Prompt for number of teams
    teams = InputBox("How many teams are there?")
    
'   Prompt for number of rounds
    rounds = InputBox("How many rounds are there?")
    
'   Ask for supplemental round
    s = MsgBox("Is there a supplemental round?", vbYesNo)
    
    If s = vbYes Then
'       Prompt for what round supplemental picks come after
        sr = InputBox("After which round do the supplemental picks come?")
'       Prompt for number of supplemental picks
        sp = InputBox("How many supplemental picks are there?")
    End If
   
'   Populate data, starting in row 2
    For r = 1 To rounds
'       Populate columns, starting in column A
        If (r Mod 2) = 1 Then
'           Go forward
            For c = 1 To teams
                i = i + 1
                Cells(r + hr, c + hc) = i
            Next c
        Else
'           Go backward
            For c = teams To 1 Step -1
                 i = i + 1
                Cells(r + hr, c + hc) = i
            Next c
        End If
'       Check for supplemental round, and add supplemental picks
        If s = vbYes Then
            If r = sr Then i = i + sp
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Joe4 said:
Since only some of the teams get a supplemental pick, and I assume that is somewhat arbitrary, I don't know that we can put the supplmental picks into the grid automatically. But we can skip those numbers and continue on.
Try this:
Code: 
Sub MyMacro()

    Dim teams As Long
    Dim rounds As Long
    Dim c As Long
    Dim r As Long
    Dim i As Long
    Dim hr As Long
    Dim hc As Long
    Dim s
    Dim sr As Long
    Dim sp As Long
    
    Application.ScreenUpdating = False
    
'   Indicate number of header rows and columns
    hr = 5
    hc = 1
    
'   Prompt for number of teams
    teams = InputBox("How many teams are there?")
    
'   Prompt for number of rounds
    rounds = InputBox("How many rounds are there?")
    
'   Ask for supplemental round
    s = MsgBox("Is there a supplemental round?", vbYesNo)
    
    If s = vbYes Then
'       Prompt for what round supplemental picks come after
        sr = InputBox("After which round do the supplemental picks come?")
'       Prompt for number of supplemental picks
        sp = InputBox("How many supplemental picks are there?")
    End If
   
'   Populate data, starting in row 2
    For r = 1 To rounds
'       Populate columns, starting in column A
        If (r Mod 2) = 1 Then
'           Go forward
            For c = 1 To teams
                i = i + 1
                Cells(r + hr, c + hc) = i
            Next c
        Else
'           Go backward
            For c = teams To 1 Step -1
                 i = i + 1
                Cells(r + hr, c + hc) = i
            Next c
        End If
'       Check for supplemental round, and add supplemental picks
        If s = vbYes Then
            If r = sr Then i = i + sp
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
Click to expand...
This is absolutely perfect!! Thank you so much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Extract Team Pairs from Table
Replies
2
Views
223
Steve 1962
S
Z
  • Question Question
Xlookup with multiple conditions
Replies
5
Views
259
Cubist
Cubist
S
  • Solved
Need Green and Red color to arrow but data are in formula
2
Replies
13
Views
197
MARK858
MARK858
welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
101
welshgasman
welshgasman
Deepakchoudhary
  • Solved
RANK based on selling multiple items in different criteria (Excel 2016)
Replies
3
Views
163
hagia_sofia
hagia_sofia

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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