MrExcel Publishing
Your One Stop for Excel Tips & Solutions

All the combinations possible


Posted by Don Levesque on January 14, 2002 7:35 AM

I've got cell A1 with a total amount and in B2 to H2 I've got specific numbers.

Is there a formula or macro I can build to find all the possible combinations of B2 to H2 that would give me the amount of A1 and write these combination in another section of my sheet.

Example: A1=12 B2=2 C2=3 D2=4 E2=5 F2=6

The possible combinations are: 6xB2, 4xC2, 3xD2, 2xF2, 1xB2+2xE2, 2xB2+2xD2, and so on.

I'm using Excel 97.

Thanks


Posted by Tom Dickinson on January 14, 2002 6:42 PM

Copy this macro. There should be a way to declare the arrays so they can be used in the FOR loops, but I don't remember it at the moment. The macros will list the possibilites starting in cell A2 and go down from there.

Dim ColB, ColC, ColD, ColE, ColF, ColG, ColH As Integer
Dim RwCtr, Ttl, AVal, CntEm, ColAmt(7) As Integer
Dim Strng As String
Sub Factors()
AVal = Range("A1")
RwCtr = 2
For ColB = 0 To AVal
ColAmt(1) = ColB
If NeedMore(1) Then
For ColC = 0 To AVal
ColAmt(2) = ColC
If NeedMore(2) Then
For ColD = 0 To AVal
ColAmt(3) = ColD
If NeedMore(3) Then
For ColE = 0 To AVal
ColAmt(4) = ColE
If NeedMore(4) Then
For ColF = 0 To AVal
ColAmt(5) = ColF
If NeedMore(5) Then
For ColG = 0 To AVal
ColAmt(6) = ColG
If NeedMore(6) Then
For ColH = 0 To AVal
ColAmt(7) = ColH
If Not (NeedMore(7)) Then
ColH = AVal
End If
Next
Else
ColG = AVal
End If
Next
Else
ColF = AVal
End If
Next
Else
ColE = AVal
End If
Next
Else
ColD = AVal
End If
Next
Else
ColC = AVal
End If
Next
Else
ColB = AVal
End If
Next
End Sub
Function NeedMore(EndCol) As Boolean
NeedMore = True
If ColAmt(EndCol) > 0 And Range("A2").Offset(0, EndCol) > 0 Then
Ttl = 0
For CntEm = 1 To EndCol
Ttl = Ttl + ColAmt(CntEm) * Range("A2").Offset(0, CntEm)
Next
If Ttl >= AVal Then
NeedMore = False
End If
If Ttl = AVal Then
Strng = Empty
For CntEm = 1 To EndCol
If ColAmt(CntEm) > 0 And ColAmt(CntEm) <= AVal Then
If Strng <> Empty Then
Strng = Strng & "+"
End If
Strng = Strng & ColAmt(CntEm) & "x" & Chr(65 + CntEm) & "2"
End If
Next
Range("A" & RwCtr) = Strng
RwCtr = RwCtr + 1
End If
End If
End Function

Posted by Don Levesque on January 17, 2002 10:34 AM

THANKS!! It works great