Conditional Sum and Weighted Average using VBA

[dlilienthal]

I am trying to write a VBA routine to combine certain rows using Excel 2007. I have a table with columns PartNo, Type, Customer, Data, Qty, Price. Data is sorted by PartNo, Type, Customer. When multiple rows have the same PartNo, Type, Customer, I want to combine these rows into a single row with a sum Qty and weighted average Price. Sample data at http://www.daltech.info/SampleData1.xlsx. Is there a straightforward way to do this?

 

[xld]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
    With ActiveSheet
        
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = LastRow To 2 Step -1
        
            If .Cells(i, "A").Value = .Cells(i - 1, "A").Value And _
                .Cells(i, "B").Value = .Cells(i - 1, "B").Value And _
                .Cells(i, "C").Value = .Cells(i - 1, "C").Value Then
                
                .Cells(i - 1, "F").Value = ((.Cells(i - 1, "E").Value * .Cells(i - 1, "F").Value) _
                                         + (.Cells(i, "E").Value * .Cells(i, "F").Value)) _
                                         / (.Cells(i - 1, "E").Value + .Cells(i, "E").Value)
                .Cells(i - 1, "E").Value = .Cells(i - 1, "E").Value + .Cells(i, "E").Value
                .Rows(i).Delete
            End If
        Next i
    End With
    
End Sub

 

[dlilienthal]

Thank you. That's exactly what I needed!