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]

 

[BarryL]

In column U put the below formula in beside the last row in Col T. For example if the last column in T was 22 then put in U22:

=IF(COUNTIF(S22:$S$22,S22)=1,U23+1,U23)

in U23 put in a 0. and drag the formula up to the top.

Then in col V put the below formula. copy down and across as much as needed. This formula assumes there are headers.

=IFERROR(INDEX(R:R,MATCH(ROW()-1,$U:$U,0)),"")

 

[Aladin Akyurek]

Do you want (a) a result output consisting of start date, name, and date of absence, which meets your condition or (b) an additional column like U where the most recent date of absence listed per each name?

 

[BarryL]

That last formula should be =IFERROR(LOOKUP(2,1/($U:$U=ROW()-1),R:R),"")

 

[rfletcher35]

Hi,

Love the code and think I'm getting my head around it, but if I'm reading it right it will remove the rows with duplicates and I don't think that will work as I have other information on adjacent columns. Unless I move all my data to another sheet to reference?

 

[Aladin Akyurek]

Hi,

Love the code and think I'm getting my head around it, but if I'm reading it right it will remove the rows with duplicates and I don't think that will work as I have other information on adjacent columns. Unless I move all my data to another sheet to reference?

Post #3?

 

[rfletcher35]

This is not working it just converts the cells where I put the formula in to 0's

 

[Aladin Akyurek]

This is not working it just converts the cells where I put the formula in to 0's

You skip post #3...

 

[godsaaint]

When you use "remove duplicates" option in the data tab, excel deletes duplicates beginning from the bottom. You can just sort the date of absence by newest and then proceed to remove duplicates and you'll keep the ones with the latest date.

 

[Peter_SSs]

When you use "remove duplicates" option in the data tab, excel deletes duplicates beginning from the bottom. You can just sort the date of absence by newest and then proceed to remove duplicates and you'll keep the ones with the latest date.

I would be reluctant to rely on Remove Duplicates as it it not robust. A simple example to demonstrate: http://www.mrexcel.com/forum/excel-questions/874643-remove-duplicates-visual-basic-applications-code.html#post4241909
As far as I am aware, the issue has not been resolved.