• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Max Date from Column Based on Embedded Text in Array

P

pmreeve

New Member
I am trying to pull the most recent "date" (column A) in the past if the "activity type" was meeting(column B) and the "participants" included *bob (column C), but bob is in a string of text, not always at the beginning, middle or included at all. I put them in descending order by date for ease of reference, but the dates are random in the report.The formula should ignore dates in the future.


Date: Most recent in past

Activity: =Meeting

Participant: Contains *Bob


The answer should be 1/1/13:

Date Activity Participants

1/1/2010 Call Jill; Bob; Mike

12/1/2012 Meeting Joe

1/1/2013 Meeting Bob; Joe; Jim

2/1/2013 Call Jim; Bob

3/15/2013 Call Julie; Bob

4/15/2013 Meeting Mike, Michelle; Bob

5/1/2013 Call Jill, Bob

6/1/2015 Meeting Mike; Bob
 
Hi pmreeve,


Use array formula as..


Code: 
=IFERROR(LARGE(IF((ActRange=Activity)*(ISNUMBER(SEARCH(Participant,PartRange))*(DateRange<TODAY())),DateRange),1),"")


https://dl.dropbox.com/u/78831150/Excel/Max%20Date%20from%20Column%20Based%20on%20Embedded%20Text%20in%20Array%20%28pmreeve%29.xls


Regards,

Deb
 
=MAX(IF(A2:A9<=TODAY(),IF(B2:B9="meeting",IF(ISNUMBER(SEARCH("Bob",C2:C9)),A2:A9))))

Followed by Control/Shift/Enter
 
OOPS...

I should have wait for some time.. Congrats.. for solving your own problem.. and thanks for sharing...


Regards,

Deb
 
You must log in or register to reply here.
Back
Top