asked on # Make unique combinations in separate sheets

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

VB ScriptMicrosoft ExcelMicrosoft Office

Which macro do you want to modify?

Martin,

I believe putting the code here is better. The download seems take a long time for some reason.

The code above would work except I need to edit it request specific numbers to start with (Total of 11 at this point but 10 would work)

The difference is that the code puts all numbers on the same sheet and I'd like to separate the combinations based on the starting number. For example all numbers that begin with the number 2 in the first row would be put into a sheet labeled "Number_2", etc.

Hope this answers your question.

I believe putting the code here is better. The download seems take a long time for some reason.

```
Sub CreateCombinations()
'keepITcool 2004/11/01
Dim rSrc As Range, rDst As Range, rITM As Range
Dim cItm As Collection, vItm()
Dim aIdx() As Byte, vRes()
Dim nItm&, nDim&, nCnt&
Dim r&, c&
Set rSrc = Application.InputBox("Select the Source data", Type:=8)
If rSrc Is Nothing Then
Beep
Exit Sub
End If
'Create a collection of unique items in range.
Set cItm = New Collection
On Error Resume Next
For Each rITM In rSrc.Cells
If rITM <> vbNullString Then cItm.Add rITM.Value2, CStr(rITM.Value2)
Next
nItm = cItm.Count
ReDim vItm(1 To nItm)
For r = 1 To nItm
vItm(r) = cItm(r)
Next
On Error GoTo 0
Let nDim = Application.InputBox("Size of 'groups' ", Type:=1)
If nDim < 1 Or nDim > nItm Then
Beep
Exit Sub
End If
'Get the number of combinations
nCnt = Application.Combin(nItm, nDim)
If nCnt > Rows.Count Then
MsgBox nCnt & " combinations...Wont fit ", vbCritical
'Exit Sub
End If
'Create the index array
ReDim aIdx(0 To 2, 1 To nDim) As Byte
'Create the result array
ReDim vRes(1 To nCnt, 1 To nDim)
'min on first row, max on last row
For c = 1 To nDim
aIdx(0, c) = c
aIdx(2, c) = nItm - nDim + c
vRes(1, c) = vItm(aIdx(0, c))
vRes(nCnt, c) = vItm(aIdx(2, c))
Next
For r = 2 To nCnt - 1
aIdx(1, nDim) = aIdx(0, nDim) + 1
For c = 1 To nDim - 1
If aIdx(0, c + 1) = aIdx(2, c + 1) Then
aIdx(1, c) = aIdx(0, c) + 1
Else
aIdx(1, c) = aIdx(0, c)
End If
Next
For c = 2 To nDim
If aIdx(1, c) > aIdx(2, c) Then
aIdx(1, c) = aIdx(1, c - 1) + 1
End If
Next
For c = 1 To nDim
aIdx(0, c) = aIdx(1, c)
vRes(r, c) = vItm(aIdx(1, c))
Next
Next
dump:
Set rDst = Application.InputBox("Select the Destination Range", Type:=8)
If rDst Is Nothing Then
Beep
Exit Sub
End If
If Rows.Count - rDst.Row < nCnt Then
Stop
ElseIf Columns.Count - rDst.Column < nDim Then
Stop
End If
With rDst
.CurrentRegion.Clear
.Resize(nCnt, nDim) = vRes
End With
End Sub
```

The code above would work except I need to edit it request specific numbers to start with (Total of 11 at this point but 10 would work)

The difference is that the code puts all numbers on the same sheet and I'd like to separate the combinations based on the starting number. For example all numbers that begin with the number 2 in the first row would be put into a sheet labeled "Number_2", etc.

Hope this answers your question.

Okay that macro was not in the posted workbook so I added it. I ran it and it asked me to select a range and I selected something like A1:E25. It then asked me for size of 'Groups'. I was then told, not surprisingly, that the 10,000,000 combinations "Won't fit".

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

29172136.xlsb

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

Martin,

I am attaching an excel workbook that I have tested it on. I selected as follows:

the first range B10:AG19 on "sheet1"

the second range B3:F3 on "Numbers_1"

The reason for two selections is that:

the first selection is the numbers to make the combination from

the second selection is where the sheet to put the combinations into.

Hope this clarifies how this script works. But please remember that I need to make changes that conform to my original post above.

CreateCominations_Created_9_2013.xlsm

I am attaching an excel workbook that I have tested it on. I selected as follows:

the first range B10:AG19 on "sheet1"

the second range B3:F3 on "Numbers_1"

