Summary using VBA
 

ORDER DATA
Order # Part# Qty
1 A11 3
2 A12 4
3 A11 1
4 A12 2
5 A11 3
6 A11 1
7 A11 2
8 A13 2
9 A11 3
10 A12 4

I have order Order #, Part # and Qty in Col A,B, C in Tab-A
I want USING VBA a summary as under in TAb-2. I am aware this can achieved
vy PIVOT, SUBTOTALS etc.

ORDER SUMMARY
Part# Qty
A11 13
A12 10
A13 2
Grand Total 25

Summary using VBA
 

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim j As Long
Dim iRow As Long
Dim iLastRow As Long

With ActiveSheet

.Range("B1:C1").Copy Worksheets("Sheet2").Range("A1")
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
j = 2
For i = 2 To iLastRow
iRow = 0
On Error Resume Next
iRow = Application.Match(.Cells(i, "B"), _
Worksheets("Sheet2").Columns(1), 0)
On Error GoTo 0
If iRow = 0 Then
Worksheets("Sheet2").Cells(j, "A").Value = _
.Cells(i, "B").Value
Worksheets("Sheet2").Cells(j, "B").Value = _
.Cells(i, "C").Value
j = j + 1
Else
Worksheets("Sheet2").Cells(iRow, "B").Value = _
Worksheets("Sheet2").Cells(iRow, "B").Value + _
.Cells(i, "C").Value
End If
Next i

Worksheets("Sheet2").Cells(j, "A").Value = "Grand Total"
Worksheets("Sheet2").Cells(j, "B").FormulaR1C1 = "=SUM(R2C:R[-1]C)"
End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Ananth" wrote in message
...
ORDER DATA
Order # Part# Qty
1 A11 3
2 A12 4
3 A11 1
4 A12 2
5 A11 3
6 A11 1
7 A11 2
8 A13 2
9 A11 3
10 A12 4

I have order Order #, Part # and Qty in Col A,B, C in Tab-A
I want USING VBA a summary as under in TAb-2. I am aware this can achieved
vy PIVOT, SUBTOTALS etc.

ORDER SUMMARY
Part# Qty
A11 13
A12 10
A13 2
Grand Total 25