I'm sure this is very easy, but...

[bigmacfann]

What I am trying to do is find out how many combinations of 5 numbers from 1-9 equal 13 when added up. For example, 1+1+1+1+9=13, 1+2+1+1+8=13, 1+3+1+1+7=13, etc. However, I don't want the combinations that are the same as the ones before such as 1+1+1+1+9 is the same thing as 9+1+1+1+1, 1+9+1+1+1. I would only want 1 combination total with four 1's and one 9, three 1's a 2 and an 8, and so on... Is there a macro or possibly a function in excel that will do this? I should have stayed awake in algebra when the teacher was teaching permutations and combinations! I would want the combinations to show up for me automatically starting at A1. A1=1, B1=1, C1=1, D1=1, E1=9. then the next combination starting at A2. Can someone help me with this? Thank you in advance.

 

[bigmacfann]

P.S.

I've been using a macro created by "pgc01" to create all combinations WITHOUT repetition, it looks like this:

Sub Combinations()
Dim rRng As Range, p As Integer
Dim vElements, lRow As Long, vresult As Variant

Set rRng = Range("A1", Range("A1").End(xlDown)) ' The set of numbers
p = Range("B1").Value ' How many are picked

vElements = Application.Index(Application.Transpose(rRng), 1, 0)
ReDim vresult(1 To p)
Columns("C").Resize(, p + 1).Clear
Call CombinationsNP(vElements, p, vresult, lRow, 1, 1)
End Sub

Sub CombinationsNP(vElements As Variant, p As Integer, vresult As Variant, lRow As Long, iElement As Integer, iIndex As Integer)
Dim i As Integer

For i = iElement To UBound(vElements)
vresult(iIndex) = vElements(i)
If iIndex = p Then
lRow = lRow + 1
Range("C" & lRow).Resize(, p) = vresult
Else
Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
End If
Next i
End Sub

-------------------------------------------------------

What I need is to find all combinations WITH repetition. For example: if I have A1=1, A2=2, A3=3... A9=9 and B1=5 I get 126 combinations but I need the integers in column A to be able to repeat themselves so instead of my first combination being 1 2 3 4 5, it should be 1 1 1 1 1. The next should be 1 1 1 1 2, then 1 1 1 1 3 and so on. But what I don't want would be 2 1 1 1 1 or 3 1 1 1 1 or 1 2 1 1 1 or 1 3 1 1 1 etc because I already have a combination of four 1's and a two or a three. Is it possible to modify this macro to achieve the results I'm looking for? Thanks!

 

[HarryS]

Try something like this

Private Sub CommandButton1_Click()
    outrow = 0
    For x1 = 1 To 9
        For x2 = 1 To 9
            For x3 = 1 To 9
                For x4 = 1 To 9
                    For x5 = 1 To 9
                        total = x1 + x2 + x3 + x4 + x5
                        If total = 13 Then
                            outrow = outrow + 1
                            Cells(outrow, 1) = x1
                            Cells(outrow, 2) = x2
                            Cells(outrow, 3) = x3
                            Cells(outrow, 4) = x4
                            Cells(outrow, 5) = x5
                              Cells(outrow,6)= total

                        End If
                    Next x5
                Next x4
            Next x3
        Next x2
    Next x1

End Sub

These are not combinations. You should have stayed awake during Maths.
Combinations 5 from 9 are like 2,4,5,6,8 with no repeats and none can add up to 13 as the lowest possible sum is 1,2,3,4,5 =15

 

[HarryS]

Sorry they repeat which was one of your not to do's
It must be Sat and that James Boag premium beer has been to good.

If someone sober does not give a solution I will think on it further.

 

[HarryS]

Alcohol rots the brain

Try this

Private Sub CommandButton1_Click()
 Dim used(300, 9) As Integer
 Dim group(9) As Integer
 
 outrow = 0
 cc = 0
 
    For x1 = 1 To 9
        For x2 = x1 To 9
            For x3 = x2 To 9
                For x4 = x3 To 9
                    For x5 = x4 To 9
                        total = x1 + x2 + x3 + x4 + x5
                        If total = 13 Then
                             outrow = outrow + 1
                            Cells(outrow, 1) = x1
                            Cells(outrow, 2) = x2
                            Cells(outrow, 3) = x3
                            Cells(outrow, 4) = x4
                            Cells(outrow, 5) = x5
                        End If
                    Next x5
                Next x4
            Next x3
        Next x2
    Next x1

End Sub
 It has to be better than the last mess

 

[HarryS]

forget about he two DIM statemets. They were a Dim brain trying to work.