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?

 

[motilulla]

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)

Hello &#x2605 hicrpot10, in that case I will suggest you to use pgc01 code under this link you will find your answer sure</SPAN></SPAN>

https://www.mrexcel.com/forum/excel-questions/277924-combination-help-5.html#post1424848

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

 

[Worf]

I tested the code with a small combination with repetition and it produced the expected result.

Tag: VBA combination with repetition.

 

[bvb007]

Really great stuff, I appreciate that you shared it!
I have a question: I tried with 12 elements (which means 479million permutations), but I got run time error '7' - Out of memory. Do you have any suggestions ?
Thanks in advance

 

[Worf]

• Permutations can be with or without repetition, which is your case?
• Permutations are defined by two parameters, but you informed only one (12). What is the other one?

 

[bvb007]

Hi, this is what I'm trying to permute. Obviously, I don't write them into the sheet as Excel would not able to handle that many lines, but I want to use the vPerms array which is filled with the data. But it says out of memory. Any ideas?

• Permutations can be with or without repetition, which is your case?
• Permutations are defined by two parameters, but you informed only one (12). What is the other one?

 

[Worf]

Hi

I am at work and the firewall is probably preventing me from seeing what you posted.
I will try to visualize it at home.

 

[bvb007]

hi, the picture contains basically:

Elements | Qty
A | 1
B | 1
C | 1
D | 1
E | 1
F | 1
G | 1
H | 1
I | 1
J | 1
K | 1
L | 1

So 12 pcs of elements, with quantities 1.

Hi

I am at work and the firewall is probably preventing me from seeing what you posted.
I will try to visualize it at home.

 

[shg]

Could you not examine them one by one instead of generating them all?

What are you trying to do?