Permutation Macro help please :(

[sasha09]

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 Object	2nd Object	3rd Object	4th Object	5th Object	6th Object	7th Object	8th Object
1	2	3	4	5	6	7	8
1	2	3	4	5	6	8	7
1	2	3	4	5	7	6	8
1	2	3	4	5	7	8	6

<tbody>
</tbody>

And that list would continue until:

8

7

6

5

4

3

2

1

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

 

[L. Howard]

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

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

 

[L. Howard]

A slight rewrite to make it a one step click and done.

Just run Sub DoString().

Regards,
Howard

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

 

[sasha09]

This did the trick! Thank you sooo much!!! :D :D :D

 

[L. Howard]

ADVERTISEMENT

You are welcome, thanks for the feed back.

Regards,
Howard

 

[Ikenna]

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.

 

[Peter_SSs]

Anybody, please help me. I need to do a complete enumeration of number permutation for sports betting accumulation.

Welcome to the MrExcel board!

Duplicate to [url]http://www.mrexcel.com/forum/excel-questions/828308-permutation-macro-help-please.html[/URL]

Please do not post the same question multiple times. Refer to #9 of the Forum Rules.

Any answers to this question, bumps, clarifications, or follow-ups should be posted to the linked thread.