Display All Permutations of a List Without Repeating Values

[Yabber]

Hi Everyone,

I've been trying to get this to work this out for a while but am starting to struggle and am running out of time!

I'm trying to optimise a running order for a show and am looking to generate all possible permutations of a possible running order with 28 acts. Below is an example of what I'm looking for with just 3 acts. The input for this would simply be the number of acts (in this case 3).

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Act[/TD]
[TD]Act[/TD]
[TD]Act[/TD]
[/TR]
[TR]
[TD]Option 1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Option 2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Option 3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Option 4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Option 5[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Option 6[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I have tried to generate this using For loops in VBA but cannot solve the issue of repeating acts (ie. each act can only happen once 1,3,3 is not a possible option)

Is anyone able to provide some VBA code or some advice to help me? I realise that 28 acts will result in a large number of permutations but have already written a macro to sort and rank these. I'm hoping that the number of rows will be less than excels limit! I have been trying to find and learn from similar examples online but cannot find anything to match my criteria.

I will continue to work on it myself but my experience of VBA is recording functions and very basic ifs and for loops.

Thank you in Advance!

Yabber

 

[Yabber]

Update: if my calculations are correct 28 acts leaves me with about 3x10^29 permutations which is far to many

It would still be great to get this working for say 10 acts as I can work with it split into smaller sections!

 

[kalak]

You give an example of your input, but what's the example output you're looking for from this input?

 

[Yabber]

Hi kalak,

The input would simply be the total number of acts. The table above shows the target output from an input value of 3 acts.

For example if the input was 4 the first row of the output would be: 1,2,3,4 followed by all other possible configurations. If the input was 5 it would be 1,2,3,4,5.

I hope that makes sense, I'm not always the best at explaining things!