Jun 19 2019 09:27 AM
Hello Experts,
What would be the formula for Excel to list all possible combinations of 5 from the set of 6?
I have attached an example of the result I am looking to achieve.
Thanks in advance!
Regards,
Peter
Jun 21 2019 02:35 AM
Jun 21 2019 03:08 AM
Hi Jan,
I have attached another image, as you can see there is a pool of 7 numbers in separate columns. using COMBIN formula will say there are 21 possible combinations of 5 numbers from the pool of 7.
I am looking for a formula that will list those combinations.
Thanks.
Regards,
Peter
Jun 21 2019 07:22 AM - edited Jun 21 2019 07:44 AM
I found this link online which may help. It is basically exactly what you want but it uses pizza ingredients. There may need to be some changes added to sum the numbers. I can try it out myself I you want. I think the VBA code listed is probably needed.
https://www.get-digital-help.com/2015/02/26/return-all-combinations/
Update! Got it to work.
1. Type in the numbers, then press alt+f11 to go to VBA editor.
2. Insert -> Module and paste the code below in.
3. Go back to Excel and choose 21 rows and 5 columns like for me I did E3-I23
4. Type in the combination equation, hold down ctl+shift and press enter.
Here is the VBA Code
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 Function
Function 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 Function
Jun 21 2019 07:49 AM
I have tried this software but it doesn't work for this.
I find it very strange that excel can calculate all possible combinations but there is no formula to list those combinations.
Jun 21 2019 07:52 AM - edited Jun 21 2019 07:55 AM
@Peter1550It did list the combinations though...like isn't the list of numbers such as in the picture in the E3-I23 cells what you mean by listing? What specifically are you looking for from the pictures you posted? Which cells? And the VBA editor software?
Jun 21 2019 09:09 AM
Jun 21 2019 09:22 AM
Strangely, I receive the result like on the picture, not too sure what did I do wrong here?