Listing possible combinations

Copper Contributor

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 

7 Replies
I see you haven't received any responses so far. I tried looking at your sample but had a hard time figuring out what you need from the example. Perhaps you'll have more chance on a solution if you try to give us an example (using a smaller set of numbers) of the expected end result where all possible combinations are shown.

@Jan Karel Pieterse 

 

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

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.

Screenshot (6).png

 

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

@crybloodwing

 

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.

 

 

@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?

@crybloodwing 

 

It didn't work for me I must have done something wrong?

 

 

@crybloodwing 

 

Strangely, I receive the result like on the picture, not too sure what did I do wrong here?