Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals problem: Excel 2003 (not, AFAIK, the nested subtotals bug) | Excel Discussion (Misc queries) | |||
Subtotals: Nested subtotals below higher subtotal | Excel Discussion (Misc queries) | |||
How do I copy an outline w/ subtotals & paste just the subtotals | Excel Discussion (Misc queries) | |||
Problem with nested subtotals, placing secondary subtotals BELOW . | Excel Discussion (Misc queries) | |||
why are nested subtotals coming out below outer subtotals? | Excel Worksheet Functions |