Results 1 to 6 of 6

Thread: need most recent date with duplicate values

  1. #1

    need most recent date with duplicate values



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

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

  3. #3
    My apologies, earliest date.

  4. #4
    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

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Bob Phillips View Post
    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
    To include the day difference:

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

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •