need most recent date with duplicate values

Hi, I need excel to find, from of a number of duplicate items (A), the most recent date in another column (B) - then subtract from another date (C) so that only the 4th row below would populate value.



29382 - 10/13/2014 - 10/03/2014
29382 - 10/11/2014 - 10/03/2014
29382 - 10/14/2014 - 10/03/2014
29382 - 10/09/2014 - 10/03/2014 <---** This would be the value I want, 10/09/14 subtract 10/03/2014 and thus, 6 days
29382 - 10/11/2014 - 10/03/2014

That is the earliest date in column B, not the most recent? Have you stated the problem clearly?

My apologies, earliest date.

This array formula should do it

=IF(B1=MIN(IF(A1=$A$1:$A$20,$B$1:$B$20)),$C$1:$C$20,"")

adjust the ranges to suit

Bob Phillips said:

This array formula should do it

=IF(B1=MIN(IF(A1=$A$1:$A$20,$B$1:$B$20)),$C$1:$C$20,"")

adjust the ranges to suit

Click to expand...

To include the day difference:

=IF(B1=MIN(IF(A1=$A$1:$A$20,$B$1:$B$20)),B1-$C$1:$C$20,"")

Took another look at this to find a more robust solution that includes the date difference, and allows for the possibility of varying dates in Col C