setting up number combinations
 

how can i set it up so that i can have the program write out the combinations
between two numbers so that there are never the same number twice
and then add a third number to the equation

i need this for league teams
if i have for example 6 teams and i want them to play every other team
before repeating a team
then i want to add games into this combination so not only dont the play the
same team twice they go through all the games before repeating them again

setting up number combinations
 

Below is code I wrote for somebody else earlier this week.

Modify these two line as necessary
'you can make instring as long or short as you want. You can put string in
the array instead of number.
'for 6 players
InStrings = Array(1, 2, 3, 4, 5, 6)
'for 10 players
InStrings = Array(1, 2, 3, 4, 5, 6,7,8,9,10)

modify for size of team or number of teams playing together
ComboLen = 2


Public InStrings
Public combo
Public RowCount
Public ComboLen
Sub combinations()

InStrings = Array(1, 2, 3, 4, 5, 6)
Length = UBound(InStrings) + 1

Level = 1
RowCount = 1
ComboLen = 2
ReDim combo(ComboLen)
Position = 0

Call recursive(Level, Position)
End Sub
Sub recursive(ByVal Level As Integer, ByVal Position As Integer)

Length = UBound(InStrings) + 1

For i = Position To (Length - 1)

'for combinations check if item already entered
found = False
For j = 0 To (Level - 2)
'combo is a count of the combinations,not the actual data
'123
'124
'125
'234
'235
'245
'345
'data is actually in InStrings
If combo(j) = i Then
found = True
Exit For
End If
Next j

If found = False Then
combo(Level - 1) = i
If Level = ComboLen Then
For j = 0 To (ComboLen - 1)
If j = 0 Then
ComboString = InStrings(combo(j))
Else
ComboString = ComboString & "," & InStrings(combo(j))
End If
Next j
Sheets("Sheet2").Range("A" & RowCount) = ComboString
RowCount = RowCount + 1
Else
Call recursive(Level + 1, i)
End If
End If
Next i
End Sub


"lifeguardernie" wrote:

how can i set it up so that i can have the program write out the combinations
between two numbers so that there are never the same number twice
and then add a third number to the equation

i need this for league teams
if i have for example 6 teams and i want them to play every other team
before repeating a team
then i want to add games into this combination so not only dont the play the
same team twice they go through all the games before repeating them again

setting up number combinations
 

how do i actually apply this code in excel
i'm not used to them

"Joel" wrote:

Below is code I wrote for somebody else earlier this week.

Modify these two line as necessary
'you can make instring as long or short as you want. You can put string in
the array instead of number.
'for 6 players
InStrings = Array(1, 2, 3, 4, 5, 6)
'for 10 players
InStrings = Array(1, 2, 3, 4, 5, 6,7,8,9,10)

modify for size of team or number of teams playing together
ComboLen = 2


Public InStrings
Public combo
Public RowCount
Public ComboLen
Sub combinations()

InStrings = Array(1, 2, 3, 4, 5, 6)
Length = UBound(InStrings) + 1

Level = 1
RowCount = 1
ComboLen = 2
ReDim combo(ComboLen)
Position = 0

Call recursive(Level, Position)
End Sub
Sub recursive(ByVal Level As Integer, ByVal Position As Integer)

Length = UBound(InStrings) + 1

For i = Position To (Length - 1)

'for combinations check if item already entered
found = False
For j = 0 To (Level - 2)
'combo is a count of the combinations,not the actual data
'123
'124
'125
'234
'235
'245
'345
'data is actually in InStrings
If combo(j) = i Then
found = True
Exit For
End If
Next j

If found = False Then
combo(Level - 1) = i
If Level = ComboLen Then
For j = 0 To (ComboLen - 1)
If j = 0 Then
ComboString = InStrings(combo(j))
Else
ComboString = ComboString & "," & InStrings(combo(j))
End If
Next j
Sheets("Sheet2").Range("A" & RowCount) = ComboString
RowCount = RowCount + 1
Else
Call recursive(Level + 1, i)
End If
End If
Next i
End Sub


"lifeguardernie" wrote:

how can i set it up so that i can have the program write out the combinations
between two numbers so that there are never the same number twice
and then add a third number to the equation

