Finding All Combinations With No Repeat Values

[ianmanson]

Dear Mr. Excel,

I hope you can help me, I have been searching and playing and trying to find a solution.

If I have a list of numbers (say 1-5), I would like a way of calculating every different combination of those numbers that is possible, however I need every number to be used in every result (ie. no repeats). So...
1,2,3,4,5
1,3,4,5,2
1,4,5,2,3
Are all acceptable (every value used, nu duplicates)
However...
1,1,2,3,4 etc would not be acceptable as it contains duplicates and does not use all numbers.

I have previously found a similar problem on the website, but this allowed duplicates. I was able to adjust the formula for my need (numbers 1-50) but that would produce (allowing for duplicates) I think around 80 million options.

Many Thanks

Ian

 

[oldbrewer]

you are talking about PERMUTATIONS not COMBINATIONS

for lotteries etc it is combinations

for j=1 to 5
for k=1 to 5
if k=j then next k

carry on like that so you have 5 nested loops and if n is not rejected make cells(1,1)=j cells(1,2)= k etc etc