Haseeb,
Can you help me to modify this slightly? I still need to use the date
range as specified in A2 and A4, but instead of doing the whole
substitution, I would like for it to either return the actual line to
the C column if it contains a remark+date in valid range, or a blank
line if it doesn't.
On Oct 21, 6:59 pm, Haseeb Avarakkan <haseeb.avarak...@gmail.com>
wrote:
> Hello Zeunasc;
>
> I am not good in to explain something. Here is an attempt. Hope this would
> help you.
>
> A2 = Start Date, here 10/20/2011
>
> A4 = End Date, here 10/25/2011
>
> Basically 10/20/2011 is 40836 (40836 days after 1/1/1900) &
>
> 10/25/2011 is 40841 (40841 days after 1/1/1900). If you change the cell
> format to General you can see this number.
>
> Defined a name “*CurrPeriod*” with Start & End date.
>
> *=INDEX(Sheet1!$A:$A,Sheet1!$A$2):INDEX(Sheet1!$A:$A,Sheet1!$A$4)*
>
> If you use INDEX before or after *:* it will give the actual cell reference,
> rather than it’s value. So here it become,
>
> INDEX(Sheet1!$A:$A,40836):INDEX(Sheet1!$A:$A,40841), which is
>
> A40836:A40841
>
> Note: you can also use INDIRECT here, =INDIRECT(Sheet1!$A$2&":"&Sheet1!$A$4)
> INDIRECT is volatile function. This will slow up the calculation.
>
> Take A8 formula as an example.
>
> =LOOKUP("zzzzz",CHOOSE({1,2},A8,SUBSTITUTE(TRIM(A8),"remark
> "&TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy"),"")))
>
> ROW(CurrPeriod) *|* This will give the array of the start & end dates,
> here, ROW(A40836:A40841), which is {40836;40837;40838;40839;40840;40841}
>
> TEXT(ROW(CurrPeriod),"mm/dd/yyyy") *|* Here TEXT function will convert
> the all the values to mm/dd/yyyy format, which is
> {"10/20/2011";"10/21/2011";"10/22/2011";"10/23/2011";"10/24/2011";"10/25/2011"}
>
> "*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*" *|* * will help to look for
> partial match. Array will become
> {"*10/20/2011*";"*10/21/2011*";"*10/22/2011*";"*10/23/2011*";"*10/24/2011*";"*10/25/2011*"}
>
> MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0) *|* If any of the
> above array contains in A8, will give 1 otherwise #N/A. So here will become
> {1;#N/A;#N/A;#N/A;#N/A;#N/A}. This means first value (10/20/2011) is
> contains in A8 rest of them doesn’t.
>
> LOOKUP is always looking for approximate value. Since we looking on a single
> cell MATCH always will give 1 or #N/A.
>
> LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod))
>
> | So this part will become
>
> LOOKUP(2,{1;#N/A;#N/A;#N/A;#N/A;#N/A},{40836;40837;40838;40839;40840;40841})
> | so here LOOKUP will give 40836 which is 10/20/2011
>
> TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy")
>
> *|* So here this will become
>
> TEXT(40836,”mm/dd/yyyy”) which is 10/20/2011
>
> "remark
> "&TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy")
>
> *|* will become here, remark 10/20/2011
>
> TRIM(A8) *| *This will avoid all unnecessay spaces If A8 contains.
>
> SUBSTITUTE(TRIM(A8),"remark
> "&TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy"),"")
>
> This will become here, *SUBSTITUTE(TRIM(A8),"remark 10/20/2011","") *which
> will replace remark 10/20/2011 to “”
>
> CHOOSE({1,2},A8,SUBSTITUTE(TRIM(A8),"remark
> "&TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy"),""))
>
> This will give array of A8 & Substituted value. If any of the number doesn’t
> contain in A8 LOOKUP will give #N/A error, so will give a array of A8 & #N/A
>
> =LOOKUP("zzzzz",CHOOSE({1,2},A8,SUBSTITUTE(TRIM(A8),"remark
> "&TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy"),"")))
>
> This will look for the text which is in last. If #N/A is in last will show
> the A8 value otherwise the substituted text.
>
> If you are using XL2007 or later, you can use IFERROR; like
>
> =IFERROR(SUBSTITUTE(TRIM(A8),"remark
> "&TEXT(LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/yyyy")&"*",A8,0),ROW(CurrPeriod)),"mm/dd/yyyy"),""),"")
>
> *LOOKUP function*;
>
> http://office.microsoft.com/en-us/excel-help/lookup-HP005209163.aspx
>
> *Volatile Functions*;
>
> http://www.decisionmodels.com/calcsecretsi.htm
>
> Hope this helps;
>
> Haseeb.
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