i need this for league teams
if i have for example 6 teams and i want them to play every other team
before repeating a team
then i want to add games into this combination so not only dont the play the
same team twice they go through all the games before repeating them again

setting up number combinations
 

For 6 teams it is just easier to manually go through the combination. try this

game 1: 1,2 3,4 5,6
game 2: 1,3 2,5 4,6
game 3: 1,4 2,6 3,5
game 4: 1,5 2,4 3,6
game 5: 1,6 2,3 4,5


"lifeguardernie" wrote:

how do i actually apply this code in excel
i'm not used to them

"Joel" wrote:

Below is code I wrote for somebody else earlier this week.

Modify these two line as necessary
'you can make instring as long or short as you want. You can put string in
the array instead of number.
'for 6 players
InStrings = Array(1, 2, 3, 4, 5, 6)
'for 10 players
InStrings = Array(1, 2, 3, 4, 5, 6,7,8,9,10)

modify for size of team or number of teams playing together
ComboLen = 2


Public InStrings
Public combo
Public RowCount
Public ComboLen
Sub combinations()

InStrings = Array(1, 2, 3, 4, 5, 6)
Length = UBound(InStrings) + 1

Level = 1
RowCount = 1
ComboLen = 2
ReDim combo(ComboLen)
Position = 0

Call recursive(Level, Position)
End Sub
Sub recursive(ByVal Level As Integer, ByVal Position As Integer)

Length = UBound(InStrings) + 1

For i = Position To (Length - 1)

'for combinations check if item already entered
found = False
For j = 0 To (Level - 2)
'combo is a count of the combinations,not the actual data
'123
'124
'125
'234
'235
'245
'345
'data is actually in InStrings
If combo(j) = i Then
found = True
Exit For
End If
Next j

If found = False Then
combo(Level - 1) = i
If Level = ComboLen Then
For j = 0 To (ComboLen - 1)
If j = 0 Then
ComboString = InStrings(combo(j))
Else
ComboString = ComboString & "," & InStrings(combo(j))
End If
Next j
Sheets("Sheet2").Range("A" & RowCount) = ComboString
RowCount = RowCount + 1
Else
Call recursive(Level + 1, i)
End If
End If
Next i
End Sub


"lifeguardernie" wrote:

how can i set it up so that i can have the program write out the combinations
between two numbers so that there are never the same number twice
and then add a third number to the equation

i need this for league teams
if i have for example 6 teams and i want them to play every other team
before repeating a team
then i want to add games into this combination so not only dont the play the
same team twice they go through all the games before repeating them again

setting up number combinations
 

i did go through the combinations manually but i also need it tied to a
separate game
game 1: 1,2 a 3,4 b 5,6 c
game 2: 1,3 2,5 4,6
game 3: 1,4 2,6 3,5
game 4: 1,5 2,4 3,6
game 5: 1,6 2,3 4,5

but while i could manually work out games and teams for 4 and 8 teams i cant
work out 6 teams


"Joel" wrote:

For 6 teams it is just easier to manually go through the combination. try this

game 1: 1,2 3,4 5,6
game 2: 1,3 2,5 4,6
game 3: 1,4 2,6 3,5
game 4: 1,5 2,4 3,6
game 5: 1,6 2,3 4,5


"lifeguardernie" wrote:

how do i actually apply this code in excel
i'm not used to them

"Joel" wrote:

Below is code I wrote for somebody else earlier this week.

Modify these two line as necessary
'you can make instring as long or short as you want. You can put string in
the array instead of number.
'for 6 players
InStrings = Array(1, 2, 3, 4, 5, 6)
'for 10 players
InStrings = Array(1, 2, 3, 4, 5, 6,7,8,9,10)

modify for size of team or number of teams playing together
ComboLen = 2


Public InStrings
Public combo
Public RowCount
Public ComboLen
Sub combinations()

InStrings = Array(1, 2, 3, 4, 5, 6)
Length = UBound(InStrings) + 1

Level = 1
RowCount = 1
ComboLen = 2
ReDim combo(ComboLen)
Position = 0

Call recursive(Level, Position)
End Sub
Sub recursive(ByVal Level As Integer, ByVal Position As Integer)

Length = UBound(InStrings) + 1

For i = Position To (Length - 1)

