formula -too slow

[Wanna Learn]

Hello I have Excel 2002
I have a report that is imported from another system
col A has the name of the rep, col B has the ID no., col C has the order
type
and col D has the number of calls received for the order type
in column E I have this formula =IF(A1=A2,D1+D2,"0")
Then I go back and delete the row that has the duplicate name

Problem
Is there a faster way to do this, ? Sometimes the report has over 500 lines
and it takes a while to delete the rows thanks in advance

 

[John Bundy]

You can use almost the same formula to do it, if it is sorted by rep then
=IF(A1=A2,1,"") this will put a 1 in all dupes, copy paste special values the
whole column, then sort and delete all of the rows with a 1.

 

[Don Guillett]

Copy this macro to a module and execute from the sheet with the data. It
adds it up and deletes the rows for you.

Sub AddUpColDandDeleteDups()
For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1
If Cells(i - 1, "a") = Cells(i, "a") Then
Cells(i - 1, "d") = Cells(i - 1, "d") + Cells(i, "d")
Rows(i).Delete
End If
Next i
End Sub

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm