Modify this Code to add-up numbers in COL-D

Guanjin Peter

Active Member
Joined
May 21, 2008
Messages
429
It's a linked post from:
http://www.excelforum.com/showthread.php?t=647543

I managed to do the combine the row if column B matches. However column D(quantity, number value), i want the quantity to add-up if column B matches. Any idea how do I modify the code below to do that?

for example:
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 30
TDG-**002 Tuna Cheese Pizza Bar (MG) MG 30
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 30

will combine to become
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 60 <<<< combined
TDG-**002 Tuna Cheese Pizza Bar (MG) MG 30 <<<< left untouched

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

Code:
[LEFT]Private Sub CommandButton1_Click()

  Dim wb As Workbook, ws As Worksheet, i As Integer, filess As String
  ChDrive Left(ThisWorkbook.Path, 1)
  ChDir ThisWorkbook.Path
  filess = Dir("template(*).xls")
  While filess <> ""
    Set wb = Workbooks.Open(filess)
    Set ws = wb.Sheets("Sheet1")
    Dim intRow As Integer
    intRow = 1
            Do While ws.Cells(4 + intRow, 1).Value <> ""
            i = i + 1
                proid = ws.Cells(4 + intRow, 1).Value
                pro = ws.Cells(4 + intRow, 2).Value
                uom = ws.Cells(4 + intRow, 3).Value
                qty = ws.Cells(4 + intRow, 4).Value
                ThisWorkbook.Sheets("GrandTotal").Range("a" & 1 + i).End(xlUp).Offset(1, 0).Resize(, 4) = Array(proid, pro & " (" & uom & ")", uom, qty)
                intRow = intRow + 1
            Loop
    ws.Range("a1:d4").Copy
    wb.Close savechanges:=False
    filess = Dir()
  Wend
  
    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
    
    Rows("1:1").Insert Shift:=xlDown
    Rows("1:1").Insert Shift:=xlDown
    Rows("1:1").Insert Shift:=xlDown
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

End Sub[/LEFT]
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Nope, I never tried before.

can pivot table be created automatically? (when user clicks a button) because i'm trying to reduce as much steps as possible.
 
Upvote 0
You can create a pivot table with a macro. If you record a macro while creating it manually you will get some starter code.
 
Upvote 0
i realized it can't work for me, because the data being extracted is dynamic.

I studied the code, I relized also, if I can merge the qty (in numbers on the 4th column, below I delete(as shown below), that'll do.
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
[COLOR=red][B]        If WorksheetFunction.CountIf(Columns(2), Cells(lngTMP, 2)) > 1 Then
            Rows(lngTMP).Delete[/B][/COLOR]
        End If
    Next lngTMP
Fin:
    Application.ScreenUpdating = True
 
Upvote 0
i realized it can't work for me, because the data being extracted is dynamic.

The data may well be dynamic, but the size is known from your iRows variable. So a pivot table is perfectly possible.

In your current code what would you merge the quantity into?
 
Upvote 0
The quantity value(at ColD) will add up when column b matches:

for example:
col A -------Col B ---------------------ColC - Col D
TDG-**002-Tuna Cheese Pizza Bar (KG)-- KG----30
TDG-**002-Tuna Cheese Pizza Bar (MG)--MG----30
TDG-**002-Tuna Cheese Pizza Bar (KG)--KG----30

will combine to become
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 60 <<<< combined
TDG-**002 Tuna Cheese Pizza Bar (MG) MG 30 <<<< left untouched
 
Upvote 0
I understand what you want, but where would you store the value on (the deleted) line 3 so that you can eventually add it to the value on line 1?

Record a macro while creating a pivot table from your sample data. If you post the code someone will probably be able to amend it so that it is dynamic.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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
Back
Top