remove duplicates, keeping latest

[rfletcher35]

I have posted below a sample of my sheet from Columns R,S, & T

I need to remove duplicate names so there is only one entry per name but I want to keep the one with the latest date of absence.....

Any ideas on this one please???

Fletch

[TABLE="width: 408"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]Name[/TD]
[TD]Date of Absence[/TD]
[/TR]
[TR]
[TD="align: right"]24/02/14[/TD]
[TD="align: left"]Tomasz Plonka[/TD]
[TD="align: right"]06/09/2016[/TD]
[/TR]
[TR]
[TD="align: right"]18/07/13[/TD]
[TD="align: left"]Malcolm Peters[/TD]
[TD="align: right"]02/09/2016[/TD]
[/TR]
[TR]
[TD="align: right"]25/07/16[/TD]
[TD="align: left"]Deividas Kacinskas[/TD]
[TD="align: right"]24/06/2016[/TD]
[/TR]
[TR]
[TD="align: right"]20/05/13[/TD]
[TD="align: left"]Dennis De Sousa[/TD]
[TD="align: right"]22/08/2016[/TD]
[/TR]
[TR]
[TD="align: right"]20/01/14[/TD]
[TD="align: left"]Fredrick Ndungu[/TD]
[TD="align: right"]01/09/2016[/TD]
[/TR]
[TR]
[TD="align: right"]13/01/14[/TD]
[TD="align: left"]Waldermar Kruk[/TD]
[TD="align: right"]28/06/2016[/TD]
[/TR]
[TR]
[TD="align: right"]30/04/12[/TD]
[TD="align: left"]Waldermar Kruk[/TD]
[TD="align: right"]29/06/2016[/TD]
[/TR]
[TR]
[TD="align: right"]25/07/12[/TD]
[TD="align: left"]Waldermar Kruk[/TD]
[TD="align: right"]24/08/2016[/TD]
[/TR]
[TR]
[TD="align: right"]08/02/13[/TD]
[TD="align: left"]Waldermar Kruk[/TD]
[TD="align: right"]25/08/2016[/TD]
[/TR]
[TR]
[TD="align: right"]24/03/14[/TD]
[TD="align: left"]Waldermar Kruk[/TD]
[TD="align: right"]26/08/2016[/TD]
[/TR]
[TR]
[TD="align: right"]30/04/12[/TD]
[TD="align: left"]Waldermar Kruk[/TD]
[TD="align: right"]30/08/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14/10/15[/TD]
[TD="align: left"]Waldermar Kruk[/TD]
[TD="align: right"]31/08/2016[/TD]
[/TR]
[TR]
[TD="align: right"]14/02/13[/TD]
[TD="align: left"]Waldermar Kruk[/TD]
[TD="align: right"]01/09/2016[/TD]
[/TR]
[TR]
[TD="align: right"]25/07/14[/TD]
[TD="align: left"]Waldermar Kruk[/TD]
[TD="align: right"]05/09/2016[/TD]
[/TR]
[TR]
[TD="align: right"]22/07/15[/TD]
[TD="align: left"]Waldermar Kruk[/TD]
[TD="align: right"]06/09/2016[/TD]
[/TR]
[TR]
[TD="align: right"]20/07/15[/TD]
[TD="align: left"]Waldermar Kruk[/TD]
[TD="align: right"]12/09/2016[/TD]
[/TR]
[TR]
[TD="align: right"]24/03/14[/TD]
[TD="align: left"]Asem Fekri[/TD]
[TD="align: right"]12/07/2016[/TD]
[/TR]
[TR]
[TD="align: right"]26/01/16[/TD]
[TD="align: left"]John Sthamer[/TD]
[TD="align: right"]22/08/2016[/TD]
[/TR]
[TR]
[TD="align: right"]22/11/11[/TD]
[TD="align: left"]John Sthamer[/TD]
[TD="align: right"]23/08/2016[/TD]
[/TR]
[TR]
[TD="align: right"]13/01/14[/TD]
[TD="align: left"]John Sthamer[/TD]
[TD="align: right"]24/08/2016[/TD]
[/TR]
[TR]
[TD="align: right"]03/03/14[/TD]
[TD="align: left"]John Sthamer[/TD]
[TD="align: right"]25/08/2016[/TD]
[/TR]
</tbody><colgroup><col><col span="2"></colgroup>[/TABLE]

 

[rfletcher35]

