# Conditional Sum and Weighted Average using VBA

#### dlilienthal

##### New Member
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
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``````

#### dlilienthal

##### New Member
Thank you. That's exactly what I needed!

Replies
4
Views
617
Replies
1
Views
846
Replies
3
Views
900
Replies
10
Views
254
Replies
5
Views
544

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.

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?

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