Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
match true exact "dd" vs. find next 5th character=y
hi, not sure if know how/ what to ask, am trying to find the next cell in a
column, where the 5th letter = y MATCH(TRUE,EXACT($D$373:$D$826,"dd"),0) seems to work in a formula using, MATCH(TRUE,AND(LEN(R9)=5,MID(R9,5,1)="y"),0) but this does not do I need to somehow change value of R9 to reflect the array.. using column R following might be too much, problem is above; 1st example works, the 2nd does not; exact paste to a cell will show soft returns for easy edit; BU13 is column cursor ends up in / row that this formula has to be in; thanks; item is a hyperlink to the 1st max value in that column). =HYPERLINK(IF(ISNA(INDEX(ROW(BU$571:BU$587)-ROW(BU$571)+1,INDEX(ROW(BU$571:BU$587),MATCH(MAX(B U$571:BU$587),BU$571:BU$587,0)))),"", "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",BU13),"",""),ROW(),"")&INDEX (ROW(BU$571:BU$587),MATCH(MAX(BU$571:BU$587),BU$57 1:BU$587,0))),$W$2,0))), MAX(IF(ISNUMBER(BU$571:BU$587),BU$571:BU$587,-1E+100))) not working: (R17: col cursor ends up in, row 17 where this formula.. would need to be to work) thanks; =HYPERLINK( IF(ISNA(INDEX(ROW($R$373:$R$1402)-ROW($R$373)+1,INDEX(ROW($R$373:$R$1402),MATCH(TRUE ,AND(LEN(R9)=5,MID(R9,5,1)="y"),0)))),"", "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBS TITUTE(CELL("address",$R17),"$",""),ROW(),"")&INDE X(ROW($R$373:$R$1402),MATCH(TRUE,AND(LEN(R9)=5,MI D(R9,5,1)="y"),0))),$W$2,0))), IF(ISNA(INDEX(ROW($R$373:$R$1402)-ROW($R$373)+1,MATCH(TRUE,AND(LEN(R9)=5,MID(R9,5,1 )="y"),0))),"",INDEX(ROW($R$373:$R$1402),MATCH(TRU E,AND(LEN(R9)=5,MID(R9,5,1)="y"),0)))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
match true exact "dd" vs. find next 5th character=y
MATCH(TRUE,EXACT($D$373:$D$826,"dd"),0) seems to work in a formula using, MATCH(TRUE,AND(LEN(R9)=5,MID(R9,5,1)="y"),0) but this does not trying the following idea, not quite working yet: MATCH(TRUE,EXACT(AND(LEN($AY$373:$AY$1402)=5,MID( $AY$373:$AY$1402,5,1),"Y")),0) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
match true exact "dd" vs. find next 5th character=y
he problem is mid(R9,5,1)="y" should be replace with
if(find(mid(r9,5,1),"y") < 0) "nastech" wrote: MATCH(TRUE,EXACT($D$373:$D$826,"dd"),0) seems to work in a formula using, MATCH(TRUE,AND(LEN(R9)=5,MID(R9,5,1)="y"),0) but this does not trying the following idea, not quite working yet: MATCH(TRUE,EXACT(AND(LEN($AY$373:$AY$1402)=5,MID( $AY$373:$AY$1402,5,1),"Y")),0) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
match true exact "dd" vs. find next 5th character=y
hi, thanks for responding. will changing R9 to a range work with this?
"Joel" wrote: he problem is mid(R9,5,1)="y" should be replace with if(find(mid(r9,5,1),"y") < 0) "nastech" wrote: MATCH(TRUE,EXACT($D$373:$D$826,"dd"),0) seems to work in a formula using, MATCH(TRUE,AND(LEN(R9)=5,MID(R9,5,1)="y"),0) but this does not trying the following idea, not quite working yet: MATCH(TRUE,EXACT(AND(LEN($AY$373:$AY$1402)=5,MID( $AY$373:$AY$1402,5,1),"Y")),0) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
match true exact "dd" vs. find next 5th character=y
there is no need for the match function. You are just looking for tru or
false that will be return with the AND function =if(A5=1,,) will return either true of false. which is equivalent to - this is what you want =if(A5=1,and(true,true),and(true,true)) You don't do - this is what your code is generating =if(a5=1,match(true,true),match(false,false)) "nastech" wrote: hi, thanks for responding. will changing R9 to a range work with this? "Joel" wrote: he problem is mid(R9,5,1)="y" should be replace with if(find(mid(r9,5,1),"y") < 0) "nastech" wrote: MATCH(TRUE,EXACT($D$373:$D$826,"dd"),0) seems to work in a formula using, MATCH(TRUE,AND(LEN(R9)=5,MID(R9,5,1)="y"),0) but this does not trying the following idea, not quite working yet: MATCH(TRUE,EXACT(AND(LEN($AY$373:$AY$1402)=5,MID( $AY$373:$AY$1402,5,1),"Y")),0) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change the default in Excel from "find next" to "find all" | Excel Discussion (Misc queries) | |||
How to replace "#N/A" w "0"when vlookup couldn't find the match? | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Can you replace "TRUE" with " " in an exact formula? | Excel Worksheet Functions |