PDA

View Full Version : Find cells with like values & automatically sum them



Hankins
07-28-2009, 04:58 AM
I have two columns of data that are related. I have a cost code column and a cost column. I may have several rows that share the same cost code. I need to be able to generate a list of the codes used and the total cost for each.

>>> Raw Data

Cost Code ---------- Cost
11007 --------------- $1500
11500---------------- $1000
11007---------------- $10000
10540---------------- $200
11500---------------- $500
This list could be 20 - 30 rows longer

>>> Output
11007---------------- $11500
11500---------------- $1500
10540---------------- $200

Hook a brotha up! Thanks for your help!
:beerchug:

Bob Phillips
07-28-2009, 05:08 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long
Dim cell As Range
Dim sh As Worksheet

Set sh = Worksheets("Sheet2")
With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Rows(1).Copy sh.Rows(1)
NextRow = 2
For i = 2 To LastRow

If IsError(Application.Match(.Cells(i, "A").Value, sh.Columns(1), 0)) Then

.Cells(i, "A").Copy sh.Cells(NextRow, "A")
sh.Cells(NextRow, "B").Formula = "=SUMIF('" & .Name & "'!A:A,A" & NextRow & ",'" & .Name & "'!B:B)"
NextRow = NextRow + 1
End If
Next i
End With

End Sub