Suggestions 
Example file
Combinations

1. Combinations 2. Characteristics of combinations 3. Sample file 3.1 Worksheet 'explanation' 3.2 Worksheet 'VBA' 3.3 Worksheet 'VBArecursion' 3.4 Worksheet 'VBA fixed' 3.5 Worksheet 'formulae' 3.6 Worksheet 'formula_fixed' 
Regularly, the question arises in fora what is the number of possible combinations of x elements from a total of n elements. I will explain an approach to create those combinations and how this can be performed in VBA in Excel and by Excel formulae. The graphical illustration of the approach you will find in the file .... The file also contains several VBAsolutions and two formulabased solutions. Characteristics of combinations. Combinations have 2 characteristics:  In each combination, an element occurs only once.  the order of elements does not matter: ABC is equal to ACB, BAC, BCA, CAB , and CBA as a combination. Example: How many possible combinations are there of 5 elements from a group of 10 elements? In fact, this is a question about a counter in a decimal system. A decimal system contains the elements 0, 1, 2, 3, 4, 5, 6, 7, 8 and 9. All numbers from 00000 to 99999 form the set of all groups with 5 elements from the 10 elements of the decimal system. Because each element may only occur once in a combination, the smallest group of 5 elements is 01234. The largest group then is 56789. If we count from 01234 to 56789 we have all groups of 5 elements. The number of combinations of 5 elements however is much smaller than the number of groups of 5 elements. From the total number of groups of 5 elements we can select combinations by entering a test criterion:  the second element must be larger than the first element,  the third element must be larger than the second,  the fourth larger than the third and  the fifth larger than the fourth. In short: element 1 < element 2 < element 3 < element 4 < element 5 If we now look at all numbers between 012345 and 56789 and select the numbers that meet this criterion, we are done. In VBA: For j= 1234 to 56789 c00= format(j,"00000")
nextif left(c00,1) < mid(c00,2,1) and mid(c00,2,1) < mid(c00,3,1) and mid(c00,3,1) < mid(c00,4,1) and mid(c00,4,1) < right(c00,1) then c01=c01 & vblf & c00 msgbox mid(c01,2) PatternanalysisIf we look at the pattern in the number sequence from 01234 to 56789 it shows, tht If the counter is always increased by 1 in the rightmost digit of the 01234 series, we get the following combinations: 01234 01235 01236 01237 01238 01239  if the highest number (9) is reached, the penultimate number gets 1 more (as in the decimal system). 0124.. In the last position, we don't start with 0, as in the decimal system, but with penultimate number increased by 1. The condition we use requires that the later element must be larger than the previous element. The first combination that meets this requirement is: 01245  subsequently the last counter will be increased by 1 until the maximum of 9 has been reached. The highest combination is 56789. This shows the maximum value in each position:
5th position: maximum 9 4th position: maximum 8 3rd position: maximum 7 2nd position: maximum 6 1st position: maximum 5 As soon as a counter has reached its maximum value the counter (left) next to it will be increased by 1 The sample file in this page contains 6 worksheets  the rotation principle will be illustrated graphically  and it contains flexible and static VBAapproaches  flexible and static approaches with Excelformulae The algorithm is illustrated by all combinations of 5 elements from a group of 10 elements. With the button 'Step 1' you can follow each phase of the creation of valid combinations. Each press of a button creates a new combination. A combination is written in the A1:E252 area As a graphical tool 5 'clocks' with 10 elements are used. These clocks rotate clockwise by clicking on the 'Step 1' button. The red digits of the clocks are in the corresponding column of Rnge A1:E252. Clock V rotates fastest just like in a decimal system the units, then the tens, the hundreds, etc. In this case, too, you can read the result of each click from righttoleft. If you want to start over, click on the 'Reset' button. Range A1:E252 will be cleared. The clocks are all set to 0 Each click starts the macro M_rotation. This macro 'spins' the 5 clocks according to the algorithm described above. B1 choose the number of elements from which the combinations have to be created. B2 choose the number of elements in a combination. The size of a combination can't exceed the number of elements in a population.
B3 calculates the number of possible combinations based on the parameters in cells B1 and B2B4 provides a switch by doubleclicking to display the combinations as numbers or with the labels in range A7:A32. If cell B1 isn't empty and cell B2 gets a value the execution of macro M_snb will be triggered. This macro generates the combinations. The result of all combinations is displayed from cell D1 onwards. Please note that the number of combinations can quickly exceed the number of rows in an Excel worksheet. In such a case, the macro will not be executed The worksheet 'formulae' contains a 30 x 30 table in area AT1:BX31 indicating the number of combinations of x elements from y elements. The highest number of combinations per population size is marked in light red. All combination numbers that are greater than the number of rows of an Excel worksheet are marked in grey. Macro M_snbThe macro is optimized for looping.The size of the 2dimensional array in which the results must end up is decisive. The number of combinations form the number of 'rows' of the array; the number of elements of a combination forms the second dimension ('columns') of the array. Except for the number of rows of a worksheet, the macro has no limitations. The use of a macro ensures that the size of the file is limited compared to the use of Excel formulas. All calculations are performed in the working memory. The calculation time is negligible compared to the use of Excel formulas. The interaction with the worksheet is minimized: 1 x reading; 1 x writing. Macro M_snb_000This macro follows the algorithm described above very precisely.The macro has a separate code for:
 the first combination  the first position in any combination  the last position in any combination  the positions between the first and last position in a combination If you go through this macro in the VBeditor stepbystep ( F8 ) you will see that The first combination  the first combination gets all 'column numbers' of the array All following combinations The first position  the first position equals the first position of the preceding combination.  if the maximun of the second position in the previous combination has been reached, 1 is added to the first position. The last position  the last position is the last position of the previous combination +1  if the maximum value for the last position has been reached, it gets the value of the previous position in the actual combination + 1. The positions between first and last one  the position is equal to the value of the corresponding position in the preceding combination  if the next position in the preceding combination has reached its maximum value, 1 will be added  if the same position in the preceding combination has reached its maximum value the position gets the value of the preceding position in the actual combination + 1. The macro is an exact VBA translation of the formulas in area D2:H252 of the worksheet 'formula_fixed'. Since the question of combinations is perfectly suited for the use of recursion, this is dealt with in a separate worksheet. For more information on Recursion see Recursion. B1 choose the number of elements from which the combinations have to be created. B2 choose the number of elements in a combination. The size of a combination can't exceed the number of elements in a population.