The reason for two selections is that:

the first selection is the numbers to make the combination from

the second selection is where the sheet to put the combinations into.

Hope this clarifies how this script works. But please remember that I need to make changes that conform to my original post above.

What do you want to be able to do that it doesn't do now?

Martin,

need to be able to:

- specify which numbers to make combinations with
- make a separate sheet for each number combination with label

a) 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.

3. 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.

4. if the code is run again it should delete the prior pages created and should begin with numbers from B3:L3 on the first page.

5. The first page should not be deleted or altered since it contains the numbers used to work with.

hope this clarifies it for you.

I'm sorry but I'm still mystified.

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

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.

Martin,

Worksheet named "sheet1" in the files "Combination-Sheets" is the file that contains data in B:11-AJ15.

B3-L3 contains the numbers that combinations are to be made with and that separate sheets are to be named after.

The "CreateCombinations" script is contained and run on different file and is only to serve as a sample. It does not contain the data sheet nor does it need to be used. You can write your own code.

Hope that helps,

Pedro

Here's a first step which only makes the entry of selections easier.

29172136.xlsm

Martin,

I see the selection as a popup. I assume the combinations made from the selection and put into separate sheets is next.

I appreciate all the hard work. Is there anything else you need clarification on?

Pedro

I assume that the source range is variable and will __not__ always be B11:AJ15. I s that a correct assumption? If it is variable can it change in both number of rows and number of columns?

Will you always want to select all the data?

Will you always want to select all the data?

Martin,

You are correct. The source data may not always be B11:AJ15. Yes is can change in both number of rows and number of columns. The reason for this is to avoid errors such as the ones in the embedded file. These occur when too many numbers are selected to the point where the script cannot handle that many. Hence the reason for a selecting specific range. Combination eror log.docx

No I will not always want to select all the data for the reasons stated above and also not all data may be relevant for a specific application.

Why not eliminate that message, show the results that can be shown and at the end say something like "The range selected was too large to show the complete set of results"?

It seems like an error message to let me know the selection has too many numbers. You can choose to display a different message if you wish. As long as it can output combinations containing only specific numbers.

I see that currently "size of groups" results in that number of columns of output.

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>

Martin,

I believe what you’re asking would be best answered by looking at the results in the script file. But just in case, results would be.

3 5 7 26 39

3 7 26 39 49

that is one page that contains all the combinations that begin with 3

the next page would contain combinations that begin with 14, etc.

as to the size of grous they would always be 5. Never more. Never less. So 5 numbers each until all combinations that begin with 3 are exhausted, then the next chosen number, etc.

I asked

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"?

Yes. Except it would be 5 numbers In this instance.

5 would be the size of groups based on the scenario you posted previously. Not sure if I clarified that for you.

Am I correct in assuming that there is no longer any need for the code to ask for the "size of groups" and that it will always be 5?

Yes. In this case the group will always be 5. But if it’s not too much trouble being flexible would help.

This is turning out to be difficult but I'm still working on it.

Thank you

Martin,

If you mean that 1, 3, 11, 12 and 21 is a valid combination then __yes__ these numbers make up a valid combination.

if not, then all the combinations I see should be dumped. This is because all have more than two numbers from the range 1-9. Since all combinations have 1, 3, 5, and 7 are all in the 1-9 range all those combinations should be dumped.

Another way to explain it is that since 1 is the common number in all these combinations only one other number from 2-9 should be used all others should be dumped. Hope these explanations help to clarify things for you.

I'm glad I asked that question since it looks like I totally misunderstood what you needed! I had assumed that what you wanted was for the numbers to be examined, __individually__, left to right, top to bottom, discarding characters until a valid set was accumulated. And so examining the numbers in the picture, the first 1 and 3 are OK and so they are saved, but since they are the same deci, the next number that could be a part of the first set is 11, followed by 12, and since those two are the same deci, the 5th and last valid number would be 21. To make the next valid set I would start at the next number (1) and repeat the process.

Please confirm that what you want me to do instead is to examine the numbers in the picture as__sets__ and to discard those sets that don't meet the rule(s).

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

Martin,

I am including a sample done manually that shows what the macro should do automatically. The macro takes all selected numbers and makes combinations using those numbers.

The parts in red should be discarded since they all contain more than two numbers from the 1-9 range. 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. I have cut every combination between the last red and the first green to show good and bad. The bad (shown is red) is what is discarded and the good (shown in green) is the output to a page labeled "Numbers_X" where the "X" is replaced with the leftmost number in the combinations.

