Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering and Sum group of data (Excel 2003)

    Hi all

    I have been trying to figure out how to write a macro on the following task for the past 6 hours and end up seeking assistance
    in my usual place; Woody.I have a database in sheet 1 and I need to group this data under Name and Symbol Code, after which,
    I need to sum the Total Unit and Total Amount for each change in Name and Symbol Code.

    I can only wrote up to for each change in Symbol Code but not both including Name and Symbol Code. Pls have a look
    at the below code and the attached and advise how to tweak the marco to perform the result that I am looking for.
    Sheet 2 shows the result of the codes below

    Sub SortAndGroupTotal()

    Dim i As Long
    Dim lastrow As Long
    Dim saverow As Long

    'Initialize
    saverow = 1

    'Get last row, Col A
    lastrow = Range("A65536").End(xlUp).Row

    'Select & sort Col A to K
    Range("A2:K" & lastrow).Select
    Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range( _
    "A2"), Order2:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

    'Check for duplicates in Col A
    For i = 2 To lastrow
    If Cells(i, 1) <> Cells(i + 1, 1) Then
    Cells(i, 9) = "=Sum(H" & saverow & ":H" & i & ")"
    saverow = i + 1
    End If
    Next i
    End Sub

    TIA

    Regards, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filtering and Sum group of data (Excel 2003)

    You put the results of your code, but what are the results that you want?

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering and Sum group of data (Excel 2003)

    You could use subtotals:

    Sub SortAndSubtotal()
    With Range("A1").CurrentRegion
    .Sort Key1:=Range("E2"), Order1:=xlAscending, _
    Key2:=Range("A2"), Order2:=xlAscending, Header:=xlYes
    .Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(7, 8), _
    Replace:=True
    .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7, 8), _
    Replace:=False
    End With
    End Sub

  4. #4
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering and Sum group of data (Excel 2003)

    Hi Hans,

    Thanks for providing a solution to this. The sub total results is not what I want. I have attached a sample in the above post
    which in Sheet 2 shows the result that I am looking for. I need the data in col A to col F to be together with the new values
    in col G and col H if any.

    thanks

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  5. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering and Sum group of data (Excel 2003)

    Hi Steve

    I have attached a sample which shows the result I want in Sheet 2.

    Thanks for looking into this.

    regards, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering and Sum group of data (Excel 2003)

    Try this:

    Sub SortAndSubTotal()
    Dim m As Long
    Dim r As Long
    ' Delete column I since it's not in the desired result
    Range("I1").EntireColumn.Delete
    ' Sort range
    Range("A1").CurrentRegion.Sort _
    Key1:=Range("E2"), Order1:=xlAscending, _
    Key2:=Range("A2"), Order2:=xlAscending, Header:=xlYes
    ' Last row
    m = Range("A" & Rows.Count).End(xlUp).Row
    ' Loop backwards
    For r = m To 3 Step -1
    ' Check for duplicate
    If Range("A" & r) = Range("A" & (r - 1)) And _
    Range("E" & r) = Range("E" & (r - 1)) Then
    ' Add values in clumns G and H to previous row
    Range("G" & (r - 1)) = Range("G" & (r - 1)) + Range("G" & r)
    Range("H" & (r - 1)) = Range("H" & (r - 1)) + Range("H" & r)
    ' Delete row
    Range("A" & r).EntireRow.Delete
    End If
    Next r
    End Sub

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering and Sum group of data (Excel 2003)

    Hi Hans,

    Thanks, It work perfectly!
    Would you elaborate on this few lines, I don't understand how these work, especially on r and (r - 1)

    If Range("A" & r) = Range("A" & (r - 1)) And _
    Range("E" & r) = Range("E" & (r - 1)) Then
    ' Add values in clumns G and H to previous row
    Range("G" & (r - 1)) = Range("G" & (r - 1)) + Range("G" & r)
    Range("H" & (r - 1)) = Range("H" & (r - 1)) + Range("H" & r)

    Can the same result be produce by pivot table? I have try but can't get the result or maybe I am still new to it and
    may have preformed it wrongly.

    Thank you for the effort in this.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering and Sum group of data (Excel 2003)

    In the line(s)

    If Range("A" & r) = Range("A" & (r - 1)) And _
    Range("E" & r) = Range("E" & (r - 1)) Then

    r is the number of the row being inspected, and hence r - 1 is the number of the row above it. These lines check whether the values in column A are the same in these two rows, and also those in colum E.

    The line

    Range("G" & (r - 1)) = Range("G" & (r - 1)) + Range("G" & r)

    adds the value in column G in the "current" row to that in the row above it. The same is done for column H, then the "current" row is deleted.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering and Sum group of data (Excel 2003)

    You can easily create a pivot table manually, using Data | Pivot Table and Pivot Chart Report.
    But if you prefer a macro, it could look like this:

    Sub MakePivot()
    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    Worksheets("Sheet1").Range("A1").CurrentRegion) _
    .CreatePivotTable(TableDestination:=Worksheets("Sh eet1").Range("M1"))
    .AddFields RowFields:=Array("Name", "Symbol Code", "Data")
    .PivotFields("Total Unit").Orientation = xlDataField
    End With
    .PivotFields("Total Amount").Orientation = xlDataField
    .DataPivotField.Orientation = xlColumnField
    End With
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •