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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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,642
Messages
5,524,042
Members
409,556
Latest member
gimbox16

This Week's Hot Topics

Top