How to find Last(Latest) Payment date and Amount from Excel

Hi Team

I preparing Report every month my Client Payments and Payment date (Latest payment) , this process i am doing it manually, this process sucks more time , i was tried "max" formula as well
its returns 0,

Could you anyone help me to simplify the process (using vba macro generate the report as expected)

Payment_Details
payment_details.jpg

Report
report_result.jpg


i've attached sample sheet,

Hi !

Why don't you use Excel BASICS ?!

Sort data on dates column in descending way, use an advanced filter on first column to get unique clients …

And if you really need a code, activate Macro recorder before to operate : you will get your own free code !

Try this for results starting "F1"

Sub MG10Dec27
Dim Rng As Range, Dn As Range, K As Variant, oMax1 As Date, G As Variant
Dim c As Long, oMax2 As Double
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
    If Not .Exists(Dn.Value) Then
        .Add Dn.Value, Dn
    Else
        Set .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    End If
Next
c = 1
Range("F1:H1").Value = Array("Client", "Pay_Date", "Amount")
For Each K In .keys
    oMax1 = Application.Max(.Item(K).Offset(, 1))
    For Each G In .Item(K).Offset(, 1)
        If G = oMax1 Then
        oMax2 = Application.Max(oMax2, G.Offset(, 1))
        End If
    Next G
       c = c + 1
       Cells(c, "F") = K
       Cells(c, "G") = CDate(oMax1)
       Cells(c, "H") = oMax2
       oMax1 = 0: oMax2 = 0
Next K
End With
End Sub

Regards Mick

thank you very much , your code help me to get result as i expected

PHP Code: 

Sub Macro1()
             [F6].CurrentRegion.Clear
    With Cells(1).CurrentRegion
        .Sort Cells(1), xlAscending, Cells(2), , xlDescending, Cells(3), xlDescending, xlYes
        .Columns(1).AdvancedFilter xlFilterInPlace, Unique:=True
        .Copy [F6]
        If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
    End With
End Sub 


Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

Thanks for the rep' !

Base code created using Macro recorder just with Excel basics …

You're welcome