Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for number combinations. | Excel Worksheet Functions | |||
Formula for number combinations. | Excel Worksheet Functions | |||
number combinations. | Excel Worksheet Functions | |||
All Possible Number Combinations | Excel Discussion (Misc queries) | |||
Number combinations | Excel Worksheet Functions |