I'd like to create a spreadsheet that will allow me to find all possible
combinations from 5 columns matching 2 criteria. It is for cell phone rate
plans, so the 5 columns are plans, 3000, 2000, 1350, 900, and 450. The 2
criteria are # of users (plans), and total minutes. I would like to enter
the 2 criteria and get the combinations listed out in rows.

I know enough after that to get it to calculate how much each combination
would cost me.

Any ideas how to do this? Thanks all!

- Jeff

Take a look at this:
http://groups.google.com/group/micro...744bdb640de7bf

Regards,
Ryan---

--
RyGuy


"jhachtel" wrote:

I'd like to create a spreadsheet that will allow me to find all possible
combinations from 5 columns matching 2 criteria. It is for cell phone rate
plans, so the 5 columns are plans, 3000, 2000, 1350, 900, and 450. The 2
criteria are # of users (plans), and total minutes. I would like to enter
the 2 criteria and get the combinations listed out in rows.

I know enough after that to get it to calculate how much each combination
would cost me.

Any ideas how to do this? Thanks all!

- Jeff

Sub Combins7()
'Calculates the sum of the values of all combinations of 7 things
taken 1-7 at a time.
'The 7 individual piece values are assumed to be in cells A1:A7
'Combins7 will place the various combinations in C1:C127

Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim i4 As Integer
Dim i5 As Integer
Dim i6 As Integer
Dim i7 As Integer
Dim iRow As Integer

iRow = 0

'Combin(7,1) (seven things taken 1 at a time)
For i1 = 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A")
Next i1

'Combin(7,2) (seven things taken 2 at a time)
For i1 = 1 To 6
For i2 = i1 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A")
Next i2
Next i1

'Combin(7,3)
For i1 = 1 To 5
For i2 = i1 + 1 To 6
For i3 = i2 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A") _
+ Cells(i3, "A")
Next i3
Next i2
Next i1

'Combin(7,4)
For i1 = 1 To 4
For i2 = i1 + 1 To 5
For i3 = i2 + 1 To 6
For i4 = i3 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A") _
+ Cells(i3, "A") + Cells(i4, "A")
Next i4
Next i3
Next i2
Next i1

'Combin(7,5)
For i1 = 1 To 4
For i2 = i1 + 1 To 4
For i3 = i2 + 1 To 5
For i4 = i3 + 1 To 6
For i5 = i4 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A") _
+ Cells(i3, "A") + Cells(i4, "A") _
+ Cells(i5, "A")
Next i5
Next i4
Next i3
Next i2
Next i1

'Combin(7,6)
For i1 = 1 To 2
For i2 = i1 + 1 To 3
For i3 = i2 + 1 To 4
For i4 = i3 + 1 To 5
For i5 = i4 + 1 To 6
For i6 = i5 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A")
_
+ Cells(i3, "A") + Cells(i4, "A")
_
+ Cells(i5, "A") + Cells(i6, "A")
Next i6
Next i5
Next i4
Next i3
Next i2
Next i1

'Combin(7,7)
iRow = iRow + 1
Cells(iRow, "C") = 0
For i1 = 1 To 7
Cells(iRow, "C") = Cells(iRow, "C") + Cells(i1, "A")
Next i1

End Sub

Thanks guys. I think I bit off more than I can chew. I don't even know what
I'm looking at when looking at the code. I appreciate the responses though.
I'm headed back to math class... ;)

"ytayta555" wrote:

Sub Combins7()
'Calculates the sum of the values of all combinations of 7 things
taken 1-7 at a time.
'The 7 individual piece values are assumed to be in cells A1:A7
'Combins7 will place the various combinations in C1:C127

Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim i4 As Integer
Dim i5 As Integer
Dim i6 As Integer
Dim i7 As Integer
Dim iRow As Integer

iRow = 0

'Combin(7,1) (seven things taken 1 at a time)
For i1 = 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A")
Next i1

'Combin(7,2) (seven things taken 2 at a time)
For i1 = 1 To 6
For i2 = i1 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A")
Next i2
Next i1

'Combin(7,3)
For i1 = 1 To 5
For i2 = i1 + 1 To 6
For i3 = i2 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A") _
+ Cells(i3, "A")
Next i3
Next i2
Next i1

'Combin(7,4)
For i1 = 1 To 4
For i2 = i1 + 1 To 5
For i3 = i2 + 1 To 6
For i4 = i3 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A") _
+ Cells(i3, "A") + Cells(i4, "A")
Next i4
Next i3
Next i2
Next i1

'Combin(7,5)
For i1 = 1 To 4
For i2 = i1 + 1 To 4
For i3 = i2 + 1 To 5
For i4 = i3 + 1 To 6
For i5 = i4 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A") _
+ Cells(i3, "A") + Cells(i4, "A") _
+ Cells(i5, "A")
Next i5
Next i4
Next i3
Next i2
Next i1

'Combin(7,6)
For i1 = 1 To 2
For i2 = i1 + 1 To 3
For i3 = i2 + 1 To 4
For i4 = i3 + 1 To 5
For i5 = i4 + 1 To 6
For i6 = i5 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A")
_
+ Cells(i3, "A") + Cells(i4, "A")
_
+ Cells(i5, "A") + Cells(i6, "A")
Next i6
Next i5
Next i4
Next i3
Next i2
Next i1

'Combin(7,7)
iRow = iRow + 1
Cells(iRow, "C") = 0
For i1 = 1 To 7
Cells(iRow, "C") = Cells(iRow, "C") + Cells(i1, "A")
Next i1

End Sub