Vba to list all possible permutations

[Gordonik]

Based on the sample data set. I am looking for a macro to return all possible permutations with repetition (order matters).
Let this code be universal (not only 3 but cover N unique arguments in a data set)
Let's this macro to do it in a new sheet and list an output in columns.
Google shows plenty of examples for string but not ranges.
There are a couple also where custom function is used but I do not want to use any Excel formula. Just run the code and get results.

Someone ready for this?

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[motilulla]

Hi Gordon

This is a solution.

In this case each one of the distinguishable elements will appear repeated a specific number of times.

In this example that I post, we have permutations with repetition with 2 "a"'s, 1 "b" and 2 "c"'s

The input is a table where in the first column you list the elements and in the second column you list the number of times that they repeat

Please test.

Option Explicit

' PGC - AUG 2016
' Permutation with repetition and with restrictions.
' A set of distinguishable objects is given, as well and the number of times each one is repeated
' Ex. Permutations with repetition with 2 "a"'s, 1 "b" and 2 "c"'s
' Input in a nx2 table, first columns the elements and second column how many times they repeat
Sub PermMultRep()
Dim vIn As Variant, vPerms As Variant, vPerm As Variant
Dim lRow As Long

vIn = Range("A2", Range("A2").End(xlDown)).Resize(, 2).Value ' table of elements and number of times they repeat
ReDim vPerm(1 To Application.Sum(Application.Index(vIn, 0, 2))) ' array for the current permutation
ReDim vPerms(1 To Application.MultiNomial(Application.Index(vIn, 0, 2)), 1 To UBound(vPerm)) 'array to store all permutations

PermMultRep1 vIn, vPerm, vPerms, 1, lRow ' calculate all the permutations into the vPerms array

Columns("D").Resize(, UBound(vPerm) + 1).Clear ' clears columns for the output
Range("D2").Resize(UBound(vPerms, 1), UBound(vPerms, 2)).Value = vPerms ' writes the output in E2, down and across
End Sub

Sub PermMultRep1(ByVal vIn As Variant, vPerm As Variant, vPerms As Variant, ByVal lInd As Long, lRow As Long)
Dim j As Long, lCol As Long
Dim v1 As Variant

For j = LBound(vIn, 1) To UBound(vIn, 1)
    If vIn(j, 2) > 0 Then
        vPerm(lInd) = vIn(j, 1)
        If lInd = UBound(vPerm) Then
            lRow = lRow + 1
            For lCol = 1 To UBound(vPerm)
                vPerms(lRow, lCol) = vPerm(lCol)
            Next lCol
        Else
            v1 = vIn
            v1(j, 2) = v1(j, 2) - 1
            PermMultRep1 v1, vPerm, vPerms, lInd + 1, lRow
        End If
    End If
Next j
End Sub

Input is in A2, down and across.


[TABLE="width: 2"]
<TBODY>[TR]
[TH][/TH]
[TH="align: center"]A
[/TH]
[TH="align: center"]B
[/TH]
[TH="width: 30, align: center"]C
[/TH]
[TH="width: 30, align: center"]D
[/TH]
[TH="width: 30, align: center"]E
[/TH]
[TH="width: 30, align: center"]F
[/TH]
[TH="width: 30, align: center"]G
[/TH]
[TH="width: 30, align: center"]H
[/TH]
[TH="width: 30, align: center"]I
[/TH]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: left"]Element
[/TD]
[TD="align: left"]Quantity
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]18
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]19
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]20
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]21
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]22
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]23
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]24
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]25
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]26
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]27
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]28
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]29
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]30
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]31
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]c
[/TD]
[TD="align: left"]b
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: left"]a
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]32
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="colspan: 10"][Book1]Sheet4
[/TD]
[/TR]
</TBODY>[/TABLE]

Hello, finding a solution for my requirement I come across this thread under this link</SPAN></SPAN>
https://www.mrexcel.com/forum/excel...-all-possible-permutations-2.html#post4600178</SPAN></SPAN>
After the set up when I run the code getting error 438 with highlighting the line below</SPAN></SPAN>

</SPAN></SPAN>[COLOR=#000000]
ReDim vPerms(1 To Application.MultiNomial(Application.Index(vIn, 0, 2)), 1 To UBound(vPerm)) 'array to store all permutations[/COLOR]</SPAN></SPAN>[COLOR=#000000]
[/COLOR]

</SPAN></SPAN>

Please anyone can look it for me need help </SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN>

 

[motilulla]

Hello, finding a solution for my requirement I come across this thread under this link</SPAN></SPAN>
https://www.mrexcel.com/forum/excel...-all-possible-permutations-2.html#post4600178</SPAN></SPAN>
After the set up when I run the code getting error 438 with highlighting the line below</SPAN></SPAN>

</SPAN></SPAN>[COLOR=#000000]
ReDim vPerms(1 To Application.MultiNomial(Application.Index(vIn, 0, 2)), 1 To UBound(vPerm)) 'array to store all permutations[/COLOR]</SPAN></SPAN>[COLOR=#000000]
[/COLOR]

</SPAN></SPAN>

Please anyone can look it for me need help </SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN>

Hello, well search in the forum and in the goggle find that the function "MultiNomial" is not a part of Excel 2000 does there is any substitute function for it?</SPAN></SPAN>

 

[pgc01]

Hello, well search in the forum and in the goggle find that the function "MultiNomial" is not a part of Excel 2000 does there is any substitute function for it?

Hi

Instead of

ReDim vPerms(1 To Application.MultiNomial(Application.Index(vIn, 0, 2)), 1 To UBound(vPerm)) 'array to store all permutations

try

With Application
    ReDim vPerms(1 To .Fact(.Sum(.Index(vIn, 0, 2))) / .Product(.Fact(.Index(vIn, 0, 2))), 1 To UBound(vPerm)) 'array to store all permutations
End With

 

[motilulla]

Hi

Instead of

ReDim vPerms(1 To Application.MultiNomial(Application.Index(vIn, 0, 2)), 1 To UBound(vPerm)) 'array to store all permutations

try

With Application
    ReDim vPerms(1 To .Fact(.Sum(.Index(vIn, 0, 2))) / .Product(.Fact(.Index(vIn, 0, 2))), 1 To UBound(vPerm)) 'array to store all permutations
End With

PGC, ray: yes your altered code worked flawless! With my older version </SPAN></SPAN>

Thank you for your kind help, query solved! </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[hicrpot10]

Hi PGC,

I also tried the code and it is brilliant, really good stuff! But instead of "a" "b" "c" i want all the combinations of a 5 x 3 matrix eg (02 04 06 07 08 12 14 16 17 18 22 00 00 00 00) still having the permutations with repetition. Subsets of 5 as seen below.

(02 04 06 08 14)
(02 04 06 08 16)

 

[Worf]

You used the words combination and permutation, which are different things.
If it is permutation with repetition, there are (15) ^5 possibilities. Please clarify.

 

[hicrpot10]

You used the words combination and permutation, which are different things.
If it is permutation with repetition, there are (15) ^5 possibilities. Please clarify.

Combination.

 

[Worf]

I understand it is combination with repetition, 15 choices and sets of 5. There are 11628 possibilities.
I will be back as soon as possible.