- VB Script
- Microsoft Office
- Microsoft Excel
- VBA

The idea is to create unique combinations from the numbers in B11:AJ15.

The number sets are listed in B11:AJ15. I'd prefer a prompt to select the numbers as the sets may change.

1) In order to so unique numbers must be identified. I have done this in AM11:AV17. The unique numbers in AM11:AV17 need not be used as this may create errors in the code.

2) to limit the number of combinations these should be limited as follows:

a) each combination is made up of 5 unique numbers per set. An example = 1,3,11,12, 21

b) no more than one sequential number per set. In the example above,11 and 12 are sequential, thus no other sequential numbers can appear.

c) limit of two numbers from same "deci". A deci is defined as: 1-9, 10-19, 20-29, 30-39, 40-49, 50-59, 60-69, 70-79. For instance, where 11 and 12 appear 13 cannot. It would violate the deci rule.

The result should be a page of combinations. These pages should:

1) begin with the numbers in B3:L3. In other words the first page should be labeled "Number_3" and begin with the number 3 in B5. The next page should be labeled "Number_4" and begin with number 4 in B5, etc.

2) if the code is run again it should delete the prior pages created and should begin with numbers from B3:L3

The file Combinations Sheets contains the fields referred to above.

The file "Matched-And_Unmatched.xlsb" contains a sample script that makes pages but does not let me choose the numbers I wish to make combinations with. This is the added feature I need.

Combination-Sheets.xlsx

Matched-and-Unmatched.xlsb

The number sets are listed in B11:AJ15. I'd prefer a prompt to select the numbers as the sets may change.

1) In order to so unique numbers must be identified. I have done this in AM11:AV17. The unique numbers in AM11:AV17 need not be used as this may create errors in the code.

2) to limit the number of combinations these should be limited as follows:

a) each combination is made up of 5 unique numbers per set. An example = 1,3,11,12, 21

b) no more than one sequential number per set. In the example above,11 and 12 are sequential, thus no other sequential numbers can appear.

c) limit of two numbers from same "deci". A deci is defined as: 1-9, 10-19, 20-29, 30-39, 40-49, 50-59, 60-69, 70-79. For instance, where 11 and 12 appear 13 cannot. It would violate the deci rule.

The result should be a page of combinations. These pages should:

1) begin with the numbers in B3:L3. In other words the first page should be labeled "Number_3" and begin with the number 3 in B5. The next page should be labeled "Number_4" and begin with number 4 in B5, etc.

2) if the code is run again it should delete the prior pages created and should begin with numbers from B3:L3

The file Combinations Sheets contains the fields referred to above.

The file "Matched-And_Unmatched.xls

Combination-Sheets.xlsx

Matched-and-Unmatched.xlsb

Since it does ask for a range is there something else you need, and if so how do I test it.

29172136.xlsb

Which is worksheet is your main worksheet? In other words which sheet should be active when the CreateCombinations is run?

begin with the numbers in B3:L3No worksheet has any data in column 'L' or any column after 'E', so please explain what you mean.

Will you always want to select all the data?

each combination is made up of 5 unique numbers per set. An example = 1,3,11,12, 21Is one of those rows of output a "set"? And if so should the "size of groups" always be 5? Or perhaps 1 to 5>

Is one of those rows of output a "set"? And if so should the "size of groups" always be 5? Or perhaps 1 to 5because you use the term "set" and I'm trying to understand what that is. For example I selected the numbers 1 to 5, chose B11: U15 as the source, chose 4 as the "size of groups" and then chose AX11 as the output range. The start of the output looks like the attached picture. Are the yellow cells a "set"?

Please confirm that what you want me to do instead is to examine the numbers in the picture as

This keeps going until the green section where only 3 and 5 appear from the 1-9 range and only 11 and 12 appear from the 10-19 range.The first green row consists of 3, 5, 11, 12 and 17 and so aren't 11, 12 and 17 in the same deci and hence that row should be red?

If you verify that the filtered sets are correct I'll filter for the other rule and instead of writing the filtered sets to Sheet1 I will write filtered sets that start with 1 to a "Numbers_1" sheet, those that start with 2 to a "Numbers_2" sheet, etc.

29172136a.xlsm

If you only selected 1, 2 and 3 there are results that should not appear at all.I tested the code you originally provided and it too has the same problem, so I think that should be the subject of a new question.

The sample below was taken from the bottom after sorting.I don't understand how what you show below that statement is sorted. Shouldn't the first row be sorted ascending, left to right, so that it shows 6-14-16-22-25?

29172136c.xlsm

The "Numbers_x" sheets were added manually and are cleared just prior to the point where new sets are added to them. Is that a problem?

1-5-3-7-9

1-5-3-7-8

then the existing sort would produce.

1-3-5-7-9

1-3-5-7-8

Do you want a second sort that produces

1-3-5-7-8

1-3-5-7-9

Now let's talk about the second set of data you show in this post.. As I pointed out, hat result comes from existing code and I might be able to correct that in a new question, but for now I can probably eliminate the problem in my output to the "Numbers_x" sheets. First though I need you to verify something. Currently If In choose 1, 2 and 3 I produce filtered sets where any one of those numbers appears in the sorted AX:BB sets. Please verify that when I choose 1, 2 and 3 that only the "Numbers_1", "Numbers_2" and "Numbers_3" sheets should be filled.

- Froze the header rows so that they are always visible
- Added the second sort
- Added a 3rd filter (which is done first) that excludes any set that doesn't start with one of the selected numbers
- Added documentation

The code runs fairly slowly because there is a lot going on. I may be able to speed it up in future questions.

29172136d.xlsm

I don't understand what you did that caused the red-highlighted numbers to appear.

What did you do differently from what I did the attached video?

2020-02-16_09-37-00.mp4

...then selected a cell in Sheet_1 as the output sheet.I assume you meant "...then selected a cell in Numbers_1 as the output sheet."

What you should do is to select a cell like AX11 on Sheet1 (the sheet that contains the 'Select' button).

Just so you are aware, when I selected 1, 2, 4, 6, 7 and then chose B11:AJ11 as the source like I assume you did it took 2 1/2 minutes to run.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017

Experts Exchange Most Valuable Expert (MVE) 2015, 2017

Experts Exchange Top Expert Visual Basic Classic 2012 to 2019

Experts Exchange Top Expert VBA 2018, 2019

Experts Exchange Distinguished Expert in Excel 2018

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial