Listing combinations in excel

I have a series of 7 events, matches, each with three possible outcomes, 0, 1 and 3.

I want to list in excel all the possible combinations that could result from these matches.

So it is just 3 to the power of 7.

I would like them listed as 0,0,0,0,0,0,0 then 0,0,0,0,0,0,1 etc etc.

I have started trying to build a macro to do it but failed miserably.

If anyone can offer any pointers it would be much appreciated.

Thanks

Try:


Dim vals(7), cary As Integer
Sub combinationlist()
Dim i As Integer
Dim j As Long
Dim s As String
' gsnu
For i = 1 To 7
vals(i) = 0
Next

Cells(1, 1).Value = "0,0,0,0,0,0,0"

For j = 2 To 3 ^ 7
For i = 1 To 7
If i = 1 Then
cary = 1
End If
Call bump(i)
Next
s = ""
For i = 1 To 7
s = vals(i) & "," & s
Next
Cells(j, 1).Value = Left(s, 13)
Next
End Sub
Sub bump(i)
If cary = 0 Then
Exit Sub
End If
If vals(i) = 0 Then
vals(i) = 1
cary = 0
ElseIf vals(i) = 1 Then
vals(i) = 3
cary = 0
Else
vals(i) = 0
cary = 1
End If
End Sub

--
Gary''s Student


"Raigmore" wrote:

>
> I have a series of 7 events, matches, each with three possible outcomes,
> 0, 1 and 3.
>
> I want to list in excel all the possible combinations that could result
> from these matches.
>
> So it is just 3 to the power of 7.
>
> I would like them listed as 0,0,0,0,0,0,0 then 0,0,0,0,0,0,1 etc etc.
>
> I have started trying to build a macro to do it but failed miserably.
>
> If anyone can offer any pointers it would be much appreciated.
>
> Thanks
>
>
> --
> Raigmore
> ------------------------------------------------------------------------
> Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071
> View this thread: http://www.excelforum.com/showthread...hreadid=518960
>
>

So you have 2187 combinations, expressed in a number system base 3 (although
you don't want 2's but 3's, but that's just presentation, doesn't alter the
approach)
I used Ron Rosenfeld's function to convert your decimal numbers (base 10) to
base 3 numbers, converted them to text with 7 digits and replaced the 2's
with 3s.

Here's the formula, starting in A1 and to be filled down to A2187:

=SUBSTITUTE(TEXT(BaseConvert(ROW()-1,10,3),"0000000"),"2","3")

and here's Ron's code:

' ==================================================
Function BaseConvert(Num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String


'by Ron Rosenfeld
'Handles from base 2 to base 62 by differentiating small and capital letters


Dim LDI As Integer 'Leading Digit Index
Dim i As Integer, j As Integer
Dim Temp, Temp2
Dim Digits()
Dim r
Dim DecSep As String


DecSep = Application.International(xlDecimalSeparator)


On Error GoTo HANDLER


If FromBase > 62 Or ToBase > 62 _
Or FromBase < 2 Or ToBase < 2 Then
BaseConvert = "Base out of range"
Exit Function
End If


If InStr(1, Num, "E") And FromBase = 10 Then
Num = CDec(Num)
End If


'Convert to Base 10
LDI = InStr(1, Num, DecSep) - 2
If LDI = -2 Then LDI = Len(Num) - 1


j = LDI


Temp = Replace(Num, DecSep, "")
For i = 1 To Len(Temp)
Temp2 = Mid(Temp, i, 1)
Select Case Temp2
Case "A" To "Z"
Temp2 = Asc(Temp2) - 55
Case "a" To "z"
Temp2 = Asc(Temp2) - 61
End Select
If Temp2 >= FromBase Then
BaseConvert = "Invalid Digit"
Exit Function
End If
r = CDec(r + Temp2 * FromBase ^ j)
j = j - 1
Next i


If r <> 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase)))
If r < 1 Then LDI = 0


ReDim Digits(LDI)