Guys can anyone help with this i'm still stuck???

 

[rfletcher35]

I now have the following code which sorts the 2 columns Now E&F (Names & Dates) into alphabetical order with the latest dates corresponding to the names to the latest first. So in theory I just require a way now to retain on the first name in column E with the latest date.

Any ideas????

Sub Macro1()
'
' Macro1 Macro
'
'
Range("E3:F3").Select
Range(Selection, Selection.End(xlDown)).Select
'Range("E3:F3149").Select

ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("F4:F4000" _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("E3:F4000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("E4:E4000" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange Range("E3:F4000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub


[TABLE="width: 296"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date of Absence[/TD]
[/TR]
[TR]
[TD]Aaron Dougherty[/TD]
[TD="align: right"]26/05/2016[/TD]
[/TR]
[TR]
[TD]Aaron Johnson[/TD]
[TD="align: right"]16/11/2015[/TD]
[/TR]
[TR]
[TD]Aaron Johnson[/TD]
[TD="align: right"]13/11/2015[/TD]
[/TR]
[TR]
[TD]Aaron Richards[/TD]
[TD="align: right"]09/12/2015[/TD]
[/TR]
[TR]
[TD]Aaron Richards[/TD]
[TD="align: right"]08/12/2015[/TD]
[/TR]
[TR]
[TD]Aaron Richardson[/TD]
[TD="align: right"]02/09/2016[/TD]
[/TR]
[TR]
[TD]Aaron Richardson[/TD]
[TD="align: right"]01/09/2016[/TD]
[/TR]
[TR]
[TD]Aaron Richardson[/TD]
[TD="align: right"]31/08/2016[/TD]
[/TR]
[TR]
[TD]Aaron Richardson[/TD]
[TD="align: right"]30/08/2016[/TD]
[/TR]
[TR]
[TD]Aaron Richardson[/TD]
[TD="align: right"]26/08/2016[/TD]
[/TR]
[TR]
[TD]Aaron Richardson[/TD]
[TD="align: right"]25/08/2016[/TD]
[/TR]
[TR]
[TD]Aaron Richardson[/TD]
[TD="align: right"]24/08/2016[/TD]
[/TR]
[TR]
[TD]Aaron Richardson[/TD]
[TD="align: right"]24/05/2016[/TD]
[/TR]
[TR]
[TD]Aaron Richardson[/TD]
[TD="align: right"]21/01/2016[/TD]
[/TR]
[TR]
[TD]Aaron Shaw[/TD]
[TD="align: right"]13/09/2016[/TD]
[/TR]
[TR]
[TD]Aaron Shaw[/TD]
[TD="align: right"]07/09/2016[/TD]
[/TR]
[TR]
[TD]Aaron Shaw[/TD]
[TD="align: right"]06/09/2016[/TD]
[/TR]
[TR]
[TD]Aaron Shaw[/TD]
[TD="align: right"]02/06/2016[/TD]
[/TR]
[TR]
[TD]Aaron Shaw[/TD]
[TD="align: right"]01/06/2016[/TD]
[/TR]
[TR]
[TD]Aaron Shaw[/TD]
[TD="align: right"]31/05/2016[/TD]
[/TR]
[TR]
[TD]Aaron Shaw[/TD]
[TD="align: right"]27/05/2016[/TD]
[/TR]
[TR]
[TD]Aaron Shaw[/TD]
[TD="align: right"]26/05/2016[/TD]
[/TR]
[TR]
[TD]Aaron Shaw[/TD]
[TD="align: right"]08/03/2016[/TD]
[/TR]
[TR]
[TD]Aaron Shaw[/TD]
[TD="align: right"]09/11/2015[/TD]
[/TR]
[TR]
[TD]Aaron Strover[/TD]
[TD="align: right"]13/06/2016[/TD]
[/TR]
[TR]
[TD]Aaron Strover[/TD]
[TD="align: right"]24/11/2015[/TD]
[/TR]
[TR]
[TD]Abdul Gbaja-Biamila[/TD]
[TD="align: right"]17/12/2015[/TD]
[/TR]
[TR]
[TD]Abdul Gbaja-Biamila[/TD]
[TD="align: right"]16/12/2015[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[rfletcher35]

Think I've solved it guys by using =IF(V7=V6,"Duplicate","") in column G this lets me know which are duplicates. It's just a matter now of filtering on these and removing.

Thanks for all your help throughout.

Fletch