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
need to be able to:
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.
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.
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,
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?
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>
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.
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 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?
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.
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.
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.
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.
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.
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 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."