For i = UBound(Digits) To 0 Step -1
Digits(i) = Format(Fix(r / ToBase ^ i))
r = CDbl(r - Digits(i) * ToBase ^ i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i


Temp = StrReverse(Join(Digits, "")) 'Integer portion
ReDim Digits(DecPlace)


If r <> 0 Then
Digits(0) = DecSep
For i = 1 To UBound(Digits)
Digits(i) = Format(Fix(r / ToBase ^ -i))
r = CDec(r - Digits(i) * ToBase ^ -i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
End If


BaseConvert = Temp & Join(Digits, "")


Exit Function
HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _
"Number being converted: " & Num)


End Function
' ==================================================


--
Kind regards,

Niek Otten


"Raigmore" <[email protected]> wrote in
message news:[email protected]...
>
> I have a series of 7 events, matches, each with three possible outcomes,
> 0, 1 and 3.
>
> I want to list in excel all the possible combinations that could result
> from these matches.
>
> So it is just 3 to the power of 7.
>
> I would like them listed as 0,0,0,0,0,0,0 then 0,0,0,0,0,0,1 etc etc.
>
> I have started trying to build a macro to do it but failed miserably.
>
> If anyone can offer any pointers it would be much appreciated.
>
> Thanks
>
>
> --
> Raigmore
> ------------------------------------------------------------------------
> Raigmore's Profile:
> http://www.excelforum.com/member.php...o&userid=30071
> View this thread: http://www.excelforum.com/showthread...hreadid=518960
>

If you need the digits in separate cells, put this in B1:

=MID($A1,COLUMN(A1),1)

Copy Right to H1 and then copy down.

--
Kind regards,

Niek Otten

"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
> So you have 2187 combinations, expressed in a number system base 3
> (although you don't want 2's but 3's, but that's just presentation,
> doesn't alter the approach)
> I used Ron Rosenfeld's function to convert your decimal numbers (base 10)
> to base 3 numbers, converted them to text with 7 digits and replaced the
> 2's with 3s.
>
> Here's the formula, starting in A1 and to be filled down to A2187:
>
> =SUBSTITUTE(TEXT(BaseConvert(ROW()-1,10,3),"0000000"),"2","3")
>
> and here's Ron's code:
>
> ' ==================================================
> Function BaseConvert(Num, FromBase As Integer, _
> ToBase As Integer, Optional DecPlace As Long) _
> As String
>
>
> 'by Ron Rosenfeld
> 'Handles from base 2 to base 62 by differentiating small and capital
> letters
>
>
> Dim LDI As Integer 'Leading Digit Index
> Dim i As Integer, j As Integer
> Dim Temp, Temp2
> Dim Digits()
> Dim r
> Dim DecSep As String
>
>
> DecSep = Application.International(xlDecimalSeparator)
>
>
> On Error GoTo HANDLER
>
>
> If FromBase > 62 Or ToBase > 62 _
> Or FromBase < 2 Or ToBase < 2 Then
> BaseConvert = "Base out of range"
> Exit Function
> End If
>
>
> If InStr(1, Num, "E") And FromBase = 10 Then
> Num = CDec(Num)
> End If
>
>
> 'Convert to Base 10
> LDI = InStr(1, Num, DecSep) - 2
> If LDI = -2 Then LDI = Len(Num) - 1
>
>
> j = LDI
>
>
> Temp = Replace(Num, DecSep, "")
> For i = 1 To Len(Temp)
> Temp2 = Mid(Temp, i, 1)
> Select Case Temp2
> Case "A" To "Z"
> Temp2 = Asc(Temp2) - 55
> Case "a" To "z"
> Temp2 = Asc(Temp2) - 61
> End Select
> If Temp2 >= FromBase Then
> BaseConvert = "Invalid Digit"
> Exit Function
> End If
> r = CDec(r + Temp2 * FromBase ^ j)
> j = j - 1
> Next i
>
>
> If r <> 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase)))
> If r < 1 Then LDI = 0
>
>
> ReDim Digits(LDI)
>
>
> For i = UBound(Digits) To 0 Step -1
> Digits(i) = Format(Fix(r / ToBase ^ i))
> r = CDbl(r - Digits(i) * ToBase ^ i)
> Select Case Digits(i)
> Case 10 To 35
> Digits(i) = Chr(Digits(i) + 55)
> Case 36 To 62
> Digits(i) = Chr(Digits(i) + 61)
> End Select
> Next i
>
>
> Temp = StrReverse(Join(Digits, "")) 'Integer portion
> ReDim Digits(DecPlace)
>
>
> If r <> 0 Then
> Digits(0) = DecSep
> For i = 1 To UBound(Digits)
> Digits(i) = Format(Fix(r / ToBase ^ -i))
> r = CDec(r - Digits(i) * ToBase ^ -i)
> Select Case Digits(i)
> Case 10 To 35
> Digits(i) = Chr(Digits(i) + 55)
> Case 36 To 62
> Digits(i) = Chr(Digits(i) + 61)
> End Select
> Next i
> End If
>
>
> BaseConvert = Temp & Join(Digits, "")
>
>
> Exit Function
> HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _
> "Number being converted: " & Num)
>
>
> End Function
> ' ==================================================
>
>
> --
> Kind regards,
>
> Niek Otten
>
>
> "Raigmore" <[email protected]> wrote
> in message news:[email protected]...
>>
>> I have a series of 7 events, matches, each with three possible outcomes,
>> 0, 1 and 3.
>>
>> I want to list in excel all the possible combinations that could result
>> from these matches.
>>
>> So it is just 3 to the power of 7.
>>
>> I would like them listed as 0,0,0,0,0,0,0 then 0,0,0,0,0,0,1 etc etc.
>>
>> I have started trying to build a macro to do it but failed miserably.
>>
>> If anyone can offer any pointers it would be much appreciated.
>>
>> Thanks
>>
>>
>> --
>> Raigmore
>> ------------------------------------------------------------------------
>> Raigmore's Profile:
>> http://www.excelforum.com/member.php...o&userid=30071
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=518960
>>
>
>

Thanks gents, that is great and your help is much appreciated.

It does the job perfectly and I will be able to use the info again for other tasks.

Good to hear that,

Next time, use

=SUBSTITUTE(TEXT(BaseConvert(ROW(A1)-1,10,3),"0000000"),"2","3")

so you don't necessarily have to start in row 1 and so can include headers
if you wish.

--
Kind regards,

Niek Otten

"Raigmore" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks gents, that is great and your help is much appreciated.
>
> It does the job perfectly and I will be able to use the info again for
> other tasks.
>
>
> --
> Raigmore
> ------------------------------------------------------------------------
> Raigmore's Profile:
> http://www.excelforum.com/member.php...o&userid=30071
> View this thread: http://www.excelforum.com/showthread...hreadid=518960
>

In article <[email protected]>,
Raigmore <[email protected]> wrote:

> I want to list in excel all the possible combinations that could result
> from these matches.
>
> So it is just 3 to the power of 7.
>
> I would like them listed as 0,0,0,0,0,0,0 then 0,0,0,0,0,0,1 etc etc.

[ except with 3 instead of 2 for the third possible digit ]

This doesn't need macros; it can be done with functions.

You'll be filling a 7-column by 2187-row block with these. What follows
assumes that the top left corner will be at cell B3, so the bottom right
corner is in cell H2189.

Fill the first row cells, B3 through H3, with zeroes.

Next, in the rightmost column, start at the second row of the block, cell
H4, and put in the formula:

=IF(H3=0,1,IF(H3=3,0,1))

and copy it down, so it fills every cell of that right column. This cycles
0,1,3 all down the column.

Now, put the following formula (which is similar to that one, but with a
prefix) in the top right still-empty cell, G4:

=IF(H4>0,G3,IF(G3=0,1,IF(G3=3,0,1)))

and copy that to all the remaining cells, B4 thru G2189.

If I didn't screw any of that up, you now have what I think you asked for.

--
Randy Hudson