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.
Read this article to learn more about permutations:
Permutations with and without repetition
I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]
Permutations with and without repetition
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 have only one combination. I guess only mathematicians use the word permutations, everyone else uses the word combinations even if they talk about permutations.
The following article demonstrates a problem solved with combinations, the order is not important:
Identify numbers in sum using Excel solver
Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]
Identify numbers in sum using Excel solver
User defined functions
The following User Defined 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:
You need to copy the VBA code below to your workbook before you try the above User Defined Function.
Watch this video to learn more about the UDF
How to enter an array formula
- Select cell range E3:I23
- Press with left mouse button on 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.
A beginners guide to Excel array formulas
Array formulas allows you to do advanced calculations not possible with regular formulas.
A beginners guide to Excel array formulas
VBA Code
'Dimension public variable and declare data type Public result() As Variant 'Name User Defined Function Function Combinations(rng As Range, n As Single) 'Save values from cell range rng to array variable rng1 rng1 = rng.Value 'Redimension array variable result ReDim result(n - 1, 0) 'Start User Defined Function Recursive with paramters rng1, n, 1, 0 Call Recursive(rng1, n, 1, 0) 'Remove a column of values from array variable result ReDim Preserve result(UBound(result, 1), UBound(result, 2) - 1)¨ 'Transpose values in variable result and then return result to User Defined Function on worksheet Combinations = Application.Transpose(result) End Function
'Name User Defined Function and paramters Function Recursive(r As Variant, c As Single, d As Single, e As Single) 'Dimension variables and declare data types Dim f As Single 'For ... Next statement For f = d To UBound(r, 1) 'Save value in array variable r row f column 1 to array variable result row e and last column result(e, UBound(result, 2)) = r(f, 1) 'If ... Then ... Else ... End If statement 'Check if variable in e is equal to c -1 If e = (c - 1) Then 'Add another column to array variable result ReDim Preserve result(UBound(result, 1), UBound(result, 2) + 1) 'For ... Next statement For g = 0 To UBound(result, 1) 'Save value in array variable result row g second last column to result row g last column result(g, UBound(result, 2)) = result(g, UBound(result, 2) - 1) Next g 'Continue here if e is not equal to c - 1 Else 'Start User Defined Function Recursive with parameters r, c, f + 1, e + 1 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)
- Press with left mouse button on "Insert" on the menu
- Press with left mouse button on "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
Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in […]
List permutations no repetition [UDF]
This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]
List permutations no repetition [UDF]
List permutations with repetition [UDF]
This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]
List permutations with repetition [UDF]
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
Count cells based on a condition and month
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
How to use Pivot Tables – Excel’s most powerful feature and also least known
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.
Change chart series by clicking on data [VBA]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
This article demonstrates a formula What's on this page Question Formula Array formula Explaining array formula Get the Excel File […]
59 Responses to “Return all combinations”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I am working on a udf version without Redim Preserve.
Public result() As Variant Function Combinations(rng As Range, n As Single) Dim b As Single rng1 = rng.Value b = WorksheetFunction.Combin(UBound(rng1, 1), n) ReDim result(b, n - 1) Call Recursive(rng1, n, 1, 0, 0) For g = 0 To UBound(result, 2) result(UBound(result, 1), g) = "" Next g Combinations = result End FunctionFunction Recursive(r As Variant, c As Single, d As Single, e As Single, h As Single) Dim f As Single For f = d To UBound(r, 1) result(h, e) = r(f, 1) If e = (c - 1) Then For g = 0 To UBound(result, 2) result(h + 1, g) = result(h, g) Next g h = h + 1 Else Call Recursive(r, c, f + 1, e + 1, h) End If Next f End FunctionHi Oscar,
Could we extend this formula to the next page and so on so that the combinations continues to be generated from where it left off in the previous page, because the results stops being generated at row 1048576 (last row in excel). It would be a great help, thanks.
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 https://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:
https://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:
Sub ListPermut() Dim ws As Worksheet, Ans As String, ans1() As String, digits As Integer Dim num As Integer, p As Long, i As Long, t As Long Dim rng() As Long, c As Long, rng1() As String Set ws = Sheets.Add Ans = InputBox("How many items?") digits = InputBox("How many combined?") For i = 1 To Ans tmp = tmp & i & "," Next i ans1 = Split(Left(tmp, Len(tmp) - 1), ",") num = UBound(ans1) + 1 p = Application.WorksheetFunction.Combin(num, digits) ReDim rng(1 To digits) ReDim rng1(1 To digits) For c = 1 To digits rng(c) = c Next c i = 0 Application.ScreenUpdating = False Do Until (i + t) = (p - 1) For c = LBound(rng1) To UBound(rng1) rng1(c) = ans1(rng(c) - 1) Next c ws.Range("A1").Resize(, digits).Offset(i) = rng1 rng(digits) = rng(digits) + 1 If rng(digits) > num Then For c = digits - 1 To 1 Step -1 If rng(c) <> num + c - digits Then rng(c) = rng(c) + 1 Exit For End If Next c For d = c + 1 To digits rng(d) = rng(d - 1) + 1 Next d End If i = i + 1 If i = 1000000 Then Set ws = Sheets.Add t = t + 1000000 i = 0 End If Loop For c = LBound(rng1) To UBound(rng1) rng1(c) = ans1(rng(c) - 1) Next c ws.Range("A1").Resize(, digits).Offset(i) = rng1 Application.ScreenUpdating = True End SubType 96 in the first inputbox and 3 in the second.
Macro inserts a new sheet and returns all combinations (rep not allowed).
Get the 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
Hello Ajit, were you successful on the use of the sugessted macro for your 96c3?
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!
How exactly do you use INDEX in this case?
Hi, how can we use index i ths case to get the original data like cheese, ham etc.
Thanks
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
Thanks for great function. How can I list the combination into row only. Example combinations(A1:A5,2) will generate result into row 1, 2, 3, 4 (1 column only)
Appreciate your job.
Hi Oscar,
Good day.
How can i continue the list after #21, as i have total of 84 combinations.
Thank you
Hello Oscar,
can you please help me out with the following part of my VBA code:
.....
Set BranchXX = Range(Cells(3, 2), Cells(fin, 3))
Set BranchKK = Range(Cells(3, 1), Cells(nr_row, 1))
BranchXX.Select
Selection.FormulaArray = "=Combinations(BranchKK,2)"
I use your function: Combinations and the above code gives an error.
Although if I change the inputs of the function like below everything works fine:
...
Set BranchXX = Range(Cells(3, 2), Cells(fin, 3))
'Set BranchKK = Range(Cells(3, 1), Cells(nr_row, 1))
BranchXX.Select
Selection.FormulaArray = "=Combinations(RC[-1]:R[5]C[-1],2)"
Although I want the first argument of the function to be variable(depending on nr_row ) and need to implement the code like the way I did it firstly
Thank you very much!!
Please provide me with your email address to send you my request
Hi Oscar, I need help with trying to create a number generator that has specific filters for 5-45 numbers with no repeats!!
Is there a you tube step-by-step instruction tutorial on how a beginner can go about creating a number generator for a lottery matrix of 5/45, 5/69,5/70 and 6/39 and so forth ?
hi oscar
i need macros code for 22 alphabets (array) in 11 combination in excel
Sir,
When I Set Range (22,11) in Combinations UDF it show #VALUE. how can i solve.
KP,
try the macro I posted on the December 5, 2016 at 1:59 pm. The Combinations UDF has a limit of 65532 rows.
Hi, I have gone through a lot of such posts. But could not find the dumbest VBA combination algo so far. What I need is this, the combination generator for N (4) picked items with K (2) types (obvious repeat)
the total combination set becomes N+1 = 5
AAAA
AAAB
AABB
ABBB
BBBB
Thanks in advance.
123
124
125
234
235
345
how we build it in query access
not sql
but query eccess
Hİ
!!!!
I am writing to you from Turkey. I'm a student and I have an urgent homework. I have 290 rows of data in my hand.
This combination of 290 data needs to derive 2,3,4,5,6,7,8 combinations.Sample data:
1. a01b
2. a02B
3. a03c
4.
..
290.x02w
data such as.
my excel knowledge is weak. If you have a macro formula, please write to me. can you please help me?
for excample:
a01b-a01c : Combination of 2
...
a01b-a01c-a01d-a01h-a01f : combination of 5
...
a01h-a01f-a01b-a01c- a02c-a03a-a02s-a012 : combination of 8;
That's the way I need it.
Please
Please
waiting for help
[…] shown here in cells A3 through G5042. This file already has what you need, but here is how you can create your own set of combinations for different numbers of […]
following on from Arits VBA code,
How would you add a rule that the combinations cant be more than 4 consecutive numbers in the generated sequence?
Permutation and Group Speed Dating Scenario.
I'm looking to use a modified permutation to create a seating list.
Input is the number tables 5, number seats 5 and list of members 10.
Input
Member 1
Member 2
Member 3
Member 4
Member 5
Member 6
Member 7
Member 8
Member 9
Member 10
Needed Output where the numbers in the round columns are the table assigments for each member as we rearrange members every 15 minutes. Every members should only meet every other member 1 time.
Members Round 1 Round 2 Round
Member 1 1 2 3
Member 2
Member 3
Member 4
Member 5
Member 6
Member 7
Member 8
Member 9
ALL COMBINATIONS
IS WORKING NICE HOWEVER WHEN I TYPE 26 AND 5 WERE YOU COMMENT TO ONE OF THE USERS IS GIVING ME ALL THE COMBINATIONS FROM 1 TO 26 BUT I NEED FOR CERTAIN NUMBERS AND HOW MUCH I TRIED I JUST COULD'NT MODIFY THE CODE TO FIT MY PURPOSE
PLEASE IF YOU CAN GIVE ME AN IDEA ON HOW TO ADD THE RIGHT SHEET AND THE RIGHT RANGE FOR MY NUMBERS WILL BE GREAT AS I NEED ALL COMBINATIONS FOR A CERTAIN BLOCK OF NUMBERS
MANY THANKS
Hi Sir,
Thanks, but here my query is, I have name of 22 employees and want to combinations of 11 of them. please suggest now how can i use the code for that, as per combinations formula total combinations are 705432
thanks,
Gaurav Anand
Hi There,
How can I add another Ingredients 6 in UDF. Thanks
Thanks for a wonderful post. I really appreciate your coding knowledge.
This coding was working like pro.
After after a recent windows update it's working only for maximum 25 numbers with a group of 5.
If the number is more than 25 and the group is 5 then the file get crashed, and gives wrong results.
I request you to have a look.
Hi, I need to select 11 persons out of 22 persons, I need all combinations, I tried everything as mentioned in the blog and comments, but still can't do it, name error comes, can anyone please help?
Hi Oscar, thanks for your great work. HI, I am trying to use this combinations functions on a bigger database. I have 90 items and I want to combine 5 at a time. I tried the macro written out for Ajit on December 5 and the feedback i got is #NAME?. Tried it severally but still the same. How do i tackle it?
Stella,
90 items and 5 chosen returns 43 949 268 combinations.
That is more rows than a worksheet can contain.
The UDF is for smaller combinations.
The #NAME error suggests that you didin't copy the VBA code to a module in your workbook?