Tennis set combinations
 

I would be grateful for help with listing all the possible game
combinations within a set of tennis.

I just need to list all the possible combinations of which player (1 or
2) wins each game.

So for 6-0 to a player it is obviously 1,1,1,1,1.

6-1 can be 1,1,1,1,1,2,1 etc

Is there a way excel can list all the possible combinations?

Even if I just have a combination of all the ways 1 and 2 can win in a
12 game period.

I guess it is just 2*2*2*2*2 etc but how do I list it in 1s and 2s?


Hope this is clear!


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071
View this thread: http://www.excelforum.com/showthread...hreadid=497492

Tennis set combinations
 

Raigmore,

Well, as you said there is only one combination for a 6-0 win but for 6-1
there will be 6 combinations and for 6-2 there are 21 combinations. This
fits in with the formula: =SUM( COMBIN( {list of total games in Sets} ,
{ No of wins for played No 2} ) +1 ie

=SUM(COMBIN({7,8}-1,{1,2}))+1

array entered with Ctrl + Shift + Enter

if that is not just a coincidence then the array formula:

=SUM(COMBIN({7,8,9,10,11}-1,{1,2,3,4,5}))+1

will give us 462 possible combinations for a win.

Of course my maths is no where near good enough to prove that this is true,
perhaps one of the clever people around here can chip in and tell us.


--
HTH

Sandy

with @tiscali.co.uk

"Raigmore" wrote in
message ...

I would be grateful for help with listing all the possible game
combinations within a set of tennis.

I just need to list all the possible combinations of which player (1 or
2) wins each game.

So for 6-0 to a player it is obviously 1,1,1,1,1.

6-1 can be 1,1,1,1,1,2,1 etc

Is there a way excel can list all the possible combinations?

Even if I just have a combination of all the ways 1 and 2 can win in a
12 game period.

I guess it is just 2*2*2*2*2 etc but how do I list it in 1s and 2s?


Hope this is clear!


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile:
http://www.excelforum.com/member.php...o&userid=30071
View this thread: http://www.excelforum.com/showthread...hreadid=497492

Tennis set combinations
 

Or without having to array enter it:

=SUMPRODUCT(COMBIN({7,8,9,10,11}-1,{1,2,3,4,5}))+1

--
HTH

Sandy

with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Raigmore,

Well, as you said there is only one combination for a 6-0 win but for 6-1
there will be 6 combinations and for 6-2 there are 21 combinations. This
fits in with the formula: =SUM( COMBIN( {list of total games in Sets} ,
{ No of wins for played No 2} ) +1 ie

=SUM(COMBIN({7,8}-1,{1,2}))+1

array entered with Ctrl + Shift + Enter

if that is not just a coincidence then the array formula:

=SUM(COMBIN({7,8,9,10,11}-1,{1,2,3,4,5}))+1

will give us 462 possible combinations for a win.

Of course my maths is no where near good enough to prove that this is
true,
perhaps one of the clever people around here can chip in and tell us.


--
HTH

Sandy

with @tiscali.co.uk

"Raigmore" wrote
in message ...

I would be grateful for help with listing all the possible game
combinations within a set of tennis.

I just need to list all the possible combinations of which player (1 or
2) wins each game.

So for 6-0 to a player it is obviously 1,1,1,1,1.

6-1 can be 1,1,1,1,1,2,1 etc

Is there a way excel can list all the possible combinations?

Even if I just have a combination of all the ways 1 and 2 can win in a
12 game period.

I guess it is just 2*2*2*2*2 etc but how do I list it in 1s and 2s?


Hope this is clear!


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile:
http://www.excelforum.com/member.php...o&userid=30071
View this thread:
http://www.excelforum.com/showthread...hreadid=497492

Tennis set combinations
 

Raigmore,

Well, we can brute force it. Run the macro below, with a blank sheet active.

This assumes that you aren't using the "Must win by 2 games" rule - otherwise, there's an infinite
number of combinations.

With that assumption, the set ends after a maximum of 11 games, with a winner guaranteed. There are
924 possible combinations.

HTH,
Bernie
MS Excel MVP

Sub BruteForce()
For i = 1 To 11
Cells(1, i).Value = "Game " & i
Next i
Cells(1, 12).Value = "Winner"

RCount = 2
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2

Result = a & " " & b & " " & c & " " & d & _
" " & e & " " & f & " " & g & " " & h & _
" " & i & " " & j & " " & k

splResult = Split(Result, " ")

Count1 = 0
Count2 = 0

For m = LBound(splResult) To UBound(splResult)
If CInt(splResult(m)) = 1 Then
Count1 = Count1 + 1
Else
Count2 = Count2 + 1
End If
Next m

If Count1 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 1 Then Count1 = Count1 - 1
If Count1 = 0 Then GoTo Written1
Next n
Written1:
Cells(RCount, 12).Value = 1
RCount = RCount + 1
End If

If Count2 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 2 Then Count2 = Count2 - 1
If Count2 = 0 Then GoTo Written2
Next n
Written2:
Cells(RCount, 12).Value = 2
RCount = RCount + 1
End If

Next k
Next j
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a

Cells.EntireColumn.AutoFit
End Sub



"Raigmore" wrote in message
...

I would be grateful for help with listing all the possible game
combinations within a set of tennis.

I just need to list all the possible combinations of which player (1 or
2) wins each game.

So for 6-0 to a player it is obviously 1,1,1,1,1.

6-1 can be 1,1,1,1,1,2,1 etc

Is there a way excel can list all the possible combinations?

Even if I just have a combination of all the ways 1 and 2 can win in a
12 game period.

I guess it is just 2*2*2*2*2 etc but how do I list it in 1s and 2s?


Hope this is clear!


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071
View this thread: http://www.excelforum.com/showthread...hreadid=497492

Tennis set combinations
 

Bernie,

In XL97 your code chokes on:

splResult = Split(Result, " ")

with *Split* hilighted and saying "Sub or function not defined". Asking
Help about *Split* brings up the page about Splitting the window. May I ask
what *Split* does in later versions of XL?


--
Regards

Sandy

with @tiscali.co.uk


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Raigmore,

Well, we can brute force it. Run the macro below, with a blank sheet
active.

This assumes that you aren't using the "Must win by 2 games" rule -
otherwise, there's an infinite number of combinations.

With that assumption, the set ends after a maximum of 11 games, with a
winner guaranteed. There are 924 possible combinations.

HTH,
Bernie
MS Excel MVP

Sub BruteForce()
For i = 1 To 11
Cells(1, i).Value = "Game " & i
Next i
Cells(1, 12).Value = "Winner"

RCount = 2
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2

Result = a & " " & b & " " & c & " " & d & _
" " & e & " " & f & " " & g & " " & h & _
" " & i & " " & j & " " & k

splResult = Split(Result, " ")

Count1 = 0
Count2 = 0

For m = LBound(splResult) To UBound(splResult)
If CInt(splResult(m)) = 1 Then
Count1 = Count1 + 1
Else
Count2 = Count2 + 1
End If
Next m

If Count1 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 1 Then Count1 = Count1 - 1
If Count1 = 0 Then GoTo Written1
Next n
Written1:
Cells(RCount, 12).Value = 1
RCount = RCount + 1
End If

If Count2 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 2 Then Count2 = Count2 - 1
If Count2 = 0 Then GoTo Written2
Next n
Written2:
Cells(RCount, 12).Value = 2
RCount = RCount + 1
End If

Next k
Next j
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a

Cells.EntireColumn.AutoFit
End Sub



"Raigmore" wrote
in message ...

I would be grateful for help with listing all the possible game
combinations within a set of tennis.

I just need to list all the possible combinations of which player (1 or
2) wins each game.

So for 6-0 to a player it is obviously 1,1,1,1,1.

6-1 can be 1,1,1,1,1,2,1 etc

Is there a way excel can list all the possible combinations?

Even if I just have a combination of all the ways 1 and 2 can win in a
12 game period.

I guess it is just 2*2*2*2*2 etc but how do I list it in 1s and 2s?


Hope this is clear!


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile:
http://www.excelforum.com/member.php...o&userid=30071
View this thread:
http://www.excelforum.com/showthread...hreadid=497492

Tennis set combinations
 

Sandy,

In later versions, split returns an array of values from the string that is passed it, broken apart
by, in this case, spaces. So, with an input of

This is the input string.

It will output an array

This
is
the
input
string.

The way around not having it in XL97 is to step through the string looking for spaces, writing the
values out to an array. There is certainly code in the archives showing how to do this. If you
_really_ need it, I can re-write this to work in <yuck XL97.

HTH,
Bernie
MS Excel MVP


"Sandy Mann" wrote in message
...
Bernie,

In XL97 your code chokes on:

splResult = Split(Result, " ")

with *Split* hilighted and saying "Sub or function not defined". Asking Help about *Split*
brings up the page about Splitting the window. May I ask what *Split* does in later versions of
XL?


--
Regards

Sandy

with @tiscali.co.uk


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Raigmore,

Well, we can brute force it. Run the macro below, with a blank sheet active.

This assumes that you aren't using the "Must win by 2 games" rule - otherwise, there's an
infinite number of combinations.

With that assumption, the set ends after a maximum of 11 games, with a winner guaranteed. There
are 924 possible combinations.

HTH,
Bernie
MS Excel MVP

Sub BruteForce()
For i = 1 To 11
Cells(1, i).Value = "Game " & i
Next i
Cells(1, 12).Value = "Winner"

RCount = 2
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2

Result = a & " " & b & " " & c & " " & d & _
" " & e & " " & f & " " & g & " " & h & _
" " & i & " " & j & " " & k

splResult = Split(Result, " ")

Count1 = 0
Count2 = 0

For m = LBound(splResult) To UBound(splResult)
If CInt(splResult(m)) = 1 Then
Count1 = Count1 + 1
Else
Count2 = Count2 + 1
End If
Next m

If Count1 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 1 Then Count1 = Count1 - 1
If Count1 = 0 Then GoTo Written1
Next n
Written1:
Cells(RCount, 12).Value = 1
RCount = RCount + 1
End If

If Count2 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 2 Then Count2 = Count2 - 1
If Count2 = 0 Then GoTo Written2
Next n
Written2:
Cells(RCount, 12).Value = 2
RCount = RCount + 1
End If

Next k
Next j
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a

Cells.EntireColumn.AutoFit
End Sub



"Raigmore" wrote in message
...

I would be grateful for help with listing all the possible game
combinations within a set of tennis.

I just need to list all the possible combinations of which player (1 or
2) wins each game.

So for 6-0 to a player it is obviously 1,1,1,1,1.

6-1 can be 1,1,1,1,1,2,1 etc

Is there a way excel can list all the possible combinations?

Even if I just have a combination of all the ways 1 and 2 can win in a
12 game period.

I guess it is just 2*2*2*2*2 etc but how do I list it in 1s and 2s?


Hope this is clear!


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071
View this thread: http://www.excelforum.com/showthread...hreadid=497492

Tennis set combinations
 

Public Function ReadUntil(ByRef sIn As String, _
sDelim As String, Optional bCompare As Long _
= vbBinaryCompare) As String
Dim nPos As String
nPos = InStr(1, sIn, sDelim, bCompare)
If nPos 0 Then
ReadUntil = Left(sIn, nPos - 1)
sIn = Mid(sIn, nPos + Len(sDelim))
End If
End Function
Public Function Split97(ByVal sIn As String, Optional sDelim As _
String, Optional nLimit As Long = -1, Optional bCompare As _
Long = vbBinaryCompare) As Variant
Dim sRead As String, sOut() As String, nC As Integer
If sDelim = "" Then
Split97 = sIn
End If
sRead = ReadUntil(sIn, sDelim, bCompare)
Do
ReDim Preserve sOut(nC)
sOut(nC) = sRead
nC = nC + 1
If nLimit < -1 And nC = nLimit Then Exit Do
sRead = ReadUntil(sIn, sDelim)
Loop While sRead < ""
ReDim Preserve sOut(nC)
sOut(nC) = sIn
Split97 = sOut
End Function

The readuntil and split97 functions were stolen from the MSKB:
http://support.microsoft.com/default...b;en-us;188007
HOWTO: Simulate Visual Basic 6.0 String Functions in VB5

=======
If the original string isn't too long, you could try Tom Ogilvy's split97:

Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

Sandy Mann wrote:

Bernie,

In XL97 your code chokes on:

splResult = Split(Result, " ")

with *Split* hilighted and saying "Sub or function not defined". Asking
Help about *Split* brings up the page about Splitting the window. May I ask
what *Split* does in later versions of XL?

--
Regards

Sandy

with @tiscali.co.uk

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Raigmore,

Well, we can brute force it. Run the macro below, with a blank sheet
active.

This assumes that you aren't using the "Must win by 2 games" rule -
otherwise, there's an infinite number of combinations.

With that assumption, the set ends after a maximum of 11 games, with a
winner guaranteed. There are 924 possible combinations.

HTH,
Bernie
MS Excel MVP

Sub BruteForce()
For i = 1 To 11
Cells(1, i).Value = "Game " & i
Next i
Cells(1, 12).Value = "Winner"

RCount = 2
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2

Result = a & " " & b & " " & c & " " & d & _
" " & e & " " & f & " " & g & " " & h & _
" " & i & " " & j & " " & k

splResult = Split(Result, " ")

Count1 = 0
Count2 = 0

For m = LBound(splResult) To UBound(splResult)
If CInt(splResult(m)) = 1 Then
Count1 = Count1 + 1
Else
Count2 = Count2 + 1
End If
Next m

If Count1 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 1 Then Count1 = Count1 - 1
If Count1 = 0 Then GoTo Written1
Next n
Written1:
Cells(RCount, 12).Value = 1
RCount = RCount + 1
End If

If Count2 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 2 Then Count2 = Count2 - 1
If Count2 = 0 Then GoTo Written2
Next n
Written2:
Cells(RCount, 12).Value = 2
RCount = RCount + 1
End If

Next k
Next j
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a

Cells.EntireColumn.AutoFit
End Sub



"Raigmore" wrote
in message ...

I would be grateful for help with listing all the possible game
combinations within a set of tennis.

I just need to list all the possible combinations of which player (1 or
2) wins each game.

So for 6-0 to a player it is obviously 1,1,1,1,1.

6-1 can be 1,1,1,1,1,2,1 etc

Is there a way excel can list all the possible combinations?

Even if I just have a combination of all the ways 1 and 2 can win in a
12 game period.

I guess it is just 2*2*2*2*2 etc but how do I list it in 1s and 2s?


Hope this is clear!


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile:
http://www.excelforum.com/member.php...o&userid=30071
View this thread:
http://www.excelforum.com/showthread...hreadid=497492




--

Dave Peterson

Tennis set combinations
 

Thank you Dave, I will try it out.

--
Regards

Sandy

with @tiscali.co.uk

"Dave Peterson" wrote in message
...
Public Function ReadUntil(ByRef sIn As String, _
sDelim As String, Optional bCompare As Long _
= vbBinaryCompare) As String
Dim nPos As String
nPos = InStr(1, sIn, sDelim, bCompare)
If nPos 0 Then
ReadUntil = Left(sIn, nPos - 1)
sIn = Mid(sIn, nPos + Len(sDelim))
End If
End Function
Public Function Split97(ByVal sIn As String, Optional sDelim As _
String, Optional nLimit As Long = -1, Optional bCompare As _
Long = vbBinaryCompare) As Variant
Dim sRead As String, sOut() As String, nC As Integer
If sDelim = "" Then
Split97 = sIn
End If
sRead = ReadUntil(sIn, sDelim, bCompare)
Do
ReDim Preserve sOut(nC)
sOut(nC) = sRead
nC = nC + 1
If nLimit < -1 And nC = nLimit Then Exit Do
sRead = ReadUntil(sIn, sDelim)
Loop While sRead < ""
ReDim Preserve sOut(nC)
sOut(nC) = sIn
Split97 = sOut
End Function

The readuntil and split97 functions were stolen from the MSKB:
http://support.microsoft.com/default...b;en-us;188007
HOWTO: Simulate Visual Basic 6.0 String Functions in VB5

=======
If the original string isn't too long, you could try Tom Ogilvy's split97:

Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

Sandy Mann wrote:

Bernie,

In XL97 your code chokes on:

splResult = Split(Result, " ")

with *Split* hilighted and saying "Sub or function not defined". Asking
Help about *Split* brings up the page about Splitting the window. May I
ask
what *Split* does in later versions of XL?

--
Regards

Sandy

with @tiscali.co.uk

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Raigmore,

Well, we can brute force it. Run the macro below, with a blank sheet
active.

This assumes that you aren't using the "Must win by 2 games" rule -
otherwise, there's an infinite number of combinations.

With that assumption, the set ends after a maximum of 11 games, with a
winner guaranteed. There are 924 possible combinations.

HTH,
Bernie
MS Excel MVP

Sub BruteForce()
For i = 1 To 11
Cells(1, i).Value = "Game " & i
Next i
Cells(1, 12).Value = "Winner"

RCount = 2
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2

Result = a & " " & b & " " & c & " " & d & _
" " & e & " " & f & " " & g & " " & h & _
" " & i & " " & j & " " & k

splResult = Split(Result, " ")

Count1 = 0
Count2 = 0

For m = LBound(splResult) To UBound(splResult)
If CInt(splResult(m)) = 1 Then
Count1 = Count1 + 1
Else
Count2 = Count2 + 1
End If
Next m

If Count1 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 1 Then Count1 = Count1 - 1
If Count1 = 0 Then GoTo Written1
Next n
Written1:
Cells(RCount, 12).Value = 1
RCount = RCount + 1
End If

If Count2 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 2 Then Count2 = Count2 - 1
If Count2 = 0 Then GoTo Written2
Next n
Written2:
Cells(RCount, 12).Value = 2
RCount = RCount + 1
End If

Next k
Next j
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a

Cells.EntireColumn.AutoFit
End Sub



"Raigmore"
wrote
in message
...

I would be grateful for help with listing all the possible game
combinations within a set of tennis.

I just need to list all the possible combinations of which player (1
or
2) wins each game.

So for 6-0 to a player it is obviously 1,1,1,1,1.

6-1 can be 1,1,1,1,1,2,1 etc

Is there a way excel can list all the possible combinations?

Even if I just have a combination of all the ways 1 and 2 can win in a
12 game period.

I guess it is just 2*2*2*2*2 etc but how do I list it in 1s and 2s?


Hope this is clear!


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile:
http://www.excelforum.com/member.php...o&userid=30071
View this thread:
http://www.excelforum.com/showthread...hreadid=497492




--

Dave Peterson

Tennis set combinations
 

Sandy,

Below is a macro rewritten without Split, which is probably a little bit faster. Give it a try.

HTH,
Bernie
MS Excel MVP

Sub BruteForce2()
For i = 1 To 11
Cells(1, i).Value = "Game " & i
Next i
Cells(1, 12).Value = "Winner"

RCount = 2
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2

Result = a & " " & b & " " & c & " " & d & _
" " & e & " " & f & " " & g & " " & h & _
" " & i & " " & j & " " & k

Count1 = Len(Result) - Len(Application.Substitute(Result, "1", ""))
Count2 = Len(Result) - Len(Application.Substitute(Result, "2", ""))

If Count1 = 6 Then
For n = 1 To 21 Step 2
Cells(RCount, (n + 1) / 2).Value = Mid(Result, n, 1)
If CInt(Mid(Result, n, 1)) = 1 Then Count1 = Count1 - 1
If Count1 = 0 Then GoTo Written1
Next n
Written1:
Cells(RCount, 12).Value = 1
RCount = RCount + 1
End If

If Count2 = 6 Then
For n = 1 To 21 Step 2
Cells(RCount, (n + 1) / 2).Value = Mid(Result, n, 1)
If CInt(Mid(Result, n, 1)) = 2 Then Count2 = Count2 - 1
If Count2 = 0 Then GoTo Written2
Next n
Written2:
Cells(RCount, 12).Value = 2
RCount = RCount + 1
End If

Next k
Next j
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a

Cells.EntireColumn.AutoFit
End Sub


"Sandy Mann" wrote in message
...
Bernie,

In XL97 your code chokes on:

splResult = Split(Result, " ")

with *Split* hilighted and saying "Sub or function not defined". Asking Help about *Split*
brings up the page about Splitting the window. May I ask what *Split* does in later versions of
XL?


--
Regards

Sandy

with @tiscali.co.uk


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Raigmore,

Well, we can brute force it. Run the macro below, with a blank sheet active.

This assumes that you aren't using the "Must win by 2 games" rule - otherwise, there's an
infinite number of combinations.

With that assumption, the set ends after a maximum of 11 games, with a winner guaranteed. There
are 924 possible combinations.

HTH,
Bernie
MS Excel MVP

Sub BruteForce()
For i = 1 To 11
Cells(1, i).Value = "Game " & i
Next i
Cells(1, 12).Value = "Winner"

RCount = 2
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2

Result = a & " " & b & " " & c & " " & d & _
" " & e & " " & f & " " & g & " " & h & _
" " & i & " " & j & " " & k

splResult = Split(Result, " ")

Count1 = 0
Count2 = 0

For m = LBound(splResult) To UBound(splResult)
If CInt(splResult(m)) = 1 Then
Count1 = Count1 + 1
Else
Count2 = Count2 + 1
End If
Next m

If Count1 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 1 Then Count1 = Count1 - 1
If Count1 = 0 Then GoTo Written1
Next n
Written1:
Cells(RCount, 12).Value = 1
RCount = RCount + 1
End If

If Count2 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 2 Then Count2 = Count2 - 1
If Count2 = 0 Then GoTo Written2
Next n
Written2:
Cells(RCount, 12).Value = 2
RCount = RCount + 1
End If

Next k
Next j
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a

Cells.EntireColumn.AutoFit
End Sub



"Raigmore" wrote in message
...

I would be grateful for help with listing all the possible game
combinations within a set of tennis.

I just need to list all the possible combinations of which player (1 or
2) wins each game.

So for 6-0 to a player it is obviously 1,1,1,1,1.

6-1 can be 1,1,1,1,1,2,1 etc

Is there a way excel can list all the possible combinations?

Even if I just have a combination of all the ways 1 and 2 can win in a
12 game period.

I guess it is just 2*2*2*2*2 etc but how do I list it in 1s and 2s?


Hope this is clear!


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071
View this thread: http://www.excelforum.com/showthread...hreadid=497492

Tennis set combinations
 

Thank you Bernie but Dave posted the code from the MSKB that I have yet to
have a look at.

--
Regards

Sandy

with @tiscali.co.uk

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Sandy,

In later versions, split returns an array of values from the string that
is passed it, broken apart by, in this case, spaces. So, with an input of

This is the input string.

It will output an array

This
is
the
input
string.

The way around not having it in XL97 is to step through the string looking
for spaces, writing the values out to an array. There is certainly code
in the archives showing how to do this. If you _really_ need it, I can
re-write this to work in <yuck XL97.

HTH,
Bernie
MS Excel MVP


"Sandy Mann" wrote in message
...
Bernie,

In XL97 your code chokes on:

splResult = Split(Result, " ")

with *Split* hilighted and saying "Sub or function not defined". Asking
Help about *Split* brings up the page about Splitting the window. May I
ask what *Split* does in later versions of XL?


--
Regards

Sandy

with @tiscali.co.uk


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Raigmore,

Well, we can brute force it. Run the macro below, with a blank sheet
active.

This assumes that you aren't using the "Must win by 2 games" rule -
otherwise, there's an infinite number of combinations.

With that assumption, the set ends after a maximum of 11 games, with a
winner guaranteed. There are 924 possible combinations.

HTH,
Bernie
MS Excel MVP

Sub BruteForce()
For i = 1 To 11
Cells(1, i).Value = "Game " & i
Next i
Cells(1, 12).Value = "Winner"

RCount = 2
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2

Result = a & " " & b & " " & c & " " & d & _
" " & e & " " & f & " " & g & " " & h & _
" " & i & " " & j & " " & k

splResult = Split(Result, " ")

Count1 = 0
Count2 = 0

For m = LBound(splResult) To UBound(splResult)
If CInt(splResult(m)) = 1 Then
Count1 = Count1 + 1
Else
Count2 = Count2 + 1
End If
Next m

If Count1 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 1 Then Count1 = Count1 - 1
If Count1 = 0 Then GoTo Written1
Next n
Written1:
Cells(RCount, 12).Value = 1
RCount = RCount + 1
End If

If Count2 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 2 Then Count2 = Count2 - 1
If Count2 = 0 Then GoTo Written2
Next n
Written2:
Cells(RCount, 12).Value = 2
RCount = RCount + 1
End If

Next k
Next j
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a

Cells.EntireColumn.AutoFit
End Sub



"Raigmore" wrote
in message ...

I would be grateful for help with listing all the possible game
combinations within a set of tennis.

I just need to list all the possible combinations of which player (1 or
2) wins each game.

So for 6-0 to a player it is obviously 1,1,1,1,1.

6-1 can be 1,1,1,1,1,2,1 etc

Is there a way excel can list all the possible combinations?

Even if I just have a combination of all the ways 1 and 2 can win in a
12 game period.

I guess it is just 2*2*2*2*2 etc but how do I list it in 1s and 2s?


Hope this is clear!


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile:
http://www.excelforum.com/member.php...o&userid=30071
View this thread:
http://www.excelforum.com/showthread...hreadid=497492

Tennis set combinations
 

Thank you very very much Bernie. Both you and Dave have got to a lot of
trouble for me and I am not even the OP!

Running your code it returns 462 wins for 1 and 462 wind for 2. The formula
that I posted:

=SUMPRODUCT(COMBIN({7,8,9,10,11}-1,{1,2,3,4,5}))+1

also returns 462 - must have been a lucky guess <g

--
Thank you again

Sandy

with @tiscali.co.uk

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Sandy,

Below is a macro rewritten without Split, which is probably a little bit
faster. Give it a try.

HTH,
Bernie
MS Excel MVP

Sub BruteForce2()
For i = 1 To 11
Cells(1, i).Value = "Game " & i
Next i
Cells(1, 12).Value = "Winner"

RCount = 2
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2

Result = a & " " & b & " " & c & " " & d & _
" " & e & " " & f & " " & g & " " & h & _
" " & i & " " & j & " " & k

Count1 = Len(Result) - Len(Application.Substitute(Result, "1", ""))
Count2 = Len(Result) - Len(Application.Substitute(Result, "2", ""))

If Count1 = 6 Then
For n = 1 To 21 Step 2
Cells(RCount, (n + 1) / 2).Value = Mid(Result, n, 1)
If CInt(Mid(Result, n, 1)) = 1 Then Count1 = Count1 - 1
If Count1 = 0 Then GoTo Written1
Next n
Written1:
Cells(RCount, 12).Value = 1
RCount = RCount + 1
End If

If Count2 = 6 Then
For n = 1 To 21 Step 2
Cells(RCount, (n + 1) / 2).Value = Mid(Result, n, 1)
If CInt(Mid(Result, n, 1)) = 2 Then Count2 = Count2 - 1
If Count2 = 0 Then GoTo Written2
Next n
Written2:
Cells(RCount, 12).Value = 2
RCount = RCount + 1
End If

Next k
Next j
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a

Cells.EntireColumn.AutoFit
End Sub


"Sandy Mann" wrote in message
...
Bernie,

In XL97 your code chokes on:

splResult = Split(Result, " ")

with *Split* hilighted and saying "Sub or function not defined". Asking
Help about *Split* brings up the page about Splitting the window. May I
ask what *Split* does in later versions of XL?


--
Regards

Sandy

with @tiscali.co.uk


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Raigmore,

Well, we can brute force it. Run the macro below, with a blank sheet
active.

This assumes that you aren't using the "Must win by 2 games" rule -
otherwise, there's an infinite number of combinations.

With that assumption, the set ends after a maximum of 11 games, with a
winner guaranteed. There are 924 possible combinations.

HTH,
Bernie
MS Excel MVP

Sub BruteForce()
For i = 1 To 11
Cells(1, i).Value = "Game " & i
Next i
Cells(1, 12).Value = "Winner"

RCount = 2
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2

Result = a & " " & b & " " & c & " " & d & _
" " & e & " " & f & " " & g & " " & h & _
" " & i & " " & j & " " & k

splResult = Split(Result, " ")

Count1 = 0
Count2 = 0

For m = LBound(splResult) To UBound(splResult)
If CInt(splResult(m)) = 1 Then
Count1 = Count1 + 1
Else
Count2 = Count2 + 1
End If
Next m

If Count1 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 1 Then Count1 = Count1 - 1
If Count1 = 0 Then GoTo Written1
Next n
Written1:
Cells(RCount, 12).Value = 1
RCount = RCount + 1
End If

If Count2 = 6 Then
For n = LBound(splResult) To UBound(splResult)
Cells(RCount, n + 1).Value = splResult(n)
If splResult(n) = 2 Then Count2 = Count2 - 1
If Count2 = 0 Then GoTo Written2
Next n
Written2:
Cells(RCount, 12).Value = 2
RCount = RCount + 1
End If

Next k
Next j
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a

Cells.EntireColumn.AutoFit
End Sub



"Raigmore" wrote
in message ...

I would be grateful for help with listing all the possible game
combinations within a set of tennis.

I just need to list all the possible combinations of which player (1 or
2) wins each game.

So for 6-0 to a player it is obviously 1,1,1,1,1.

6-1 can be 1,1,1,1,1,2,1 etc

Is there a way excel can list all the possible combinations?

Even if I just have a combination of all the ways 1 and 2 can win in a
12 game period.

I guess it is just 2*2*2*2*2 etc but how do I list it in 1s and 2s?


Hope this is clear!


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile:
http://www.excelforum.com/member.php...o&userid=30071
View this thread:
http://www.excelforum.com/showthread...hreadid=497492

Tennis set combinations
 

Hi Bernie,

Not only may it be faster but I fould it much easier to understand!

--
Thank you again

Sandy

with @tiscali.co.uk

Tennis set combinations
 

Thanks guys, that is exactly what I needed and you have saved me a load
of time.

And I think I might be able to do some of these things myself now as
well.

Much appreciated.


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071
View this thread: http://www.excelforum.com/showthread...hreadid=497492

Tennis set combinations
 

I need to change one thing but have tried and failed!

I need it to run to 12 games, as it can be 7-5 or 6-6, if it is 6-6
then a tie break happens.

I don't need to worry about the tie break but need to know the 7-5
combinations and the 6-6 ones? At 6-6 there would be a third state of
'draw" rather than anyone winning.

Is there a way I can just amend the macro.

Thanks


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071
View this thread: http://www.excelforum.com/showthread...hreadid=497492

Tennis set combinations
 

Raigmore,

Try this version.

HTH,
Bernie
MS Excel MVP

Sub BruteForce3()
For i = 1 To 12
Cells(1, i).Value = "Game " & i
Next i
Cells(1, 13).Value = "Winner"

RCount = 2
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2

Result = a & " " & b & " " & c & " " & d & _
" " & e & " " & f & " " & g & " " & h & _
" " & i & " " & j & " " & k

Count1 = Len(Result) - Len(Application.Substitute(Result, "1", ""))
Count2 = Len(Result) - Len(Application.Substitute(Result, "2", ""))

DataPt1 = False
DataPt2 = False

If Count1 = 6 Then
For n = 1 To 21 Step 2
Cells(RCount, (n + 1) / 2).Value = Mid(Result, n, 1)
If CInt(Mid(Result, n, 1)) = 1 Then Count1 = Count1 - 1
If Count1 = 0 Then GoTo Written1
Next n
Written1:
Cells(RCount, 13).Value = 1
RCount = RCount + 1
DataPt1 = True
End If

If Count2 = 6 Then
For n = 1 To 21 Step 2
Cells(RCount, (n + 1) / 2).Value = Mid(Result, n, 1)
If CInt(Mid(Result, n, 1)) = 2 Then Count2 = Count2 - 1
If Count2 = 0 Then GoTo Written2
Next n
Written2:
Cells(RCount, 13).Value = 2
RCount = RCount + 1
DataPt2 = True
End If

If DataPt1 Or DataPt2 Then
If Cells(RCount - 1, 11) < "" Then
Cells(RCount - 1, 1).EntireRow.Copy Cells(RCount, 1).EntireRow
Cells(RCount - 1, 12).Value = IIf(DataPt1, 1, 2)
Cells(RCount, 12).Value = IIf(DataPt1, 2, 1)
Cells(RCount, 13).Value = "Tie"
RCount = RCount + 1
End If
End If

Next k
Next j
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a

Cells.EntireColumn.AutoFit
Cells.HorizontalAlignment = xlCenter
Cells(1, 14).Value = "Score" & Chr(10) & "1 v 2"
Cells(2, 14).FormulaR1C1 = _
"= ""'"" & COUNTIF(RC[-13]:RC[-2],1) & "" - "" & COUNTIF(RC[-13]:RC[-2],2)"
Cells(2, 14).AutoFill Destination:=Range(Cells(2, 14), Cells(RCount, 14))
Cells(1, 14).EntireColumn.Value = Cells(1, 14).EntireColumn.Value
End Sub



"Raigmore" wrote in message
...

I need to change one thing but have tried and failed!

I need it to run to 12 games, as it can be 7-5 or 6-6, if it is 6-6
then a tie break happens.

I don't need to worry about the tie break but need to know the 7-5
combinations and the 6-6 ones? At 6-6 there would be a third state of
'draw" rather than anyone winning.

Is there a way I can just amend the macro.

Thanks


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071
View this thread: http://www.excelforum.com/showthread...hreadid=497492

Tennis set combinations
 

Ooops. The line:

Cells(2, 14).AutoFill Destination:=Range(Cells(2, 14), Cells(RCount, 14))

Should be:

Cells(2, 14).AutoFill Destination:=Range(Cells(2, 14), Cells(RCount-1, 14))

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Raigmore,

Try this version.

HTH,
Bernie
MS Excel MVP

Sub BruteForce3()
For i = 1 To 12
Cells(1, i).Value = "Game " & i
Next i
Cells(1, 13).Value = "Winner"

RCount = 2
For a = 1 To 2
For b = 1 To 2
For c = 1 To 2
For d = 1 To 2
For e = 1 To 2
For f = 1 To 2
For g = 1 To 2
For h = 1 To 2
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2

Result = a & " " & b & " " & c & " " & d & _
" " & e & " " & f & " " & g & " " & h & _
" " & i & " " & j & " " & k

Count1 = Len(Result) - Len(Application.Substitute(Result, "1", ""))
Count2 = Len(Result) - Len(Application.Substitute(Result, "2", ""))

DataPt1 = False
DataPt2 = False

If Count1 = 6 Then
For n = 1 To 21 Step 2
Cells(RCount, (n + 1) / 2).Value = Mid(Result, n, 1)
If CInt(Mid(Result, n, 1)) = 1 Then Count1 = Count1 - 1
If Count1 = 0 Then GoTo Written1
Next n
Written1:
Cells(RCount, 13).Value = 1
RCount = RCount + 1
DataPt1 = True
End If

If Count2 = 6 Then
For n = 1 To 21 Step 2
Cells(RCount, (n + 1) / 2).Value = Mid(Result, n, 1)
If CInt(Mid(Result, n, 1)) = 2 Then Count2 = Count2 - 1
If Count2 = 0 Then GoTo Written2
Next n
Written2:
Cells(RCount, 13).Value = 2
RCount = RCount + 1
DataPt2 = True
End If

If DataPt1 Or DataPt2 Then
If Cells(RCount - 1, 11) < "" Then
Cells(RCount - 1, 1).EntireRow.Copy Cells(RCount, 1).EntireRow
Cells(RCount - 1, 12).Value = IIf(DataPt1, 1, 2)
Cells(RCount, 12).Value = IIf(DataPt1, 2, 1)
Cells(RCount, 13).Value = "Tie"
RCount = RCount + 1
End If
End If

Next k
Next j
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a

Cells.EntireColumn.AutoFit
Cells.HorizontalAlignment = xlCenter
Cells(1, 14).Value = "Score" & Chr(10) & "1 v 2"
Cells(2, 14).FormulaR1C1 = _
"= ""'"" & COUNTIF(RC[-13]:RC[-2],1) & "" - "" & COUNTIF(RC[-13]:RC[-2],2)"
Cells(2, 14).AutoFill Destination:=Range(Cells(2, 14), Cells(RCount, 14))
Cells(1, 14).EntireColumn.Value = Cells(1, 14).EntireColumn.Value
End Sub



"Raigmore" wrote in message
...

I need to change one thing but have tried and failed!

I need it to run to 12 games, as it can be 7-5 or 6-6, if it is 6-6
then a tie break happens.

I don't need to worry about the tie break but need to know the 7-5
combinations and the 6-6 ones? At 6-6 there would be a third state of
'draw" rather than anyone winning.

Is there a way I can just amend the macro.

Thanks


--
Raigmore
------------------------------------------------------------------------
Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071
View this thread: http://www.excelforum.com/showthread...hreadid=497492