Any Excel geniuses here???

Hooked

Howdy all

I'm trying to generate a number sequence in Excel (I'm a graphic designer) so that I can generate 720 results for possible race finish of 6 greyhounds.

ie
123456
123465
123564
123546

and so on...
6x5x4x3x2x1 = 720 variables

I'm relatively familiar with Excel as we use it for mail merging, personalising data and writing numbering blocks for tickets, docket books, etc... but these are usually in sequence.

This one has me stumped!

kippy

Is this of any use to you?
http://spreadsheetpage.com/index.php/tip/generating_permutations/

I've had to do this in the past and found a great link on it, but can I find it again? NO

Hooked

kippy wrote: »

Is this of any use to you?
http://spreadsheetpage.com/index.php/tip/generating_permutations/

I've had to do this in the past and found a great link on it, but can I find it again? NO

Thanks but as a graphic designer (photoshop, indesign, etc...) all that code is double dutch to me. I'll try and make sense of it! Cheers

daniel2013

I know you wish to use excel, however would it be possible for you to use random.org (online random number generator)?
http://www.random.org/integer-sets/

Use the settings below:
Generate 720 sets with 6 unique random integer(s) in each.
Each integer should have a value between 1 and 6 (both inclusive; limits ±1,000,000,000).

Make sure also to uncheck the three checkboxes in Step 2 (Display Options)

coylemj

daniel2013 wrote: »

I know you wish to use excel, however would it be possible for you to use random.org (online random number generator)?
http://www.random.org/integer-sets/

Use the settings below:
Generate 720 sets with 6 unique random integer(s) in each.
Each integer should have a value between 1 and 6 (both inclusive; limits ±1,000,000,000).

Make sure also to uncheck the three checkboxes in Step 2 (Display Options)

I don't think that will meet the OP's requirements because it would be statistically impossible for that method to give him the full set of 720 permutations. Generating random numbers will always throw up duplicates which he does not want.

Imagine asking a random number generator to generate 100 numbers in the range 1-100, what are the chances that there will be no duplicates?

Hooked

coylemj wrote: »

I don't think that will meet the OP's requirements because it would be statistically impossible for that method to give him the full set of 720 permutations. Generating random numbers will always throw up duplicates which he does not want.

Imagine asking a random number generator to generate 100 numbers in the range 1-100, what are the chances that there will be no duplicates?

You were dead right!!!

I was able to do "find and replace" search in my design software and some are repeated... But thanks a MILLION!!! Daniel2013 for the suggestion. You momentarily had a virtual pint being pulled!!!

davetherave

Open up a blank excel document.


Press Alt+F11 to open up visual basic
Insert --> Module

Paste everything in the quote below.

Dim r As Long, c As Long, strArray() As String, sTemp As String

Sub Combo()
Dim InString As String
r = 0: c = 0: Erase strArray()
InString = InputBox("Enter text to permute:", "Permutations", "1,2,3,4,5,6")
If InStr(InString, ",") = 0 Then Exit Sub
strArray = Split(InString, ",")
If UBound(strArray) - LBound(strArray) + 1 <= 6 Then
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
Call GetPerms("", Left("012345", UBound(strArray) - LBound(strArray) + 1))
Range(ActiveCell, ActiveCell.Offset(0, c)).Columns.AutoFit
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
Else: MsgBox "Too many permutations!"
End If
End Sub

Private Sub GetPerms(x As String, y As String)
Dim i As Integer, j As Integer, k As Integer
j = Len(y)
If j < 2 Then
For k = 1 To Len(x & y)
sTemp = sTemp & strArray(CInt(Mid(x & y, k, 1))) & ","
Next k
ActiveCell.Offset(r, c) = Left(sTemp, Len(sTemp) - 1)
sTemp = ""
If ActiveCell.Row + r < Rows.Count Then: r = r + 1: Else: r = 0: c = c + 1
Else
For i = 1 To j
Call GetPerms(x + Mid(y, i, 1), Left(y, i - 1) + Right(y, j - i))
Next i
End If
End Sub

Go back to your excel document.
Click in the cell you want the list to begin at.
Tools --> Macro --> Macros and click on Combo. Click on run. You should see 1,2,3,4,5,6. Click Ok.

This will give you the 720 results in a column.

daniel2013

Hooked wrote: »

You were dead right!!!

I was able to do "find and replace" search in my design software and some are repeated... But thanks a MILLION!!! Daniel2013 for the suggestion. You momentarily had a virtual pint being pulled!!!

Sorry about that, but your original post just said you wanted 720 results, not all 720 possible results! Good luck with finding a solution though, I'm afraid I can't help you there if you need all the possibilities

Hooked

davetherave wrote: »

Open up a blank excel document.

This will give you the 720 results in a column.

Dave The Rave.... YOU DA MAN!!!!!!!

I thought I needed these in separate columns BUT your version with all numbers in one "cell" is an even better option for me. I hope the CSV treatment and additional commas dont throw things out of whack!!!

CHEERS!!!!