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?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,982
Members
430,100
Latest member
namhnz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top