Conditional Sum and Weighted Average using VBA

dlilienthal

New Member
Joined
Jul 29, 2010
Messages
2
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?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,917
Messages
5,525,621
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top