Latest entry

Paulod · Apr 9th 2015

My spreadsheet has several thousand rows containing, amongst other information, names of clients and the dates they have been visited (these vary from case to case) . To save time I am after code that will enable the row that has the latest date each client was visited to be displayed on a new separate sheet.
Help here would be well received.

Sheet 1(existing sheet)
A B C
9/April/2015 Bill 3
8/March/2015 Gavin 3
8/March/2015 Bill 4
1/April/2015 Gavin 2

Sheet 2 (new sheet)
A B C
9/April/2015 Bill 3
1/April/2015 Gavin 2

jindon · Apr 9th 2015

Re: Latest entry

If you want vb code for the above, upload a sample workbook and I will see if I can help you or not.

Paulod · Apr 9th 2015

Re: Latest entry

Hi Jindon
Thanks for taking the time to look at this for me, I'm new to this and appreciate any and all help I can get.
What I'm after is the row based on the latest date each person ordered to appear on a new sheet.
Cheers

jindon · Apr 10th 2015

Re: Latest entry

This should do, BUT...
You need to fix you data in col.A.
Some of dates are not serial date, they are text value...7

Code

Sub test()
    Dim a, i As Long, ii As Long
    a = Sheets("sheet1").Cells(1).CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 1 To UBound(a, 1)
            If Not .exists(a(i, 2)) Then
                .Item(a(i, 2)) = .Count + 1
                For ii = 1 To UBound(a, 2)
                    a(.Item(a(i, 2)), ii) = a(i, ii)
                Next
            Else
                If a(.Item(a(i, 2)), 1) < a(i, 1) Then
                    For ii = 1 To UBound(a, 2)
                        a(.Item(a(i, 2)), ii) = a(i, ii)
                    Next
                End If
            End If
        Next
        i = .Count
    End With
    With Sheets("sheet2").Cells(1).Resize(i, UBound(a, 2))
        .CurrentRegion.ClearContents
        .Value = a: .Parent.Select
    End With
End Sub

Display More

Paulod · Apr 10th 2015

Re: Latest entry

Thanks for your brilliance Jindon, I changed the date format and it's worked perfectly. If you're ever in Auckland dinner is on me

jindon · Apr 10th 2015

Re: Latest entry

You are welcome and thanks for the offer...

Latest entry

Participate now!

Share