We help IT Professionals succeed at work.

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
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Which macro do you want to modify?

Author

Commented:
Martin,

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

Open in new window



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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:
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
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:

Martin,


need to be able to: 

  1. specify which numbers to make combinations with
  2. 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.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?
begin with the numbers in B3:L3
No worksheet has any data in column 'L' or any column after 'E', so please explain what you mean.

Author

Commented:

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

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Here's a first step which only makes the entry of selections easier.
29172136.xlsm

Author

Commented:

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

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?

Author

Commented:

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.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:

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.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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, 21
Is one of those rows of output a "set"? And if so should the "size of groups" always be 5? Or perhaps 1 to 5>

Author

Commented:

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.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 5
because 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"?2020-02-12_18-37-16.png

Author

Commented:

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

Author

Commented:

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

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?

Author

Commented:

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

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:

Thank you 

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Considering only the "limit of two numbers from same "deci" rule, if this pictures shows the start of a "dump", should the green cells make up the first set?2020-02-13_20-20-05.png

Author

Commented:

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.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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).

Author

Commented:

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


Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?

Author

Commented:

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.



Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:

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.

Author

Commented:

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.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?

Author

Commented:

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.


Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:

Martin,


I need clarification. 


  1. I found several macros in that sheet which do I run to sort the numbers?
  2. 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.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:

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.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:

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.

Social distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
In this workbook I
  • 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
I was planning on adding the documentation after you closed the question, but I added it now. If there's something you don't understand I'll try to explain it. I actually wrote an article on documenting code.

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

Author

Commented:

Martin,


After running the code I get this error.


Please advise.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

Commented:

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.


Author

Commented:

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.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Author

Commented:

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

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
...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.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
SILVER EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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