'for combinations check if item already entered
found = False
For j = 0 To (Level - 2)
'combo is a count of the combinations,not the actual data
'123
'124
'125
'234
'235
'245
'345
'data is actually in InStrings
If combo(j) = i Then
found = True
Exit For
End If
Next j

If found = False Then
combo(Level - 1) = i
If Level = ComboLen Then
For j = 0 To (ComboLen - 1)
If j = 0 Then
ComboString = InStrings(combo(j))
Else
ComboString = ComboString & "," & InStrings(combo(j))
End If
Next j
Sheets("Sheet2").Range("A" & RowCount) = ComboString
RowCount = RowCount + 1
Else
Call recursive(Level + 1, i)
End If
End If
Next i
End Sub


"lifeguardernie" wrote:

how can i set it up so that i can have the program write out the combinations
between two numbers so that there are never the same number twice
and then add a third number to the equation

i need this for league teams
if i have for example 6 teams and i want them to play every other team
before repeating a team
then i want to add games into this combination so not only dont the play the
same team twice they go through all the games before repeating them again

setting up number combinations
 

Don'y you just want to rotate the columns?

game 1: 1,2 3,4 5,6
game 2: 1,3 2,5 4,6
game 3: 1,4 2,6 3,5
game 4: 1,5 2,4 3,6
game 5: 1,6 2,3 4,5

game 6: 5,6 1,2 3,4
game 7: 4,6 1,3 2,5
game 8: 3,5 1,4 2,6
game 9: 3,6 1,5 2,4
game 10: 4,5 1,6 2,3

game 11: 3,4 5,6 1,2
game 12: 2,5 4,6 1,3
game 13: 2,6 3,5 1,4
game 14: 2,4 3,6 1,5
game 15: 2,3 4,5 1,6



"lifeguardernie" wrote:

i did go through the combinations manually but i also need it tied to a
separate game
game 1: 1,2 a 3,4 b 5,6 c
game 2: 1,3 2,5 4,6
game 3: 1,4 2,6 3,5
game 4: 1,5 2,4 3,6
game 5: 1,6 2,3 4,5

but while i could manually work out games and teams for 4 and 8 teams i cant
work out 6 teams


"Joel" wrote:

For 6 teams it is just easier to manually go through the combination. try this

game 1: 1,2 3,4 5,6
game 2: 1,3 2,5 4,6
game 3: 1,4 2,6 3,5
game 4: 1,5 2,4 3,6
game 5: 1,6 2,3 4,5


"lifeguardernie" wrote:

how do i actually apply this code in excel
i'm not used to them

"Joel" wrote:

Below is code I wrote for somebody else earlier this week.

Modify these two line as necessary
'you can make instring as long or short as you want. You can put string in
the array instead of number.
'for 6 players
InStrings = Array(1, 2, 3, 4, 5, 6)
'for 10 players
InStrings = Array(1, 2, 3, 4, 5, 6,7,8,9,10)

modify for size of team or number of teams playing together
ComboLen = 2


Public InStrings
Public combo
Public RowCount
Public ComboLen
Sub combinations()

InStrings = Array(1, 2, 3, 4, 5, 6)
Length = UBound(InStrings) + 1

Level = 1
RowCount = 1
ComboLen = 2
ReDim combo(ComboLen)
Position = 0

Call recursive(Level, Position)
End Sub
Sub recursive(ByVal Level As Integer, ByVal Position As Integer)

Length = UBound(InStrings) + 1

For i = Position To (Length - 1)

'for combinations check if item already entered
found = False
For j = 0 To (Level - 2)
'combo is a count of the combinations,not the actual data
'123
'124
'125
'234
'235
'245
'345
'data is actually in InStrings
If combo(j) = i Then
found = True
Exit For
End If
Next j

If found = False Then
combo(Level - 1) = i
If Level = ComboLen Then
For j = 0 To (ComboLen - 1)
If j = 0 Then
ComboString = InStrings(combo(j))
Else
ComboString = ComboString & "," & InStrings(combo(j))
End If
Next j
Sheets("Sheet2").Range("A" & RowCount) = ComboString
RowCount = RowCount + 1
Else
Call recursive(Level + 1, i)
End If
End If
Next i
End Sub


"lifeguardernie" wrote:

