## Return all combinations

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What is a combination? To explain combinations I must explain the difference between combinations and permutations. Think of permutations as if the order is important and combinations as if the order is not important. If this is confusing, look at the following examples.

### Example 1 - Permutations

Think of a phone number, each digit can be between 0 to 9 or 10 different values. A five digit phone number contains 100 000 permutations (10x10x10x10x10 equals 100 000).

Now imagine a phone number to a friend or co-worker. If we rearrange the phone numbers, you could possibly call a stranger. The order is **important**.

### Example 2 - Combinations

Imagine you are about to buy a pizza and you can choose from five ingredients, cheese, tomato sauce, onions, ham and mushrooms. It doesn´t matter in what order you say the ingredients. The order is **not important**.

A five digit phone number has 100 000 possible permutations but five out of five pizza ingredients has only one combination. I guess only mathematicians use the word permutations, everyone else use the word combinations even if they talk about permutations.

### User defined functions

The following function lets you for example see all the combinations of pizza ingredients you can choose from if you pick 5 out of 7 ingredients.

**Custom array formula in cell range E3:I23:**

=Combinations(B3:B9,5)

**How to enter an array formula**

- Select cell range E3:I23
- Click in fomula bar
- Enter custom function
- Press and hold CTRL + SHIFT
- Press Enter

If you did it right the formula now begins and ends with curly brackets, like this {=array formula}. They appear automatically, don´t enter the curly brackets yourself.

### VBA Code

Public result() As Variant Function Combinations(rng As Range, n As Single) rng1 = rng.Value ReDim result(n - 1, 0) Call Recursive(rng1, n, 1, 0) ReDim Preserve result(UBound(result, 1), UBound(result, 2) - 1) Combinations = Application.Transpose(result) End Function Function Recursive(r As Variant, c As Single, d As Single, e As Single) Dim f As Single For f = d To UBound(r, 1) result(e, UBound(result, 2)) = r(f, 1) If e = (c - 1) Then ReDim Preserve result(UBound(result, 1), UBound(result, 2) + 1) For g = 0 To UBound(result, 1) result(g, UBound(result, 2)) = result(g, UBound(result, 2) - 1) Next g Else Call Recursive(r, c, f + 1, e + 1) End If Next f End Function

### Where to copy vba code?

- Go to VB Editor (Alt + F11)
- Click "Insert" on the menu
- Click "Module"
- Paste code to code module
- Return to excel

**Tip!** Did you know that excel can calculate the number of combinations for you? Use the COMBIN function:

=COMBIN(7,5) equals 21 combinations.

### Recommended reading

- Select numbers in each permutation
- Excel udf: List permutations without repetition
- Excel udf: List permutations with repetition

### Download excel *.xlsm file

### One Response to “Return all combinations”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**How to upload a file**

Upload file

I am working on a udf version without Redim Preserve.