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

	Act	Act	Act
Option 1	1	2	3
Option 2	1	3	2
Option 3	2	1	3
Option 4	2	3	1
Option 5	3	1	2
Option 6	3	2	1

<tbody>
</tbody>

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!