how can i set it up so that i can have the program write out the combinations
between two numbers so that there are never the same number twice
and then add a third number to the equation

i need this for league teams
if i have for example 6 teams and i want them to play every other team
before repeating a team
then i want to add games into this combination so not only dont the play the
same team twice they go through all the games before repeating them again

setting up number combinations
 

no what i want to do is within these five games have all the teams play a
different game so if by game one they play game a, by game two b, so nodody
does the same game twice

teams-game-teams-game-teams-game
game 1: 1,2 a 3,4 5,6
game 2: 1,3 b 2,5 4,6
game 3: 1,4 c 2,6 3,5
game 4: 1,5 d 2,4 3,6
game 5: 1,6 e 2,3 4,5

but manually it dosen't work out so i'm trying to figure out how to set it
up so the computer does all the work of making it fit

"Joel" wrote:

Don'y you just want to rotate the columns?

game 1: 1,2 3,4 5,6
game 2: 1,3 2,5 4,6
game 3: 1,4 2,6 3,5
game 4: 1,5 2,4 3,6
game 5: 1,6 2,3 4,5

game 6: 5,6 1,2 3,4
game 7: 4,6 1,3 2,5
game 8: 3,5 1,4 2,6
game 9: 3,6 1,5 2,4
game 10: 4,5 1,6 2,3

game 11: 3,4 5,6 1,2
game 12: 2,5 4,6 1,3
game 13: 2,6 3,5 1,4
game 14: 2,4 3,6 1,5
game 15: 2,3 4,5 1,6



"lifeguardernie" wrote:

i did go through the combinations manually but i also need it tied to a
separate game
game 1: 1,2 a 3,4 b 5,6 c
game 2: 1,3 2,5 4,6
game 3: 1,4 2,6 3,5
game 4: 1,5 2,4 3,6
game 5: 1,6 2,3 4,5

but while i could manually work out games and teams for 4 and 8 teams i cant
work out 6 teams


"Joel" wrote:

For 6 teams it is just easier to manually go through the combination. try this

game 1: 1,2 3,4 5,6
game 2: 1,3 2,5 4,6
game 3: 1,4 2,6 3,5
game 4: 1,5 2,4 3,6
game 5: 1,6 2,3 4,5


"lifeguardernie" wrote:

how do i actually apply this code in excel
i'm not used to them

"Joel" wrote:

Below is code I wrote for somebody else earlier this week.

Modify these two line as necessary
'you can make instring as long or short as you want. You can put string in
the array instead of number.
'for 6 players
InStrings = Array(1, 2, 3, 4, 5, 6)
'for 10 players
InStrings = Array(1, 2, 3, 4, 5, 6,7,8,9,10)

modify for size of team or number of teams playing together
ComboLen = 2


Public InStrings
Public combo
Public RowCount
Public ComboLen
Sub combinations()

InStrings = Array(1, 2, 3, 4, 5, 6)
Length = UBound(InStrings) + 1

Level = 1
RowCount = 1
ComboLen = 2
ReDim combo(ComboLen)
Position = 0

Call recursive(Level, Position)
End Sub
Sub recursive(ByVal Level As Integer, ByVal Position As Integer)

Length = UBound(InStrings) + 1

For i = Position To (Length - 1)

'for combinations check if item already entered
found = False
For j = 0 To (Level - 2)
'combo is a count of the combinations,not the actual data
'123
'124
'125
'234
'235
'245
'345
'data is actually in InStrings
If combo(j) = i Then
found = True
Exit For
End If
Next j

If found = False Then
combo(Level - 1) = i
If Level = ComboLen Then
For j = 0 To (ComboLen - 1)
If j = 0 Then
ComboString = InStrings(combo(j))
Else
ComboString = ComboString & "," & InStrings(combo(j))
End If
Next j
Sheets("Sheet2").Range("A" & RowCount) = ComboString
RowCount = RowCount + 1
Else
Call recursive(Level + 1, i)
End If
End If
Next i
End Sub


"lifeguardernie" wrote:

how can i set it up so that i can have the program write out the combinations
between two numbers so that there are never the same number twice
and then add a third number to the equation

i need this for league teams
if i have for example 6 teams and i want them to play every other team
before repeating a team
then i want to add games into this combination so not only dont the play the
same team twice they go through all the games before repeating them again