Sum of columns

[djeerd]

I have a list of jobs, job extras and amounts. I need to get totals for each job and job extra. See job 123 and job 123, extra 1.

This is what I have,

A B C
Job Extra Amount
1 123 100
2 124 50
3 123 30
4 125 80
5 123, 1 20
6 126 30
7 123, 2 60
8 127 10
9 123, 3 10
10 123 40
11 123, 1 10


This is wwhat i need. totals for each job and each extra

A B C
Job Extra Amount
1 123 170
2 124 50
3 125 80
4 123, 1 30
5 126 30
6 123, 2 60
7 127 10
6 123, 3 10

 

[pboltonchina]

Try this on a copy of your data

Sub MergeItems()
'Jerry Beaucaire  3/11/2010    (updated 6/21/2010)
'Merge all QTY columns for same items
Dim LastRow As Long, Rw As Long
Dim LastCol As Long, Col As Long
Dim DelRNG As Range
Application.ScreenUpdating = False

LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Set DelRNG = Range("A" & LastRow + 10)

For Rw = 2 To LastRow
    If Application.WorksheetFunction.CountIf(Range("A2:A" & Rw), _
        Range("A" & Rw)) > 1 Then
            Set DelRNG = Union(DelRNG, Range("A" & Rw))
    Else
        For Col = 2 To LastCol
            Cells(Rw, Col) = Application.WorksheetFunction.SumIf(Range("A:A"), _
                Range("A" & Rw), Columns(Col))
        Next Col
    End If
Next Rw

DelRNG.EntireRow.Delete xlShiftUp
Set DelRNG = Nothing
Application.ScreenUpdating = True
End Sub

 

[djeerd]

Thank you for your quick response. Let me give you some additional info that i did not put in the origianl post. The extras are Alpha and Numeric, varies on Job, and I need to have the totals for each extra.

The code you sent me sums the jobs and extras together (ignores the Alpha extras). So in my example when i run the macro it shows 123 in column A, 4 in column B and the sum of all amounts for 123 in column C, 270.

 

[djeerd]

correction: extras are alpha OR numeric (one or the other but not both).