# Excel Macro to group by column & sum value

Solved/Closed
Shital - Mar 10, 2010 at 10:50 PM
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
Related:

## 1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 12, 2010 at 07:08 AM
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, _
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
```
Hi there,

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