Tennis set combinations

[Raigmore]

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

I just need to list all the possible combinations of which player (1 o
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
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

 

[Sandy Mann]

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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

Or without having to array enter it:

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

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bernie Deitrick]

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

 

[Sandy Mann]

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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bernie Deitrick]

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

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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Dave Peterson]

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.aspx?scid=kb;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]

Thank you Dave, I will try it out.

--
Regards

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bernie Deitrick]

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

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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

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

--
Regards

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

Hi Bernie,

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

--
Thank you again

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Raigmore]

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]

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-
then a tie break happens.

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

Is there a way I can just amend the macro.

Thank

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

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