number combo generator?

Can anyone help me out with a little project?

I need to be able to create a number combination generator that I can give a set of numbers to and it will return all possilble number combinations.

Is there any simple way of doing this?

not hard to do, but one or two things need clarification. Let's assume that there are 3 numbers and they are 1, 2, 3

Then 123 would be an obvious combination, as would 213 or 321

1. are all numbers integers?

2. do you wish to include any combination or only combinations with all three numbers? For example, is 12 an acceptable combination?

3. do you wish to create combinations with or without replacement, i.e., is 111 or 222 or 333 an acceptable combination?

thankyou for your reply and questions. To clarify further, if I had five columns of digits (all integers) and needed all possible combinations. Let's say I had the the following integers in column one. (1,3,5). In column two I had (3,5,7). In column three I had (5,7,9,). In column four I had (7,9,11). In column five (9,11,13).

I need all combinations that are in consecutive order from least to greatest. For example, combinations such as:

1,3,5,7,9
1,5,9,11,13

are allowed

5,3,7,11,13
5,5,9,11,13

are not allowed

no two numbers are allowed to repeat and any number to the right is not allowed to be less in value to the one to the left.

Again, any help would be greatly appreciated.

Thankyou

Thanks, that clears up your problem quite a bit. One final question ...

In your reply, you define 5 sequences, (1,3,5), (3,5,7), (5,7,9,), (7,9,11), and (9,11,13). That yields 7 unique numbers: 1, 3, 5, 7, 9, 11, and 13. You then indicate that the combination 1,3,5,7,9 and the combination 1,5,9,11,13 are acceptable. Both of these happen to be 5 numbers long. So if I understand the logic, then any monatonically increasing sequence of length 5 drawn from the original unique numbers is acceptable; and other acceptable combinations would include:
1, 3, 5, 9, 11
1, 3, 5, 9, 13
3, 5, 7, 9, 11
3, 5, 7, 9, 13
etc.

Correct?

That's correct. Also, I didn't mention before but you probably realized, all number sequences are five digits in length.

Thank you.

Originally Posted by yungexec

That's correct. Also, I didn't mention before but you probably realized, all number sequences are five digits in length.

Thank you.

I suspect you meant 5 numbers in length, not digits.

Attached is a zipped spreadsheet that should do what you want.

Let me know if this works ...

I am happy that the code is close to what you want. Writing the "results" to another worksheet is simple, but before we do that, let's get the basics working correctly

The other restriction needs clarification. In you original post, you had the same number in multiple columns. For example, "1" was in col 1, col 2 and col 3. If your restriction states that only one number can be drawn from each column, can the number replicated in multiple columns be used multiple times? Should your "rule" be restated as " ... no number can be equal or less than a number to the left ..." ?

This new restriction does change how things are handled, but tweaking the code to do this should not be too difficult.

What is the likely # of values per column? The exact number is not important, but the code might be different if the # were 100 vs 5. Similarly, how many columns are typical? Again, the exact number is not important.

Finally, re "sending me something": this forum is set up to help people. It is expected that as users become more experienced and skilled, they will be able to help others. So, do not just seek help; log on now an again and see if there is someone you can help.

Sorry about the unclear specs before. This is the first time I have had to actually try to explain these type of directions to anyone.

Sometimes there will be duplicate numbers in each column. There cannot be any duplicate numbers per possible combination. No number will be equal to or less than the number to the left.

Secondly, I'm expecting to use numbers in all five columns. Any combinations found using less than five columns aren't allowed. Also, I anticipate up to six or seven numbers in column one. upto fifteen numbers in columns two thru five.

This means I am expecting some possible over flow of combinations onto a second worksheet (hopefully not a third).

Again thankyou very much.

P.S. Are there any good resources for beginners that I can get my hands on to read and teach my self VB. I'm sure this sight if full of useful informationk, I just haven't had time to look thru it all.

I will tweak the code and post something back that will accomplish what you want.

Some thought re "learning VB":
1. explore what is available at this site and others. The ExcelForum has some good sources, poke around. Also, there is a focum focused on VBA only; they have an area just for learning VBA http://www.vbaexpress.com/forum/forumdisplay.php?f=6
you should poke around there
2. get a book on VBA that is aligned with what you want to accomplish. If you have done programming in other languages, a "programmer's reference" will be quite helpful. I like "VisualBasic Programmer's Reference" by Dan Rahmel. If you want something that is focused on Excel, I recommend John Walkenbach's "Excel2000 Power Programming with VBA"
3. use the macro recorder to see the code generated by the application for things you want to do. The code is often bloated, but will give you an excellent feel for structure, methods, objects, properties, etc. Virtually every VBA programmer still uses the macro recorder to explore stuff and jog memories.
4. there are many training courses, video courses, etc., available. This site and the VBA-X site can get your started.
5. programming in VBA (like any other language) requires practice and experience.

attached is a revised version of the code to handle the situation as you redefined it. The code is written to handle only 5 columns but does not care how many numbers are in each column. Given the very large number of combinations possible, the procedure no longer displays individual combinations. Rather it simply writes them to other worksheets. The code is presently set up to write up to 15000 combinations to each of 5 worksheets (the upper limit for rows in a worksheet is 65000). The combinations are written to "Combo" worksheets as a sequence of 5 numbers, each number in an individual cell.

Let me know if this works.

MWE

It couldn't be more perfect. I can't tell you how much you helped me out.

Thank you very much,

yungexec

Glad to help out