Subtotals
Thanks Bob! That did the trick.
"Bob Phillips" wrote:
I am assuming that column G uses a formula to calculate the amount
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 And _
.Cells(i, "D").Value = .Cells(i - 1, "D").Value Then
.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
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"basic" wrote in message
...
What I am trying to do is take the following data and create totals for
similar line items.
If the BOL and the Part are the same then the two line items should be
combined.
The Quantity and the Amount should be totalled, everything else should
remain the same.
If you notice from the example the line items went from 7 to 5.
Example:
Cust. ShipDate BOL Part Quantity Price Amount
ABC 05/02/08 9490 A 1 2.00 2.00
ABC 05/02/08 9490 A 1 2.00 2.00
ABC 05/02/08 9490 B 2 1.00 2.00
ABC 05/05/08 9491 A 1 2.00 2.00
ABC 05/05/08 9491 A 1 2.00 2.00
ABC 05/05/08 9491 B 2 1.00 2.00
ABC 05/05/08 9491 C 3 3.00 9.00
Answer:
Cust. ShipDate BOL Part Quantity Price Amount
ABC 05/02/08 9490 A 2 2.00 4.00
ABC 05/02/08 9490 B 2 1.00 2.00
ABC 05/05/08 9491 A 2 2.00 4.00
ABC 05/05/08 9491 B 2 1.00 2.00
ABC 05/05/08 9491 C 3 3.00 9.00
|