Permutation Macro help please :(

sasha09

New Member
Joined
Apr 5, 2013
Messages
2
Anybody, please help me. I am not good with VBA macros but I need to do a complete enumeration of number permutations :(

Here's my problem.

I have 8 objects--1, 2, 3, 4, 5, 6, 7 and 8. Each "solution" must contain all 8 objects, but the order matters. I need a macro that can generate all of these possible permutations.

Additionally, each of the objects need to be placed in a separate cell. It should look like this, where each row is a "solution:

1st Object2nd Object3rd Object4th Object5th Object6th Object7th Object8th Object
12345678
12345687
12345768
12345786

<tbody>
</tbody>

And that list would continue until:

87654321

<tbody>
</tbody>

Please help me. I am so clueless as to what I need to do. :( Thank you to all the kind souls out there.)
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try this where you will enter 12345678 in A1, then rub Sub DoString().
Then run sub Sub TxToCoL().
The end data will start in cell D1 over eight columns and down 40,320 rows. (Change D1 to suit where you want the data to go.)
If you use other than exactly eight digits, you may want to un comment the first line of code in TxToCol and comment out the second.

Regards,
Howard

Code:
Option Explicit
Option Compare Text
Dim CurrentRow

Sub DoString()
On Error Resume Next
    Dim Instring As String
    Dim i As Integer, j As Integer
    Instring = Range("A1").Value
        Range("A1").Select
        CurrentRow = 1
    Call GetPermutation("", Instring)
        
End Sub
Sub GetPermutation(X As String, y As String)
On Error Resume Next
Dim j, i
j = Len(y)
    If j < 2 Then
            Cells(CurrentRow, 1) = X & y
            CurrentRow = CurrentRow + 1
      Else
        For i = 1 To j
            Call GetPermutation(X + Mid(y, i, 1), _
            Left(y, i - 1) + Right(y, j - i))
        Next
    End If
End Sub


Sub TxToCoL()
'Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Select
Range("A1:A40320").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array _
        (5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1)), _
        TrailingMinusNumbers:=True
End Sub
 
Upvote 0
A slight rewrite to make it a one step click and done.

Just run Sub DoString().

Regards,
Howard

Code:
Option Explicit
Option Compare Text
Dim CurrentRow
Sub DoString()
On Error Resume Next
    Dim Instring As String
    Dim i As Integer, j As Integer
    Instring = Range("A1").Value
        Range("A1").Select
        CurrentRow = 1
    Call GetPermutation("", Instring)
  TxToCoL
End Sub

Sub GetPermutation(X As String, y As String)
On Error Resume Next
Dim j, i
j = Len(y)
    If j < 2 Then
            Cells(CurrentRow, 1) = X & y
            CurrentRow = CurrentRow + 1
      Else
        For i = 1 To j
            Call GetPermutation(X + Mid(y, i, 1), _
            Left(y, i - 1) + Right(y, j - i))
        Next
    End If
End Sub
Sub TxToCoL()
'Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Select
Range("A1:A40320").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array _
        (5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1)), _
        TrailingMinusNumbers:=True
End Sub
 
Upvote 0
Anybody, please help me. I need to do a complete enumeration of number permutation for sports betting accumulation.

Here's my problem

There a 5 matches being played in the NBA

LA Lakers(LAL) Vs Sacramento Kings(SAC)
Toronto Raptors(TOR) Vs Atlanta Hawks(ATL)
LA Clippers(LAC) Vs San Antonio Spurs(SAS)
Memphis Grizzlies(MEM) Vs Phoenix Suns(PHX)
Portland Trail Blazers(POR) Vs Cleveland Cavaliers(CLE)

Let's assume the the result for ;

First match LA Lakers Vs Sacramento King is

(100 - 700) the sum is 170 which is an EVEN number
OR
(107 - 98) the sum is 205 which is an ODD number

Second match Toronto Raptors Vs Atlanta Hawks is

(112- 117) the sum is 229 which is an ODD number
OR
(98 - 102) the sum is 200 which is an EVEN number

Third match LA Clippers Vs San Antonio Spurs is

(70 - 72) the sum is 142 which is an EVEN number
OR
(100- 79) the sum is 179 which is an ODD number

Fourth Match Memphis Grizzlies Vs Phoenix Suns is

(105 - 116) the sum is 221 which is an ODD number
OR
(98 - 90) the sum is 188 which is an EVEN number

Fifth Match Portland Trail Blazers Vs Cleveland Cavaliers is

(98 -86) the sum is 184 which is an EVEN number
OR
(85 - 110) the sum is 195 which is an ODD number

*For every match there are two possible outcomes either ODD or EVEN
i need a macro to generate all the possible ARRANGEMENT(permutation) without repetition, of the outcome either ODD or EVEN of all the 5 matches to be placed on a betting slip

Here's an illustration

1st Betting Slip 2nd Betting Slip 3rd Betting Slip 4th Betting Slip 5th Betting Slip 6th Betting Slip
LAL Vs SAC - ODD LAL Vs SAC - EVEN LAL Vs SAC - ODD LAL Vs SAC - EVEN LAL Vs SAC - ODD LAL Vs SAC - EVEN
TOR Vs ATL - ODD TOR Vs ATL - EVEN TOR Vs ATL - EVEN TOR Vs ATL - ODD TOR Vs ATL - ODD TOR Vs ATL - EVEN
LAC Vs SAS - ODD LAL Vs SAC - EVEN LAL Vs SAC - ODD LAL Vs SAC - EVEN LAL Vs SAC - ODD LAL Vs SAC- EVEN
MEM Vs PHX - ODD LAL Vs SAC - EVEN LAL Vs SAC - EVEN LAL Vs SAC - ODD LAL Vs SAC - ODD LAL Vs SAC - EVEN
POR Vs CLE - ODD POR Vs CLE- EVEN POR Vs CLE - ODD POR Vs CLE - ODD POR Vs CLE - EVEN POR Vs CLE- ODD


Please i need a full permutation(arrangement) of outcome of the matches without repetition.
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top