+ Reply to Thread
Results 1 to 6 of 6

Identifying the latest date for a specific value

Hybrid View

  1. 08-12-2016, 05:37 AM #1
    JM83
    JM83 is offline
    Registered User
    Join Date
    08-12-2016
    Location
    Düsseldorf, Germany
    MS-Off Ver
    Excel 2010
    Posts
    2

    Identifying the latest date for a specific value

    Hi all

    I am struggling to find the correct formula for the following problem:

    I need to identify if the row got the latest report date for the project mentioned in column A. If this is the case the specific cell in column C should mention this (e.g. “Project A Latest”). Otherwise the cell should be left blank.

    Column A
    Projects
    Project B
    Project B
    Project B
    Project A
    Project A
    Project C
    Project C

    Column B
    Report date
    15-Sep-16
    31-Aug-16
    1-Apr-16
    1-Jul-15
    15-Sep-16
    12-Nov-16
    1-Nov-15

    Column C
    Identifier


    Many thanks!
    Reply With QuoteRegister To Reply
  2. 08-12-2016, 05:45 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,703

    Re: Identifying the latest date for a specific value

    Try this in C2:

    =IF(MAX(IF($A$2:$A$8=A2,$B$2:$B$8))=B2,A2&" Latest","")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 08-12-2016, 05:46 AM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Identifying the latest date for a specific value

    Use this array formula in C2, copied down:
    =IF(B2=MAX(IF($A$2:$A$8=A2,$B$2:$B$8)),A2&" latest","")

    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  4. 08-12-2016, 07:55 AM #4
    JM83
    JM83 is offline
    Registered User
    Join Date
    08-12-2016
    Location
    Düsseldorf, Germany
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Identifying the latest date for a specific value

    It works now! Thanks!
    Reply With QuoteRegister To Reply
  5. 08-12-2016, 07:57 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Identifying the latest date for a specific value

    You're welcome...
    Reply With QuoteRegister To Reply
  6. 08-12-2016, 08:11 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,703

    Re: Identifying the latest date for a specific value

    Glad to have helped!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA- Filter Pivot table based on latest and 2nd latest date in column
    By ziyan89 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 12-29-2015, 11:18 AM
  2. [SOLVED] Display latest date when specific text occurs
    By jhudson444 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-19-2015, 04:25 PM
  3. [SOLVED] Check Contact Name in Column, Return Latest Date and 2 Cells Next to the Latest Date
    By nKife in forum Excel General
    Replies: 8
    Last Post: 04-08-2015, 10:10 PM
  4. [SOLVED] Identifying earliest and latest times
    By Ron Purpura in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-13-2014, 10:56 AM
  5. how to extract latest date to a paticular date from diffrent date list
    By rudraalok in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-09-2014, 05:18 AM
  6. Open the latest file in a folder, and the second latest - up till the tenth latest saved
    By nikolajfh in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-28-2013, 05:20 PM
  7. Powerpivot - Returning latest update based on latest date
    By Kehjz in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 08-22-2013, 02:45 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1