summing like records
Posted by Colin on December 14, 2001 5:22 AM
I am working on a project and I need some way for excel to look through a list of 8000 records and if two or more rows have equal values in columns 1 and 2, then sum the values of column 3 into one row. The old cell contents can then be deleted or whatever.
I really need help with this one.
Thanks,
Colin
Posted by Tom Dickinson on December 14, 2001 6:26 AM
Try this:
Sub Macro1()
Dim Cnt1, Cnt2 As Integer
Cnt1 = 1
Do While Range("A" & Cnt1 + 1) <> Empty
Cnt2 = Cnt1 + 1
Do While Range("A" & Cnt2) <> Empty
If Range("A" & Cnt1) = Range("A" & Cnt2) And Range("B" & Cnt1) = Range("B" & Cnt2) Then
Range("C" & Cnt1) = Range("C" & Cnt1) + Range("C" & Cnt2)
Range("A" & Cnt2 & ":C" & Cnt2).Delete shift:=xlUp
End If
Cnt2 = Cnt2 + 1
Loop
Cnt1 = Cnt1 + 1
Loop
End Sub
I have assumed that the data starts in cell A1, and that there are no blanks. If there are blanks, you may want to switch from a "Do" loop to a "For" loop.
Good luck.
Posted by Mark W. on December 14, 2001 6:44 AM
If A1:C5 contains...
{"A","B",1
;"B","C",2
;"C","C",3
;"A","B",4
;"B","B",5}
The array formula...
{=IF(SUM(($A$1:$A$5=$B$1:$B$5)+0)>1,SUM(($A$1:$A$5=$B$1:$B$5)*$C$1:$C$5),0)}
will produce 8.
Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.
Posted by Juan Pablo G. on December 14, 2001 7:48 AM
Why not just
=SUMPRODUCT((A1:A5=B1:B5)*C1:C5)
Juan Pablo G.
Posted by Mark W. on December 14, 2001 7:58 AM
Because...
...his conditional, "if two or more rows..."
requires an array formula anyway. And, I'm
reluctant to use SUMPRODUCT with only 1
argument since Help specifies that its
arguments should be "2 to 30 arrays..."
I'd be more inclined to use...
=SUMPRODUCT((A1:A5=B1:B5)+0,C1:C5) Why not just =SUMPRODUCT((A1:A5=B1:B5)*C1:C5) Juan Pablo G. : If A1:C5 contains... : {"A","B",1
Posted by Sunitha on December 14, 2001 8:16 AM
Re: Because...
I don't think this was my question. Somehow the worong question got pasted here. Can someone answer my question?
