Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summary SUM | Excel Discussion (Misc queries) | |||
Summary | Excel Worksheet Functions | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) | |||
multi group with summary above with 1 overall summary line below | Excel Discussion (Misc queries) | |||
Summary | Excel Worksheet Functions |