Tennis set combinations

[Raigmore]

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!

[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
[email protected]
[email protected] with @tiscali.co.uk

"Raigmore" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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
[email protected]
[email protected] with @tiscali.co.uk


"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> [email protected] with @tiscali.co.uk
>
> "Raigmore" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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



"Raigmore" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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
[email protected]
[email protected] with @tiscali.co.uk


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>>
>> 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
>>
>
>

[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


"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>
>>> 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]

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
> [email protected]
> [email protected] with @tiscali.co.uk
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote
> > in message news:[email protected]...
> >>
> >> 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

[Sandy Mann]

Thank you Dave, I will try it out.

--
Regards

Sandy
[email protected]
[email protected] with @tiscali.co.uk

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]>
>> > wrote
>> > in message
>> > news:[email protected]...
>> >>
>> >> 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

[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


"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...
> 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
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>
>>> 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
>>>
>>
>>
>
>

[Sandy Mann]

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

--
Regards

Sandy
[email protected]
[email protected] with @tiscali.co.uk

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote
>>> in message news:[email protected]...
>>>>
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>

[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
[email protected]
[email protected] with @tiscali.co.uk

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote
>>> in message news:[email protected]...
>>>>
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>

[Sandy Mann]

Hi Bernie,

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

--
Thank you again

Sandy
[email protected]
[email protected] 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-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

[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



"Raigmore" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>
>> 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
>>
>
>