This continues until we get to the end of all the combinations that begin with "3". The next set of combinations is put in different sheet. Notice how I have labeled the sheets "Numbers_3" and the next page "Numbers_4".

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?

Martin,

You are correct! I meant to color it red to show the last row that should be eliminated. That is the reason I need a macro to avoid those types of errors.

The first valid combination should be 3, 5, 11, 12, and 21.

Again considering only the "limit of two numbers from same "deci" rule. This workbook is the result of selecting 1, 2 and 3 from range B11:M15, groups of 5. and selecting AX11 as the output cell. The sets in CA:CE (which I'll call the "filtered sets") are the result of filtering the AX:BB sets and cells in column BC the cells are green if the set appears in the filtered sets.

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 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.

Martin,

It looks as though what you wrote should create the desired results. However, after manually sorting the "filtered sets" in CA:CE I see some issues that need to be resolved.

1) I expected he numbers in CA:CE to be sorted smallest to largest. This way it is easier to see patterns and inconsistencies. In the picture below numbers 14, 19 and 20 are lower than the number on its left. In other words the lower numbers should be on the left and the higher numbers on the right.

2) If you only selected 1, 2 and 3 there are results that should not appear at all. For instance any set where the lowest number does not match 1, 2 or 3 should not appear at all. The sample below was taken from the bottom after sorting.

Notice how the lowest number is 6. But if you did not select 6 then these should not appear at all.

Other than that it looks like the macro would have the desired result.

Martin,

Not sure if this would help. I believe the issues above may be resolved by sorting the numbers smallest to largest and eliminating any duplicates.

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?

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?

yes, except you claim not to have chosen the number six as one of the numbers to make combinations. Thus combinations with the number six (6) should not appear on any sheet.

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.

I thought eliminating all numbers except those selected is the subject of this question. That means making new sheets that begin with only the chosen numbers.

Please let me know what can be resolved with this question and I will make new questions after we are done with this one. This way I have a better idea what to ask.

The attached workbook sorts the sets, implements both rules and writes the sets to Numbers_1, Numbers_2, etc as appropriate, When you test it, choose a smallish sample because it's fairly slow for two reasons: 1) The only way to sort left to right is one row at a time and my tests produced 42,000+ rows, and 2) I'm examining those 42,000+ rows once for your "c" rule and then for those that pass that validation I examine the remaining sets (which are the large majority of them) a second time for your "b" rule.

29172136c.xlsm

Everything I've written for you is in the CreateCombinations macro and a small additional function called isValid. The other macros that you see are all in Module2 and are a result of me recording some actions I took while I was developing the main code. That module can be deleted and I should have done that. CreateCombinations contains code that sorts the sets numerically left to right by row in (in this case) AX:BB. Does that sort interfere with the other functions you are planning?

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?

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?

It just occurred to me that in addition to sorting each set left to right, that you might want an additional sort on the columns. In other words if the unsorted data looks like

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

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

Martin,

you are correct as to sorting. The combinations should be sorted left to right top to bottom with the lefmost number being common to all cells and being put on the same sheet.

I was told the “create combinations“ macro creates a separate sheet to sort with then deletes it. If it sorts in AX:BB I was not aware of that. But it’s fine as long as it creates and sorts combinations I can worth with it.

Okay I'll add the second sort.

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.

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.

Please verify that when I choose 1, 2 and 3 that only the "Numbers_1", "Numbers_2" and "Numbers_3" sheets should be filled.

correct. But note that chosen numbers may not be sequential. Ford example, I may choose 3, 4, 10 and 14. Thus, only combinations with 3, 4, 10 and 14 are expected.

and you’re right. I want to close this question So give me some desired output and will close this one and open others as needed.

P.S. Please make comments on lines and/or subroutines to make it easier to edit and troubleshoot.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

I updated the workbook while you were testing, so please download it again.

Now I get this on "Sheet1". Not sure why.

Since you're using a pop up to select numbers A3:F7 can be left blank. but not sure why it adds numbers from B3:F8 then deletes numbers in B11:F15. Please note that numbers in B11:AJ15 and below are meant for number selection and should not be altered in any way.

FYI. If I am correct the original code makes combinations from cells B11:AJ11 and places them in Sheet_1. Then B12:AJ12 in Sheet_2, etc. If you're using that code that may be why we get these errors.

I use the numbers in row 3 in the code.

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

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?

I selected numbers from B11:AJ11 in one instance then selected a cell in Sheet_1 as the output sheet.

...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.

I’m glad I was able to help.

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

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