Subtotals

[basic]

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

 

[Bob Phillips]

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]

Thanks Bob! That did the trick.

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)