freezefiz
Board Regular
- Joined
- May 21, 2008
- Messages
- 101
I've found this great code which deletes duplicated rows.
And it'll be great if i could get some help in modifying it so as to achieve my desired output.
Before combining :
As shown below, the duplicated rows are deleted, and unfortunately this also means that the quantity for that row is also deleted.
But i would like is to sum up all the qty for that product & so only the total will be displayed.
After combining :
Pls Advise.
Thanks.
And it'll be great if i could get some help in modifying it so as to achieve my desired output.
Code:
Dim lngTMP As Long, iRows As Long
On Error GoTo Fin
Application.ScreenUpdating = False
iRows = Cells(Cells.Rows.Count, 2).End(xlUp).Row
For lngTMP = iRows To 6 Step -1
If WorksheetFunction.CountIf(Columns(2), Cells(lngTMP, 2)) > 1 Then
Rows(lngTMP).Delete
End If
Next lngTMP
Fin:
Application.ScreenUpdating = True
Before combining :
protoV6.xls | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | PrepareDate: | 6/18/2008 | ||||
2 | Day: | Wednesday | ||||
3 | DeliverDate: | |||||
4 | ProductID | Product | Uom | Qty | ||
5 | TDG-**002 | TunaCheesePizzaBar(KG) | KG | 30 | ||
6 | RFG-**588 | ThaiChickenFriedRice(KG) | KG | 30 | ||
7 | RFG-**502 | ClubSandwich(PKT) | PKT | 3 | ||
8 | RFG-**501 | RoastMexicanChicken(PKT) | PKT | 15 | ||
9 | RFG-**501 | RoastMexicanChicken(pcs) | pcs | 12 | ||
10 | TDG-**002 | TunaCheesePizzaBar(pcs) | pcs | 15 | ||
11 | RFG-**501 | RoastMexicanChicken(KG) | KG | 10 | ||
12 | TDG-**002 | TunaCheesePizzaBar(KG) | KG | 30 | ||
13 | RFG-**588 | ThaiChickenFriedRice(KG) | KG | 30 | ||
14 | RFG-**502 | ClubSandwich(PKT) | PKT | 3 | ||
15 | RFG-**501 | RoastMexicanChicken(PKT) | PKT | 15 | ||
16 | RFG-**501 | RoastMexicanChicken(pcs) | pcs | 12 | ||
17 | TDG-**002 | TunaCheesePizzaBar(pcs) | pcs | 15 | ||
18 | RFG-**501 | RoastMexicanChicken(KG) | KG | 10 | ||
19 | TDG-**002 | TunaCheesePizzaBar(KG) | KG | 30 | ||
20 | RFG-**588 | ThaiChickenFriedRice(KG) | KG | 30 | ||
21 | RFG-**502 | ClubSandwich(PKT) | PKT | 3 | ||
22 | RFG-**501 | RoastMexicanChicken(PKT) | PKT | 15 | ||
23 | RFG-**501 | RoastMexicanChicken(pcs) | pcs | 12 | ||
24 | TDG-**002 | TunaCheesePizzaBar(pcs) | pcs | 15 | ||
25 | RFG-**501 | RoastMexicanChicken(KG) | KG | 10 | ||
GrandTotal |
As shown below, the duplicated rows are deleted, and unfortunately this also means that the quantity for that row is also deleted.
But i would like is to sum up all the qty for that product & so only the total will be displayed.
After combining :
protoV6.xls | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | PrepareDate: | 6/18/2008 | ||||
2 | Day: | Wednesday | ||||
3 | DeliverDate: | |||||
4 | ProductID | Product | Uom | Qty | ||
5 | TDG-**002 | TunaCheesePizzaBar(KG) | KG | 30 | ||
6 | RFG-**588 | ThaiChickenFriedRice(KG) | KG | 30 | ||
7 | RFG-**502 | ClubSandwich(PKT) | PKT | 3 | ||
8 | RFG-**501 | RoastMexicanChicken(PKT) | PKT | 15 | ||
9 | RFG-**501 | RoastMexicanChicken(pcs) | pcs | 12 | ||
10 | TDG-**002 | TunaCheesePizzaBar(pcs) | pcs | 15 | ||
11 | RFG-**501 | RoastMexicanChicken(KG) | KG | 10 | ||
GrandTotal |
Pls Advise.
Thanks.