## 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

These functions have a limit of 65532 rows, if you need more read this comment.

### 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

### 23 Responses 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.

**Contact Oscar**

You can contact me through this webpage

I am working on a udf version without Redim Preserve.

Works great; however, I wonder if a modification can be made such

that each item in the list can be used multiple times; e.g for a

list of 1 2 3 4 "=COMBINATIONS(A1:A4,3)" can yield:

111 112 113 114 etc as well

instead of only: 123 124 134 234

Note: Sequence is not significant so 112, 211 and 121 should be considered the same and therefore shown only once.

Further to my request of April 17, I have looked at the ListPermut Function provided at http://www.get-digital-help.com/2010/12/30/excel-udf-list-permutations-with-repetition/.

Although this Function does allow repetitions, it only accepts a single parameter and does not allow specifying the number of terms that form a set. For example, my list contains 8b items while I need to display all permutations/combinations with, say, only 3 out of 8 items at a time.

Found what I am looking for at:

http://www.get-digital-help.com/2011/11/11/select-numbers-in-each-permutation/

Thanks.

Thank you so much this is almost what I need, it only allows cheese to appear in gradient #1 I would like see all variation

Cheese as ingredient 1, cheese as ingredient #2, cheese as ingredient 3 ect ect.

[…] Copy the following code to a module. How to insert a module to a workbook. […]

Hi! Thanks, this is very helpful! However, I tried running this for combinations of 24 choices, with 5 picks [basically, =combin(24,5), and it worked fine. When I tried it for 26 choices, with 5 picks, it doesn't work anymore. I tried it with 25 choices and it works... so I assume it's a limitation issue? Appreciate your help. :)

I have a number say 9347. I need all the 4 digit combinations of the number. ie 24 combinations . Is there any VB program in excel to display all the 24 combinations?

9347

9437

9374...etc

Thanks

Krish

[…] can read about the difference between combinations and permutations here: Return all combinations but in short, the order is important for permutations and not important for […]

How would I add more things to choose from Im trying to do 10 things to select.

john,

Add more ingredients to column B.

Change the custom function to:

=Combinations(B3:B12,5) + CTRL + SHIFT + ENTER

B3:B12 contains 10 things.

Hello, Thanks for sharing.. .. Its almost what I was looking for..but my assignment is slightly tricky..

Only additional query for above is.. Above code generates combination with repetitions ... What if we need to have say set of 10 men & need to form 3 member teams and carryout 9 distinct tasks.(Each man should do all 9 tasks). However no two men should repeat working with another man in another task after working with him once in a task. That's 10 men working in 3member teams working only once with rest of 9 members on the 9 tasks.

Your above code should work fine, if repetition of pairs is handled... Is this is even possible? Thanks

HI, I am trying to use this combinations functions on a bigger database. I have 96 items and I want to combine 3 at a time. So the combinations would lead to 96C3 = 142880. Can you please tell me help me with this?

Thanks,

Ajit

Ajit

The following macro lets you build all combinations:

Type 96 in the first inputbox and 3 in the second.

Macro inserts a new sheet and returns all combinations (rep not allowed).

Download excel *.xlsm file

ListCombinationsv2.xlsm

I am in awe of this macro, Oscar. Thank you very much. It worked like a charm. Many thanks,

Joey

Joey Godalla

Thank you for your comment.

Fantastic, I finally think I'm in the right place...

Yes I need to see the combinations however, my problem has a constraint.

For example there are 50 balls and 10 baskets, each basket can hold a maximum of 7 balls. What are the possible combinations and how can I list them.

Can the vba be linked to a cell that be can changed to to alter the number of balls, baskets, and maximum capacity per basket.

All the best, Oliver

Hi Oscar & all,

This looks perfect for what I am after; however, I am getting an error message when doing the array formula, saying

'Ambiguous name detected: Combinations'

and then a #NAME? error in all output cells (E3:I23).

I have tried re-pasting the VBA etc and no luck - any tips / ideas?

Many thanks in advance,

Kristina

Kristina,

'Ambiguous name detected: Combinations'You have two macros with the same name.

...Trying out the macro written for Ajit on 5/12/16.

This one works great returning combinations of numbers up to the input value - is there any way to amend to return combinations of a certain input range (e.g. CHeese, Ham etc etc) as per the original macro?

THanks again!

Kristina,

Use INDEX function to fetch values from an input range.

Great, thank you! This appears to work now :)

Much appreciated!