box combination formula

[gjones]

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

 

[JoeU2004]

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 -----

 

[Luke M]

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)

 

[JoeU2004]

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 -----

 

[gjones]

thanks but what is the formaula?

Excel or XL

 

[JoeU2004]

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]

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

 

[JoeU2004]

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

I hope you realize that I am __not__ recommending this macro. I had hoped
that you would abandon your foolish quest after seeing the macro and
contemplating the discouraging responses about the number of computations.

Be that as it may....

1. Use Tools > Macros > Security Level to set Medium level. This will
permit you to reopen the workbook with a prompt to enable macros.

Note: Macro security will apply to all workbooks that you open
subsequently.

2. Use Excel Help to find "Create macro", then click on "Create macro using
Microsoft Visual Basic" for general instructions.

3. Select and copy the macro text (including the Option Explicit directive),
and paste into the VBE window.

Note: The line "cells(r,c) = ...." probably will not paste as intended
due to wrap-around in the posting. If that line becomes two or more lines
when you paste into the VBE window, add " _" (space underscore) at the end
of each __except__ the last line.

Also: See the suggestion below before you execute the macro.

4. In Excel, select or insert a blank worksheet. With that as the active
worksheet....

5. In the VBE window, put the cursor anywhere between the Sub and End Sub
statements, then press F5 to execute the macro.

Be forewarned that the macro as written, generating more than 1.7 million
permutations in 65536 rows of 256 columns, will probably run for several
minutes, at least -- maybe even __many__ minutes the first time. (On my
computer, I estimated about 18 minutes for the first time, but less than 4
minutes for subsequent times.)

Suggestion....

Before you invest many minutes of compute time, I suggest that you try a
scaled-down run to see if the macro is giving you results in a form that you
want. For example:

Const rMax As Long = 10 'instead of 65536
Const cMax As Long = 10 'instead of 256

will generate 100 permutations in a 10-by-10 range in the worksheet.


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

 

[gjones]

Wonderful, very helpful and very nessecary on my behalf

thanks again