Excel Macro to group by column & sum value [Solved/Closed]

Shital - Mar 10, 2010 at 10:50 PM - Latest reply:  Pearson24
- Mar 16, 2010 at 01:44 AM
Hi there,

I need to write a macro for following example.

Item Qty Length
A 1 100
A 1 100
B 2 200
B 1 100
B 5 100
C 4 200
C 2 100
C 1 200
C 3 100


Basically I want to group by Column A (i.e. Item) and Column C (i.e. length) and also wants to add total of quantity for each change in lengths for an item. In this case, the result on new sheet would be as below:

Item Qty Length
A 2 100
B 2 200
B 6 100
C 5 200
C 5 100

Hope this make sense.

Can anyone help me write an excel macro for this please?

Many Thanks.

Cheers,
Shital
See more 

2 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 12, 2010 at 07:08 AM
9
Thank you
Try this
Assumption:
1. When you start macro, the sheet from where data is to be copied is the active sheet
2. An empty cell in column A indicate end of data
3. You want to paste to sheet3 (correct in macro if that is not the case)


Sub consolidateData()

Dim lRow As Long
Dim ItemRow1, ItemRow2 As String
Dim lengthRow1, lengthRow2 As String

    Columns("A:C").Select
    Selection.Copy
    
    Sheets("Sheet3").Select
    
    Range("A1").Select
    ActiveSheet.Paste
    
    Cells.Select
    Selection.Sort _
        Key1:=Range("A2"), Order1:=xlAscending, _
        Key2:=Range("C2"), Order2:=xlDescending, _
        Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        
    lRow = 2
    Do While (Cells(lRow, 1) <> "")
        
        ItemRow1 = Cells(lRow, "A")
        ItemRow2 = Cells(lRow + 1, "A")
        
        lengthRow1 = Cells(lRow, "C")
        lengthRow2 = Cells(lRow + 1, "C")
        
        If ((ItemRow1 = ItemRow2) And (lengthRow1 = lengthRow2)) Then
            Cells(lRow, "B") = Cells(lRow, "B") + Cells(lRow + 1, "B")
            Rows(lRow + 1).Delete
    
        Else
            lRow = lRow + 1
        End If
    Loop
End Sub

Thank you, rizvisa1 9

Something to say? Add comment

CCM has helped 1676 users this month

Hi there,

Thanks very much. This is what I was after. It works.

Your help much appreciated.

Thanks again.

Cheers