Forum Discussion

Peter1550's avatar
Peter1550
Copper Contributor
Jun 19, 2019

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 

excel
Formulas and Functions

7 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jun 21, 2019
    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.
    • Peter1550's avatar
      Peter1550
      Copper Contributor
      Jun 21, 2019

      JKPieterse 

       

      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

      • crybloodwing's avatar
        crybloodwing
        Copper Contributor
        Jun 21, 2019

        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

Resources