|
box combination formula
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 |
box combination formula
"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 |
box combination formula
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 |
box combination formula
"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 |
box combination formula
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 |
box combination formula
"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 |
box combination formula
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 |
box combination formula
"gjones" wrote:
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" wrote in message ... 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 |
box combination formula
Wonderful, very helpful and very nessecary on my behalf
thanks again -- Warm Regards GaryJones "JoeU2004" wrote: "gjones" wrote: 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 03:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com