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

### 33 Responses to “Return all combinations”

### Leave a Reply

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

<code>your formula</code>

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

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

VBA 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

Hi oscar.

I need an excel macro that print all posible combinations. I have an array from 1 to 42. An i want to pick 6 numbers with no repetitions. Can you help?

Thanks

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!

Hello,

How can I get excel to determine all combinations for a specific number within a 12(rows) x 4(columns) table. The tricky part is I am only interested in the combinations for numbers connecting to the selected value. See example blow;

If my specific value is 1(third row)then I would be interested in listing all 4 digit combinations starting with a number connected to it in all directions.

(down) (up)

1,5,9,3 1,7,3,2

1,5,9,2 1,7,3,4

1,5,9,4 1,7,8,4

1,5,9,8 1,7,6,2

1,5,9,0 1,7,2,1

1,5,6,0 1,7,6,0

1,5,6,2

1,5,4,3

1,5,4,7

12 x 4 table

1234

5678

9012

3456

7890

1234

5678

9012

3456

7890

1234

5678

Hi Oscar,

Your Macro is great. I am creating a large number of combination. 90 items, 9 combined. Understandably, it takes a loooong time and often runs out of memory. Is there a way for me to alter your Macro so that i can do it in pieces?

In other words, can i alter it so that instead of the array starting at

1 2 3 4 5 6 7 8 9

1 2 3 4 5 6 7 8 10

that it starts at the 2s, like

2 3 4 5 6 7 8 9 10 ?

Thank you very much

Hi oscar,

Also in addition to starting it from the 2's like i mention above (previous email), can i also alter the macro to STOP generating after it is done with the 2's?

hope that makes sense.

Thanks :)

Thanks so much Oscar! This was exactly what I needed.

Travis,

Thank you for commenting.

Hi Oscar, thanks a lot for the codes.

I have a problem with the volume of data I'm dealing with, it's a combination of 5 out of 160 data points, making around 800 million groups(800 sheets)!

I tested your code for a combination of 5 out of 100, and excel showed up with an error saying there is not enough resources!

What can I do to capture all the combinations?!

thanks.

Keivan,

You could save each sheet as a workbook as they are created, however you would end up with 800 workbooks.

Would that work?

Thank you for this code! Can your function be adapted to to see all possible combinations of 7 items (ingredients), not just combinations of 5 of the 7, for example. (And I do want combinations, not permutations.)

Thank you for any suggestions!

Sally

Use the COMBIN function in excel to see how many combinations you get with 7 values out of 7 values.

=COMBIN(7,7) returns 1.

I think you are looking for permutations?

Excel udf: List permutations without repetition

Excel udf: List permutations with repetition