Can you someone help me, what is the excel or access formula that will give
me a numeric box combination.for example i select a random set of numbers 4,
5, 6, 7, 8, 44, 10, 23, 34, 56, 99. I want the result of every possible
outcome to just these numbers from front to back in a 6 column box
combination.

Results

4, 5, 6, 7, 8, 44
5, 7, 44, 23, 34, 56
56, 8, 34, 99, 10, 99

note that any number can repeat within a given row

Warm Regards

GaryJones

"gjones" wrote:
Can you someone help me, what is the excel or access
formula that will give me a numeric box combination.

Can't help you with Access. This is an Excel forum ;-).


for example i select a random set of numbers 4,
5, 6, 7, 8, 44, 10, 23, 34, 56, 99. I want the result of every
possible outcome to just these numbers from front to back
in a 6 column box combination.
[....]
note that any number can repeat within a given row

Note that there are PERMUT(11,6) such combinations -- 332,640. Unless you
are using Excel 2007 or later, that exceeds the number of rows permitted in
a worksheet.

In any case, there is no simple Excel formula to accomplish this. I would
write a VBA UDF that returns an array -- or more likely, a VBA macro. If
you are content to return solutions as text, one per cell, you could fit all
of them within the limits of Excel 2003, filling column after column.


----- original message -----

"gjones" wrote in message
...

Can you someone help me, what is the excel or access formula that will
give
me a numeric box combination.for example i select a random set of numbers
4,
5, 6, 7, 8, 44, 10, 23, 34, 56, 99. I want the result of every possible
outcome to just these numbers from front to back in a 6 column box
combination.

Results

4, 5, 6, 7, 8, 44
5, 7, 44, 23, 34, 56
56, 8, 34, 99, 10, 99

note that any number can repeat within a given row

Warm Regards

GaryJones

While this *may* be possible in XL, do realize that with 11 numbers in a 6
column box, there are 1,771,561 possible outcomes if you allow number
repeats, and
without repeats, there are 332,640 combinations!

(both of those amounts are greater than the standard amount of rows in XL)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"gjones" wrote:


Can you someone help me, what is the excel or access formula that will give
me a numeric box combination.for example i select a random set of numbers 4,
5, 6, 7, 8, 44, 10, 23, 34, 56, 99. I want the result of every possible
outcome to just these numbers from front to back in a 6 column box
combination.

Results

4, 5, 6, 7, 8, 44
5, 7, 44, 23, 34, 56
56, 8, 34, 99, 10, 99

note that any number can repeat within a given row

Warm Regards

GaryJones

"Luke M" wrote:
While this *may* be possible in XL, do realize that with 11 numbers in a 6
column box, there are 1,771,561 possible outcomes if you allow number
repeats

Right -- 11^6. My mistake.


(both of those amounts are greater than the standard amount of rows in XL)

But not greater than the number of cells, over 16.7 million :-) :-).

(Seriously, I agree: this is computationally unsound.)


----- original message -----

"Luke M" wrote in message
...
While this *may* be possible in XL, do realize that with 11 numbers in a 6
column box, there are 1,771,561 possible outcomes if you allow number
repeats, and
without repeats, there are 332,640 combinations!

(both of those amounts are greater than the standard amount of rows in XL)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"gjones" wrote:


Can you someone help me, what is the excel or access formula that will
give
me a numeric box combination.for example i select a random set of numbers
4,
5, 6, 7, 8, 44, 10, 23, 34, 56, 99. I want the result of every possible
outcome to just these numbers from front to back in a 6 column box
combination.

Results

4, 5, 6, 7, 8, 44
5, 7, 44, 23, 34, 56
56, 8, 34, 99, 10, 99

note that any number can repeat within a given row

Warm Regards

GaryJones

thanks but what is the formaula?

Excel or XL
--
Warm Regards

GaryJones


"JoeU2004" wrote:

"Luke M" wrote:
While this *may* be possible in XL, do realize that with 11 numbers in a 6
column box, there are 1,771,561 possible outcomes if you allow number
repeats

Right -- 11^6. My mistake.


(both of those amounts are greater than the standard amount of rows in XL)

But not greater than the number of cells, over 16.7 million :-) :-).

(Seriously, I agree: this is computationally unsound.)


----- original message -----

"Luke M" wrote in message
...
While this *may* be possible in XL, do realize that with 11 numbers in a 6
column box, there are 1,771,561 possible outcomes if you allow number
repeats, and
without repeats, there are 332,640 combinations!

(both of those amounts are greater than the standard amount of rows in XL)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"gjones" wrote:


Can you someone help me, what is the excel or access formula that will
give
me a numeric box combination.for example i select a random set of numbers
4,
5, 6, 7, 8, 44, 10, 23, 34, 56, 99. I want the result of every possible
outcome to just these numbers from front to back in a 6 column box
combination.

Results

4, 5, 6, 7, 8, 44
5, 7, 44, 23, 34, 56
56, 8, 34, 99, 10, 99

note that any number can repeat within a given row

Warm Regards

GaryJones

"gjones" wrote:
thanks but what is the formaula?

I don't think you understand what we've tried to explain to you. But here
it is: not a formula, but a VBA macro.


Option Explicit

Sub doit()
Dim x As Variant
Dim xn As Integer, i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
Dim c As Long, r As Long
Dim oldCalc As Variant

' set rMax and cMax to the max rows and columns to be used
Const rMax As Long = 65536
Const cMax As Long = 256

' set Array arguments to desired numbers
x = Array(4, 5, 6, 7, 8, 44, 10, 23, 34, 56, 99)
xn = UBound(x)

Application.ScreenUpdating = False
oldCalc = Application.Calculation
Application.Calculation = xlCalculationManual

c = 0: r = 1
For i1 = 0 To xn
For i2 = 0 To xn
For i3 = 0 To xn
For i4 = 0 To xn
For i5 = 0 To xn
For i6 = 0 To xn
If c < cMax Then
c = c + 1
ElseIf r < rMax Then
r = r + 1: c = 1
Else
GoTo done
End If
Cells(r, c) = x(i1) & "," & x(i2) & "," & x(i3) & "," & x(i4) & "," &
x(i5) & "," & x(i6)
Next i6: Next i5: Next i4: Next i3: Next i2: Next i1

done:

Range("A1", Cells(r, c)).Columns.AutoFit
Cells(1, 1).Select
Application.Calculation = oldCalc
Application.ScreenUpdating = True
End Sub


----- original message -----

"gjones" wrote in message
...
thanks but what is the formaula?

Excel or XL
--
Warm Regards

GaryJones


"JoeU2004" wrote:

"Luke M" wrote:
While this *may* be possible in XL, do realize that with 11 numbers in
a 6
column box, there are 1,771,561 possible outcomes if you allow number
repeats

Right -- 11^6. My mistake.


(both of those amounts are greater than the standard amount of rows in
XL)

But not greater than the number of cells, over 16.7 million :-) :-).

(Seriously, I agree: this is computationally unsound.)


----- original message -----

"Luke M" wrote in message
...
While this *may* be possible in XL, do realize that with 11 numbers in
a 6
column box, there are 1,771,561 possible outcomes if you allow number
repeats, and
without repeats, there are 332,640 combinations!

(both of those amounts are greater than the standard amount of rows in
XL)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"gjones" wrote:


Can you someone help me, what is the excel or access formula that will
give
me a numeric box combination.for example i select a random set of
numbers
4,
5, 6, 7, 8, 44, 10, 23, 34, 56, 99. I want the result of every
possible
outcome to just these numbers from front to back in a 6 column box
combination.

Results

4, 5, 6, 7, 8, 44
5, 7, 44, 23, 34, 56
56, 8, 34, 99, 10, 99

note that any number can repeat within a given row

Warm Regards

GaryJones

thank you very much now how do i apply this VBA MACRO?

--
Warm Regards

GaryJones


"JoeU2004" wrote:

"gjones" wrote:
thanks but what is the formaula?

I don't think you understand what we've tried to explain to you. But here
it is: not a formula, but a VBA macro.


Option Explicit

Sub doit()
Dim x As Variant
Dim xn As Integer, i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
Dim c As Long, r As Long
Dim oldCalc As Variant

' set rMax and cMax to the max rows and columns to be used
Const rMax As Long = 65536
Const cMax As Long = 256

' set Array arguments to desired numbers
x = Array(4, 5, 6, 7, 8, 44, 10, 23, 34, 56, 99)
xn = UBound(x)

Application.ScreenUpdating = False
oldCalc = Application.Calculation
Application.Calculation = xlCalculationManual

c = 0: r = 1
For i1 = 0 To xn
For i2 = 0 To xn
For i3 = 0 To xn
For i4 = 0 To xn
For i5 = 0 To xn
For i6 = 0 To xn
If c < cMax Then
c = c + 1
ElseIf r < rMax Then
r = r + 1: c = 1
Else
GoTo done
End If
Cells(r, c) = x(i1) & "," & x(i2) & "," & x(i3) & "," & x(i4) & "," &
x(i5) & "," & x(i6)
Next i6: Next i5: Next i4: Next i3: Next i2: Next i1

done:

Range("A1", Cells(r, c)).Columns.AutoFit
Cells(1, 1).Select
Application.Calculation = oldCalc
Application.ScreenUpdating = True
End Sub


----- original message -----

"gjones" wrote in message
...
thanks but what is the formaula?

Excel or XL
--
Warm Regards

GaryJones


"JoeU2004" wrote:

"Luke M" wrote:
While this *may* be possible in XL, do realize that with 11 numbers in
a 6
column box, there are 1,771,561 possible outcomes if you allow number
repeats

Right -- 11^6. My mistake.


(both of those amounts are greater than the standard amount of rows in
XL)

But not greater than the number of cells, over 16.7 million :-) :-).

(Seriously, I agree: this is computationally unsound.)


----- original message -----

"Luke M" wrote in message
...
While this *may* be possible in XL, do realize that with 11 numbers in
a 6
column box, there are 1,771,561 possible outcomes if you allow number
repeats, and
without repeats, there are 332,640 combinations!

(both of those amounts are greater than the standard amount of rows in
XL)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"gjones" wrote:


Can you someone help me, what is the excel or access formula that will
give
me a numeric box combination.for example i select a random set of
numbers
4,
5, 6, 7, 8, 44, 10, 23, 34, 56, 99. I want the result of every
possible
outcome to just these numbers from front to back in a 6 column box
combination.

Results

4, 5, 6, 7, 8, 44
5, 7, 44, 23, 34, 56
56, 8, 34, 99, 10, 99

note that any number can repeat within a given row

Warm Regards

GaryJones