sum up total quantity

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.

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
ABCD
1PrepareDate:6/18/2008
2Day:Wednesday
3DeliverDate:
4ProductIDProductUomQty
5TDG-**002TunaCheesePizzaBar(KG)KG30
6RFG-**588ThaiChickenFriedRice(KG)KG30
7RFG-**502ClubSandwich(PKT)PKT3
8RFG-**501RoastMexicanChicken(PKT)PKT15
9RFG-**501RoastMexicanChicken(pcs)pcs12
10TDG-**002TunaCheesePizzaBar(pcs)pcs15
11RFG-**501RoastMexicanChicken(KG)KG10
12TDG-**002TunaCheesePizzaBar(KG)KG30
13RFG-**588ThaiChickenFriedRice(KG)KG30
14RFG-**502ClubSandwich(PKT)PKT3
15RFG-**501RoastMexicanChicken(PKT)PKT15
16RFG-**501RoastMexicanChicken(pcs)pcs12
17TDG-**002TunaCheesePizzaBar(pcs)pcs15
18RFG-**501RoastMexicanChicken(KG)KG10
19TDG-**002TunaCheesePizzaBar(KG)KG30
20RFG-**588ThaiChickenFriedRice(KG)KG30
21RFG-**502ClubSandwich(PKT)PKT3
22RFG-**501RoastMexicanChicken(PKT)PKT15
23RFG-**501RoastMexicanChicken(pcs)pcs12
24TDG-**002TunaCheesePizzaBar(pcs)pcs15
25RFG-**501RoastMexicanChicken(KG)KG10
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
ABCD
1PrepareDate:6/18/2008
2Day:Wednesday
3DeliverDate:
4ProductIDProductUomQty
5TDG-**002TunaCheesePizzaBar(KG)KG30
6RFG-**588ThaiChickenFriedRice(KG)KG30
7RFG-**502ClubSandwich(PKT)PKT3
8RFG-**501RoastMexicanChicken(PKT)PKT15
9RFG-**501RoastMexicanChicken(pcs)pcs12
10TDG-**002TunaCheesePizzaBar(pcs)pcs15
11RFG-**501RoastMexicanChicken(KG)KG10
GrandTotal


Pls Advise.
Thanks.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

freezefiz

Board Regular
Joined
May 21, 2008
Messages
101
thanks for the reply.

well that may be a great suggestion but my data is dynamic,
is that possible to implement it on pivot table?
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
By dynamic, do you mean the range size changes? You could specify a dynamic range as the source for the pivot table.
 

freezefiz

Board Regular
Joined
May 21, 2008
Messages
101

ADVERTISEMENT

well yes, the range size changes & also the values.

there may be times when some of the products might not be needed.

And also, these data are taken from yet another table.

hmm emma, is there any other possible way of doing this?
 

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
What does the original table look like? Could you do a pivot on that?

Values changing not a problem - you just refresh the pivot table. Some products not being there not an issue either - the pivot would deal with that too. (Unless you mean that you would need a row with the product name and a zero - you wouldn't get that unless the product name was in the source data with a zero - but I don't think you do mean that?)
 

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756

ADVERTISEMENT

Here's something i created for someone else but have adapted it to your needs, it assumes your data is on a sheet called data and the result will go to a sheet called Output:
Code:
Sub Find_Dups_Sum_To_New_Sheet()
    Dim MyData As Worksheet, MyOutput As Worksheet
    Dim Lrow As Long, LrowDst As Long
    Dim Rng As Range
    Set MyData = Worksheets("Data")
    Set MyOutput = Worksheets("Output")
    MyOutput.Cells.ClearContents
    Lrow = MyData.Range("A" & Rows.Count).End(xlUp).Row
    MyData.Range("A1:D" & Lrow).AdvancedFilter xlFilterCopy, , MyOutput.Range("A1"), True
    MyOutput.Range("C4") = "Totals"
    LrowDst = MyOutput.Range("A" & Rows.Count).End(xlUp).Row
    MyOutput.Range("C5:C" & LrowDst) = "=SUMIF(Data!$B$5:$B$200,B5,Data!$D$5:$D$200)"
     
End Sub
if you really want the Uom transporting over too then you need something in the C1:C3 for the advanced filter to recognise it!
EDIT: You will also need to change these lines:
Code:
MyOutput.Range("C4") = "Totals"
    LrowDst = MyOutput.Range("A" & Rows.Count).End(xlUp).Row
    MyOutput.Range("C5:C" & LrowDst) = "=SUMIF(Data!$B$5:$B$200,B5,Data!$D$5:$D$200)"
for:
Code:
 MyOutput.Range("D5") = "Totals"
    LrowDst = MyOutput.Range("A" & Rows.Count).End(xlUp).Row
    MyOutput.Range("D5:D" & LrowDst) = "=SUMIF(Data!$B$5:$B$200,B5,Data!$D$5:$D$200)"
 
Last edited:

freezefiz

Board Regular
Joined
May 21, 2008
Messages
101
Hey Simon.

Thank you so much for the codes! It worked perfectly!

hmm about the 'uom' column..
i did try to modify :

Code:
Lrow = MyData.Range("A" & Rows.Count).End(xlUp).Row
    MyData.Range("A1:D" & Lrow).AdvancedFilter xlFilterCopy, ,    MyOutput.Range("A1"), True
    MyOutput.Range("C1") = "AA"
    MyOutput.Range("C2") = "BB"
   MyOutput.Range("C3") = "BB"
   MyOutput.Range("C4") = "uom"
    MyOutput.Range("D4") = "Totals"
    LrowDst = MyOutput.Range("A" & Rows.Count).End(xlUp).Row
    MyOutput.Range("D5:D" & LrowDst) = "=SUMIF(GrandTotal!$B$5:$B$200,B5,GrandTotal!$D$5:$D$200)"

But the whole uom col didn't appear..
Am i missing something out here..?

Thanks.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Not sure if this is what you wanted ...
try
Code:
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
            End If
        Next
    End With
    .Value = b
End With
End Sub
 

ExcelPr0

New Member
Joined
Jun 17, 2008
Messages
25
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,462
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top