Filtering and Sum group of data (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Steve

Reply | Quote

Hi Steve

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

Thanks for looking into this.

regards, francis

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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:=xlDatabase, SourceData:= _
Worksheets(“Sheet1”).Range(“A1”).CurrentRegion) _
.CreatePivotTable(TableDestination:=Worksheets(“Sheet1”).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

Reply | Quote