Remove duplicates and change dates - VBA

Hi all,

I need to create a macro that will remove duplicates and leave everyone with one line. But dates will need to be amended before deletion of duplicates.

The ONE remaining line will have:-

1. The earliest Start date for each employee.

2. The greatest end date for each employee UNLESS the line with the greatest start date, has an end date that is BLANK (This means the record is still ongoing, and should remain BLANK).

Thanks in advance
Please see image/data for example.

Capture.JPG

Example Data.xlsx

Try this:-
Results start "E1"

Sub MG03Mar16
Dim Rng         As Range
Dim Dn          As Range
Dim Q
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, Array(Dn, Dn.Offset(, 1), Dn.Offset(, 2))
    Else
        Q = .Item(Dn.Value)
            If Dn.Offset(, 1) < Q(1) Then Q(1) = Dn.Offset(, 1)
            If Not Q(2) = "" Then
                If Dn.Offset(, 2) = "" Then
                    Q(2) = Dn.Offset(, 2)
                ElseIf Dn.Offset(, 2) > Q(2) Then
                    Q(2) = Dn.Offset(, 2)
                End If
            End If
        .Item(Dn.Value) = Q
    End If
Next
Dim k
Dim c As Long
c = 1
Range("E1:G1").Value = Array("Employees", "Start Date", "End Date")

For Each k In .keys
    c = c + 1
    Cells(c, "E") = .Item(k)(0)
    Cells(c, "F") = .Item(k)(1)
    Cells(c, "G") = .Item(k)(2)
Next k

End With
End Sub

Regards Mick

Hi Guys,

Tested them both and both work perfectly!!!

Thank you so so much this is a great help!