Jennifer,
Here is another approach.
(add a "Max" formula in a column to complete your request)
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
'-----------------
'Combinations sub by John Warren March 21, 2001
'Modified by Jim Cone April 06, 2006
'Calls Comb2 sub.
'Creates the list in a single column.
'Select the top cell of the column then run code.
Sub Combinations()
Dim n As Variant
Dim m As Variant
ReStart:
n = InputBox("Number of items?", "Combinations")
If Len(n) = 0 Then Exit Sub
m = InputBox("Taken how many at a time?", "Combinations")
If Len(m) = 0 Then GoTo ReStart
Application.ScreenUpdating = False
Comb2 n, m, 1, vbNullString, ActiveCell
Application.ScreenUpdating = True
End Sub
'Comb2 sub by John Warren March 21, 2001
'Modified by Jim Cone April 06, 2006
'Generate combinations of integers k..n taken m at a time, recursively.
Sub Comb2(ByVal n As Integer, ByVal m As Integer, ByVal k As Integer, _
ByVal s As String, ByRef rng As Excel.Range)
If m n - k + 1 Then Exit Sub
If m = 0 Then
rng.Value = RTrim$(s)
Set rng = rng(2, 1)
Exit Sub
End If
Comb2 n, m - 1, k + 1, s & k & " ", rng
Comb2 n, m, k + 1, s, rng
End Sub
'--------------------------
'Jim Cone - San Francisco, USA - 04/06/2006
'User must select the single column of values created by
'the Combinations sub before running this code.
'Assumes the matrix is on "Sheet2" in A1:F13 and that the
'Number of Items is 13 and are taken 5 at a time.
Sub GetColumnMaxtrixTotals()
Dim rngCell As Excel.Range
Dim rngMatrix As Excel.Range
Dim arr As Variant
Dim lngR As Long
Dim lngC As Long
Dim lngSum As Double
Set rngMatrix = Worksheets("Sheet2").Range("A1:F13")
Application.ScreenUpdating = False
For Each rngCell In Selection.Cells
arr = Split(rngCell.Value, " ")
For lngC = 1 To 6
For lngR = 0 To 4
lngSum = lngSum + rngMatrix(arr(lngR), lngC).Value
Next
rngCell.Offset(0, lngC).Value = lngSum
lngSum = 0
Next
Next
Application.ScreenUpdating = True
Set rngCell = Nothing
Set rngMatrix = Nothing
End Sub
'----------
"Jennifer Q" wrote in message ...
A user posed this problem and I have no idea how to help him out. Any
suggestions or advice?
Thanks in advance for your help.
Jennifer Q
~~~~~~~
Suppose that I have a matrix that has say 13 rows and 6 columns. I wish to
write an EXCEL program that takes 5 rows at a time and computes the sum of
each column for the resulting matrix. The program is to record the maximum
of these column sums along with the rows for which the computation was made.
The program is to cycle through all 1287 possible combinations of five rows
repeating the column sum computations and recordings as indicated above.
An example matrix is shown below:
| A | B | C | D | E | F
1 | 1 2 1 1 4 8
2 | 5 8 7 4 1 5
3 | 9 6 2 9 9 6
4 | 6 5 5 7 7 7
5 | 11 13 6 3 3 2
6 | 8 7 11 2 5 10
7 | 3 12 4 10 8 4
8 | 4 1 8 12 12 11
9 | 12 3 10 8 10 1
10| 7 9 13 13 6 3
11| 10 10 12 11 2 13
12| 2 4 9 6 13 12
13| 13 11 3 5 11 9