Macro to do Permutations by a number

b_rianv

New Member
Joined
Jul 10, 2014
Messages
6
Good morning everyone,

I would like to do a macro that does Permutations by a selected number that are "unique, random" combinations that only pertain to the inputted number in the cell. I would input in to a cell on my worksheet the number (8), the macro would only produce "unique random" combinations only pertaining to the inputted number.
Examples
8-15-27-32-45-53
8-10-21-35-48-51
8-25-36-41-49-52


Thank you,
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
I understand what a permutation is, and I do not see how you can get a permutation from only 1 number and no other information. For example, a combination lock needs 3 numbers to open it. Lets say the combination dial goes from 0 to 10. That means there are 11 possabilities. Since there are 3 numbers required to open the combination lock, the formula would be 11 X 11 X 11 or in other words eleven cubed. Assuming repetition permutations are allowed of course which you did not specify. So you see I had 2 pieces of information to come up with this permutation. I knew how many numbers are on the combination lock dial, and I know how many numbers it needed to open the combination lock. You need at least 2 things to make an equation. You can't just type 1 number into a cell and have it calculate. What is it suppose to calculate?
 
Last edited:
Upvote 0
Sorry WarPiglet, I was thinking about a different project I am doing.

What I am looking for is this. A macro "random number generator" with NO REPEATS in a ROW that will generate number combinations by a selected number and only by that number I select for the combinations to begin with. I would like to be able to be able to change the range of the combination range 1-53 that the number pool is drawn from and also how many combinations the macro will produce, let say I want 100 combinations today but tomorrow I would like to have 500.

Example... number range 1-59, length of string 6, by the number 8, at 250 combinations.
So the macro would produce number combinations within the range 0f 1-59 BUT they would start at the number "8",which mean any number lower then "8" would not be in the combinations. Then give me 250 number combinations ALL starting AT "8"
8-5-23-24-35-52
8-11-15-31-40-49
8-21-30-36-45-53
The variables for the Marco are this...
1.) number range can be changed..1-49,1-53,1-59,1-75
2.) combination length can be changed to either 5 or 6 numbers in length
3.) a selected number be it any number (1-2-3-4-5-6-7-8-9-10-11-12-13-14-15...etc.) can be selected to START the combinations, thus ANY number below the selected number to START the combinations with, will not be included in the combinations. So if I want to have combinations STARTING at 9 ALL numbers below 9 are not including in the combinations.
4.) The total amount of the combinations produced by the macro can be chosen. 50,150,225,300,500,1000 total combinations.

Does this help?
 
Upvote 0
Yes now I understand what you want. But just to clarify one thing.
5-4-3-2-1 is not a valid combination because the following numbers after 5 are lower than 5 right? Also what if it was 5-5 6-7-8, would that be valid?

Now I'm going to have to sleep on this one because I have to come up with logic that can do this. You see if you didn't want them to be random, I could easily create a macro that can increment combinations. So the first 3 combinations would look like this.
5-6-6-6-6
5-6-6-6-7
5-6-6-6-8
And it would continue to increment as such until it reaches your max value that you specified and then increment the second from right number by 1 and start the last number over at 6. It would contine to do this until it reaches your max combination that you specified. But since you want it random, I have 2 choices.
1. I can create a random generator that would generate a combination based on all of your criteria. The problem with this logic is when the code generates a combination that has already used. This means that I'd have to create a loop and have it run through the loop each time it generated a combination to ensure that combination has not already been used. This is doable and relatively easy; however, it will use up a lot of system resources going back and fourth through the loop to check if that combination is being used. An even bigger problem with this idea is that if you specify more combination requests than there are possible combinations, then it will never find all the different combinations and fall into an infinate loop that will crash your system. I have no idea about the logic I would use to override this. This is the idea I would like to use, but it has too many probabilities for error.
2. The other option is to create an array of possible combinations. All of the possible combinations would be in that array. It would ensure that you designated less than or equal to the possible different combinations. If there are 1000 possible combinations and you want to return only 200, then it will go ahead and process your request. Anymore than 1000 then it will give you an error. Then it will use a random generator to come up with a number beginning with 1 and ending with the max amount of possabilities, in this case it would be 1-1000. It will check to make sure that combination hasn't been used already through a loop just like my last idea which would once again use up a little but of system resources but it won't be as bad because the logic here is different than my last idea. Then if the combination hasn't already been used, it will print it to the next cell.

I am going to spend the rest of my night playing video games so I'll do this tomorrow because I'm not a pro with arrays and I'll have to do some research to accomplish everything I need. This could take me hours to finsih. The type of vba code I'm going to attempt to make will be a Function so that it can be customizable. Please note that what you are asking for is extremely complicated for my abilities. I have enough knowledge of VB to attempt this, but I may not be able to accomplish this task. I am however confident that I can accomplish this or I would not even have attempted it. I'll try to have it done for you tomorrow night.
 
Upvote 0
Awesome mike. Now I don't have to do it. I have already come across a problem of finding the permutations of non-repeating. I can't figure it out on a combination lock. I was just about to write the thread host about giving me the formula so I could apply it to the code.
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top