Thread: scan data, find max value of column, return single record on other worksheet

Hello,

I tried searching but I didn't see anything. I'm trying to scan a range of data, locate the maximum date, and return only 1 unique account record on another worksheet tab. See attached. I would like a button I can click to do this.

Thanks,
VB

Please see attached for the solution

if anyone can help and teach me how to do this, ill paypal you $

edit: and sorry, the final output on the second tab should have B12 instead of the **** purchase

Now where is my money?

Sub Test()
Dim WS As Worksheet: Set WS = Worksheets("Original")
Dim Col As Long, LR As Long
Dim i As Long, j As Long

With Application
.ScreenUpdating = False
.StatusBar = "Updating"
End With

With WS
    LR = .UsedRange.Rows.Count
    .Range(.Cells(1, 1), .Cells(LR, 3)).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=.Cells(1, 7), unique:=True
        For i = 2 To .Cells(Rows.Count, "I").Row
            For j = 2 To LR
                If .Cells(i, "G") & .Cells(i, "H") & .Cells(i, "I") = .Cells(j, "A") & .Cells(j, "B") & .Cells(j, "C") Then
                    If .Cells(i, "J") < .Cells(j, "D") Then
                        .Cells(i, "J") = .Cells(j, "D")
                        .Cells(i, "K") = .Cells(j, "E")
                    End If
                End If
            Next j
        Next i
End With

With Application
.ScreenUpdating = True
.StatusBar = ""
End With

End Sub