# 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

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
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
2
Views
58
Replies
1
Views
40
Replies
8
Views
78
Replies
7
Views
52
Replies
13
Views
145