Make unique combinations in separate sheets
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
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.
Since it does ask for a range is there something else you need, and if so how do I test it.
29172136.xlsb
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
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.
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
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
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.
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.
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.
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.
Yes. In this case the group will always be 5. But if it’s not too much trouble being flexible would help.
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.
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).
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.
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
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.
29172136c.xlsm
Martin,
I need clarification.
- I found several macros in that sheet which do I run to sort the numbers?
- Which macro makes sheets named "Numbers_x"
3. "sheet1" shows work done in AX:BB. Sheet1 should be untouched as I intend to perform other functions there.
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
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.
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.
Martin,
After running the code I get this error.
Please advise.
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 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 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.
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