Suggestions Example file
Combinations


1. Combinations

2. Characteristics of combinations

3. Sample file

3.1 Worksheet 'explanation'
3.2 Worksheet 'VBA'
3.3 Worksheet 'VBA-recursion'
3.4 Worksheet 'VBA fixed'
3.5 Worksheet 'formulae'
3.6 Worksheet 'formula_fixed'

1. Combinations

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 VBA-solutions and two formula-based 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")
if 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
next

msgbox mid(c01,2)

Patternanalysis

If 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

3. Sample file

The sample file in this page contains 6 worksheets
- the rotation principle will be illustrated graphically
- and it contains flexible and static VBA-approaches
- flexible and static approaches with Excel-formulae

3.1 Worksheet 'explanation'

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 right-to-left. 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.

3.2 Worksheet 'VBA'

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 B2
B4 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_snb

The macro is optimized for looping.
The size of the 2-dimensional 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_000

This 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 step-by-step ( 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'.

3.3 Worksheet 'VBA-recursion'

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 B2
B4 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_snb

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

3.4 Worksheet 'VBA_fixed'

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 'VBA-fixed' 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.

3.5 Werkblad 'formulae'

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

3.6 Worksheet 'formula_fixed'

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