• 465K Members
• 10.6K Online
• 563K Conversations

## Listing possible combinations

Occasional Contributor

# Listing possible combinations

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

# Re: Listing possible combinations

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.

# Re: Listing possible combinations

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

# Re: Listing possible combinations

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.

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

# Re: Listing possible combinations

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

# Re: Listing possible 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?

# Re: Listing possible combinations

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

# Re: Listing possible combinations

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies