Flagging the latest instance of a duplicate record by way of VBA

Kiwihiker 

You may try the following code (placed on Module1 in the attached) to flag the Unique/Latest item in column F as you showed in the sample file.

Sub IdentifyUniqueOrLatestRecord()
Dim ws      As Worksheet
Dim x       As Variant
Dim dict    As Object
Dim i       As Long
Dim Trans   As Long
Dim Flag    As Variant

Application.ScreenUpdating = False

Set ws = Worksheets("Sheet1")
x = ws.Range("A1").CurrentRegion.Value
ReDim Flag(1 To UBound(x, 1) - 1, 1 To 1)

Set dict = CreateObject("Scripting.Dictionary")

For i = 2 To UBound(x, 1)
    Trans = VBA.Trim(Split(x(i, 4), "-")(1))
    If Not dict.exists(x(i, 2)) Then
        dict.Item(x(i, 2)) = Trans
    Else
        If Trans > dict.Item(x(i, 2)) Then
            dict.Item(x(i, 2)) = Trans
        End If
    End If
Next i

For i = 2 To UBound(x, 1)
    Trans = VBA.Trim(Split(x(i, 4), "-")(1))
    If Trans = dict.Item(x(i, 2)) Then
        Flag(i - 1, 1) = True
    Else
        Flag(i - 1, 1) = False
    End If
Next i

ws.Range("A1").CurrentRegion.Columns(6).Offset(1).ClearContents
ws.Range("F2").Resize(UBound(Flag, 1), 1).Value = Flag

Application.ScreenUpdating = True
End Sub