+ Reply to Thread
Results 1 to 4 of 4

text within multiple strings, look @ dates in next column, return most recent date

  1. #1
    Registered User
    Join Date
    06-04-2012
    Location
    Waterloo, iA
    MS-Off Ver
    Excel 2007
    Posts
    14

    text within multiple strings, look @ dates in next column, return most recent date

    Alright, this is similar to one I posted before but now I only need it to return one date, the most recent one.

    e45987r, rt4352, fr65830 12Dec10 rt4352
    rt7777, fr77321, e87212r 07Aug11
    rt4352, fr32121, e00098r 28Feb10

    In this example, rows 1 & 3 both contain the string rt4352 in different spots in the cell (there are much more than 3 rows in my actual data).

    What I want to do is identify each cell in column A that contains rt4352, look at the corresponding dates from column B, then return the most recent date. In this case, I want to return 12Dec10 (not 28Feb10)

    Here's what worked for finding both dates, but I don't know if I can tweak it to only return the most recent one.
    Quote Originally Posted by NBVC View Post
    You can try something like:

    Please Login or Register  to view this content.
    adjust ranges to suit... this assumes data is in A2:B100 and the search string of interest is in C2.

    The formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down

    Any suggestions?

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: text within multiple strings, look @ dates in next column, return most recent date

    If the Date column is valid dates & formatted as ddmmmyy, try

    =MAX(IF(ISNUMBER(SEARCH($C$2,$A$2:$A$100)),B$2:B$100))

    If date column is text entries, try

    =MAX(IF(ISNUMBER(SEARCH($C$2,$A$2:$A$100)*B$2:B$100),B$2:B$100+0))

    Both are with CTRL+SHIFT+ENTER
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    06-04-2012
    Location
    Waterloo, iA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: text within multiple strings, look @ dates in next column, return most recent date

    Sorry I didn't think about the formatting making a difference (duh)

    What if the date is in a custom format? m/d/yy

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: text within multiple strings, look @ dates in next column, return most recent date

    Formatting a cell doesn't affect the formulas. That is only for display purpose. You can format a cell as you wish. If column B is valid dates, the first formula should work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1