- 551K Members
- 5,743 Online
- 660K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: Listing possible combinations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Labels:

7 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2019 02:35 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2019 07:22 AM - edited 06-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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2019 07:52 AM - edited 06-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?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2019 09:09 AM

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-21-2019 09:22 AM

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

Related Conversations

Counting number of times different combinations occur

albion1
in
Excel
on
10-28-2019
96
Views

0 Likes

2 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft