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


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

Listing combinations in excel
 

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" wrote in
message ...

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

Listing combinations in excel
 

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

Listing combinations in excel
 

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" wrote in message
...
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" wrote
in message ...

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

Listing combinations in excel
 

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

Listing combinations in excel
 

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" wrote in
message ...

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

Listing combinations in excel
 

In article ,
Raigmore 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(H40,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