sum up total quantity

[freezefiz]

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.

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.

 

[jindon]

Ah. Much Clearer now.
Jindon: I tested your code, it didn't add up the quantity value.
I could be wrong, but it seems that she wants to add up the quantity as well.

I have written the code according to the sample and result from OP's 1st post.
Add up?

Sub test()
Dim a, i As Long, ii As Long, b(), n As Long, z As String
With Range("a5", Range("a" & Rows.Count).End(xlUp)).Resize(,4)
    a = .Value
    ReDim b(1 To UBound(a,1), 1 To UBound(a,2))
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = 1 To UBound(a,1)
            z = a(i,1) & ";" & a(i,2)
            If Not .exists(z) Then
                n = n + 1 : .add z, n
                For ii = 1 To UBound(a,2) : b(n,ii) = a(i,ii) : Next
            Else
                b(.item(z), 4) = b(.item(z), 4) + a(i,4)
            End If
        Next
    End With
    .Value = b
End With
End Sub

 

[freezefiz]

hey jindon.. i'm testing out your codes now..

will let u knw the outcome.

thanks for that.

 

[ExcelPr0]

That's fast....makes what I know an embrassment. You do this everyday?

I have written the code according to the sample and result from OP's 1st post.
Add up?

 

[Simon Lloyd]

Cross posted in different names here http://www.mrexcel.com/forum/showthread.php?p=1603707#post1603707
and http://www.excelforum.com/showthread.php?t=647543&page=2

 

[jindon]

Hey Pukka

How many HN have created?
I hate it and I should have realised...

 

[Simon Lloyd]

Jindon i've banned him at Excel Forum, it's disrespectful and an abuse of the free help we give thats why i didn't reply to his follow up question about the uom in page 1!

 

[jindon]

Jindon i've banned him at Excel Forum, it's disrespectful and an abuse of the free help we give thats why i didn't reply to his follow up question about the uom in page 1!

Yes, this guy is using 3 different user names as far as I know.

"Pukka", "Guanjin, Peter" and "freezefiz"

 

[Guanjin Peter]

goodness, freezefiz IS NOT ME!

 

[Stormseed]

Pukka, if you have more than 1 username, then I am sorry to say that you are a loser.

I would only suggest the Administrators or Moderators to track the IPs of all the members as they would help extensively to limit the number of IDs created by a member on the Board. It is not a complete solution but it can help to an extent according to my understanding.

 

[freezefiz]

Guys.. i'm so sorry for this confusion.

"pukka, guanjin" & me is a different person.
i truly regret for the discomfort that it may cause to guanjin and other fellow excel experts.

The questions we posted may be similar, and that's because we're working on the same project.

We interpret the questions differently as we think that different experts have different ways of understanding the questions.

I'm so sorry for any inconvenience cause.

and I didn't think that it'd be a major issue.

But i truly appreciate all the help that i've gotten from the fellow experts here. A huge thanks to all who've helped me through the project development.

And sorry guanjin for putting you in such situation.