Results 1 to 9 of 9

20080816, 04:38 #1
 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, francisHope 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

20080816, 06:39 #2
 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

20080816, 07:15 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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

20080816, 08:13 #4
 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, francisHope 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

20080816, 08:15 #5
 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, francisHope 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

20080816, 08:45 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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

20080816, 10:22 #7
 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

20080816, 10:27 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20080816, 10:37 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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