Ok buddy here’s the Macro code please have a look
Code:
Sub report()
Dim ws1 As Worksheet
Dim wsR As Worksheet
Dim LastRow, j, t As Long
Dim i As Integer
On Error Resume Next
Set ws1 = Worksheets("Trns")
Set wsR = Worksheets("Report")
wsR.Select
LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
ws1.Range("A2:A" & LastRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=wsR.Range("A1"), Unique:=True
j = wsR.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To j
With WorksheetFunction
t = Cells(i, 1).Value
Cells(i, 2).Value = .CountIf(ws1.Range("A:A"), Cells(i, 1))
Cells(i, 3).Value = .SumIf(ws1.Range("A:A"), Cells(i, 1), ws1.Range("C:C"))
Cells(i, 4).Value = .SumProduct(.Max((ws1.Range("a3:a100") = t) * ws1.Range("D3:d100")))
End With
Next i
End Sub
Hope many things will be clear to you. And I sent you sample book. And there is no ‘ before ws2.range it’s just a typing mistake.
Bookmarks