generating all possible 5-number combinations from up to 30 numbers

Hello!

I need a way to generate and list all possible 5-number permutations from the numbers 0-30.

I must have the option of choosing how many of the numbers 0-30 to generate from, and the sum of each permutation must be equal to the chosen number.


Example: if I choose the number 0, the permutation 0,0,0,0,0 will be generated.
if I choose the numer 1, the permutaions shall be:
0,0,0,0,1
0,0,0,1,0
0,0,1,0,0
0,1,0,0,0
1,0,0,0,0

After number 1 it gets a little more complex :-).

Help is appreciated!
//Jon

You'd need a VBA script to do this and it's going to take a little while to churn through (there are more than 28 million 5-digit combinations of the numbers 0-30)

How are you planning to display the output, because there could be quite a few possible matches. For example, using the numbers 0-30 to generate a total of 30 gives 46,376 possible combinations.

Originally Posted by Andrew-R

You'd need a VBA script to do this and it's going to take a little while to churn through (there are more than 28 million 5-digit combinations of the numbers 0-30)

How are you planning to display the output, because there could be quite a few possible matches. For example, using the numbers 0-30 to generate a total of 30 gives 46,376 possible combinations.

I would like to display the output in five different columns, one number in each. Sorting could start with the left column and the highest number, meaning that with 30 numbers you would have 30 in first column going for around 1550 rows and the 29 for 1550 rows and then 28 etc. Column 2,3,4 and 5 will then follow in some structured smart way :-)


//Jon

@Nicky - I'd really, really avoid using Select inside a 28 million iteration loop, unless you're happy to wait 6 or 7 years for your program to run

Originally Posted by NickyC

As Andrew says, this is going to take a while to run, as there are so many numbers to generate. Each number will need many more rows than 1550 because of the number of permutations that can follow.

This code is ugly - I'm sure one of the experts can do better - but it should work, if it doesn't crash your program! I have set it to start a new column after 1,000,000 rows, you might want to change that.

Please Login or Register  to view this content.

It should only generate a permutation IF the sum of the individual numbers of each permutaion = 30 (or lower if thatīs the reference number of coice)
Wouldnīt it then be much more manageable for excel to generate. As written by Andrew_R the combinations for 30 number would then be 46,376.

Iīve tried a VBA similar to the one above (at least I think itīs similar) that were supposed to give all permutations regardless of the sum. Excel crashed after >150 000 rows.
Iīd like to avoid that, by only generating those permutations that equal a specified number, in this case 30.



//Jon

The following VB sub takes the maximum number of use (0-30), the target value and a range representing the cell in the upper left-hand corner of the output range and generates all combinations matching the target value:

Please Login or Register  to view this content.

This bit of code calls it (here set to my example of generating all permutations of 0-30 that equal 30 - takes about a minute to run on my machine)

Please Login or Register  to view this content.

In the test case it generates 46,376 rows of data, which is correct.

It doesn't do anything clever with switching output to new columns, or making sure it doesn't exceed the upper row limit, so if you increase the range above 30 or expand it to more digits it's likely to fail.

Originally Posted by Andrew-R

The following VB sub takes the maximum number of use (0-30), the target value and a range representing the cell in the upper left-hand corner of the output range and generates all combinations matching the target value:

Please Login or Register  to view this content.

This bit of code calls it (here set to my example of generating all permutations of 0-30 that equal 30 - takes about a minute to run on my machine)

Please Login or Register  to view this content.

In the test case it generates 46,376 rows of data, which is correct.

It doesn't do anything clever with switching output to new columns, or making sure it doesn't exceed the upper row limit, so if you increase the range above 30 or expand it to more digits it's likely to fail.

Nothing short of amazing. Thank you 46,376 times! I really needed this for work. Whish I could code like that!!!


//Jon

Happy to help, but for future reference could you avoid quoting entire posts, please?

On a side note this problem is niggling me a little, as I'm sure there's a quicker way to generate the combinations rather than churning through every possible one, but I can't quite work out how to do it. I'm torn between working it out and actually doing the work I'm supposed to be doing this afternoon

Originally Posted by Andrew-R

Happy to help, but for future reference could you avoid quoting entire posts, please?

Certainly, I was so eager that I couldnīt think straight :-)

Thanks again!

Andrew-R – good advice, I tested my code on 0-9 but forgot my elementary statistics on how much bigger 0-30 would be.
Glad you found the solution