B3 calculates the number of possible combinations based on the parameters in cells B1 and B2B4 provides a switch by doubleclicking to display the combinations as numbers or with the labels in range A7:A32. If cell B1 isn't empty and cell B2 gets a value the execution of macro M_snb will be triggered. The result of all combinations is displayed from cell D1 onwards. Macro M_snbReads the information in Range A1:B31 in the worksheet.Creates an empty array to store the results Calls the recursione macro M_comb Writes the results in the array into the worksheet. Macro M_comb(c00, y, t, v)Generates the combinations by calling itself.Writes any combination in the array, created in macro M_snb. Keeps track of production progress via a counter. Both macros use the data read in Array sn and Array sp meant to store the results. Therefore both arrays must be declared as 'Private Scope' (available for the entire macromodule). The macro is optimally flexible: it functions regardless of the parameters in B1 and B2. The only limitation is the number of rows of an Excel worksheet. All calculations take place in the working memory. The size of the VBA code is minimal. The interaction with the worksheet is minimal: 1 x reading, 1 x writing. The speed of the macro is negligible compared to the use of Excel formulas. The size of the file is negligible compared to the use of Excel formulas. If you are not looking for a VBA approach that can be used for all kinds of parameters, you can use a macro that is suitable for only one parameter combination. In the worksheet 'VBAfixed' I have included 3 macros to illustrate this: Macro kombi5_10 creates combinations of 5 elements out of 10 elements. Macro kombi6_10 creates combinations of 6 elements out of 10 elements. Macro kombi7_10 creates combinations of 7 elements out of 10 elements. The difference between the macros is only the number of nested loops and the size of the choose function. Using the three examples, you can create your own macro for the parameter combination of your choice. B1 only the value 10 is allowed. B2 only the values 5, 6 and 7 are allowed. B4 switch between digit/label display using doubleclick. Setting up this macro requires a separate macro for each parameter combination. The number of rows of a worksheet is the limitation of the macro. All calculations take place in the working memory. The size of the VBA code is minimal. The interaction with the worksheet is minimal: 1 x reading, 1 x writing. The speed of the macro is negligible compared to the use of Excel formulas. The size of the file is negligible compared to the use of Excel formulas. B1 choose the number of elements from which the combinations have to be created. B2 choose the number of elements in a combination. The size of a combination can't exceed the number of elements in a population.
B3 calculates the number of possible combinations based on the parameters in cells B1 and B2B4 provides a switch by doubleclicking to display the combinations as numbers or with the labels in range A7:A32. The range D1:W3003 contains the formulas to generate all combinations based on the parameters in cells B1 en B2 Teh range AT1:BX31 contains a table summarizing the number of possible combinations of y elements (rows) form a set of n elements (columns). This shows that a large area of the worksheet needs to be filled with formulas in order to be able to display all results. In a large number of cases (shaded in grey), the size of the worksheet is even too small. I've limited myself to 3003 rows. (combinations of 5 out of 15 or 10 out of 15). Practice has shown that expansion of the D1:W3003 area exponentially increases the size of the file and increases the calculation time accordingly. Not to be recommended therefore. In principle, it is possible to produce the desired combinations with relatively simple formulas. An example of this can be found in the worksheet 'formula_fixed'. The formulas in D2:W3003 are formulated in such a way that they can be extended via Autofill from column W to column D and from row 2 to the last row of the worksheet. This makes the formulas more complex. They are suitable for producing combinations from up to 20 elements The number or label display is not built into the formulas. The formula uses the values in preceding rows. The formula would become unnecessarily complex and the calculation time would therefore be longer. Instead, area Y1:AR3003 contains formulas to display the label values matching D2:W3003. You can use Range D2:W3003 for all cases in which no more than 3003 combinations have to be generated. The Range D2:H252 contains the formulas for the combinations of 5 elements out of 10. In area D1:H1, the numbers 0, 1, 2, 3, 4 are 'hard coded'. The formulas are not dependent on the values in cells B1 and B2. The formula in cell D1 applies only to the 1st position. You can only pull (autofill) it 'down', not to the left nor to the right. The same goes for the formula in cell H2. In the formulas in columns E, F and G, the maximum values for each position are 'hard coded'. Therefore, these formulas can only be extended vertically. In the macromodule of the worksheet 'VBA' the formulas from area D2:H252 have been translated into VBA in Macro M_snb_000. The doubleclick switching function in cell B4 works for the formulas in Range J1:N252. You can use the formulas in this worksheet as examples to create a worksheet for your own fixed parameter combination. 