+ Reply to Thread
Results 1 to 12 of 12

Match, Exact? NA error

  1. #1
    nastech
    Guest

    Match, Exact? NA error

    060724 Match, Exact? NA error
    hi, trying to make an "exact" match for the case, of a character, e.g.: "A"

    where the following works, am trying to isolate capital "A"'s.
    =MATCH("A",$AE$1:$AE$1299,0)

    the following receives a NA error. Is there a correct way to represent?
    am open to inserting IF statements, EXACT, or ROW<=().. etc.
    =MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)


    example of an EXACT, that was able to get to work in combo with other:
    =SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

  2. #2
    Biff
    Guest

    Re: Match, Exact? NA error

    What kind of data do you have in your range?

    Do the cells just contain the single letter "A" or do they contain strings
    like "Address" or "adult" ?

    So, if you're using MATCH then you just want the relative position of "A"
    within the array AE1:AE1299 ?

    Your posts always confuse me!

    Biff

    "nastech" <[email protected]> wrote in message
    news:[email protected]...
    > 060724 Match, Exact? NA error
    > hi, trying to make an "exact" match for the case, of a character, e.g.:
    > "A"
    >
    > where the following works, am trying to isolate capital "A"'s.
    > =MATCH("A",$AE$1:$AE$1299,0)
    >
    > the following receives a NA error. Is there a correct way to represent?
    > am open to inserting IF statements, EXACT, or ROW<=().. etc.
    > =MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)
    >
    >
    > example of an EXACT, that was able to get to work in combo with other:
    > =SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))




  3. #3
    nastech
    Guest

    Re: Match, Exact? NA error

    hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
    just relative position: yes if getting the row number, is what that means.

    problems that arise, I believe are from actually doing work but some
    functions only do half the job. e.g.: would think programming would have
    allowed easy exclusion of top rows where having absolute cell references that
    control that column have:
    A, a, B.. as part of the settings; but then, the formula will not work.
    I would have allowed programming to use: EXACT, ROW<(), then "" nothing...

    - either that or it's just some trick I haven't quite worked out.
    - or uniqueness of problems, haven't been thought of by others, ... thanks


    "Biff" wrote:

    > What kind of data do you have in your range?
    >
    > Do the cells just contain the single letter "A" or do they contain strings
    > like "Address" or "adult" ?
    >
    > So, if you're using MATCH then you just want the relative position of "A"
    > within the array AE1:AE1299 ?
    >
    > Your posts always confuse me!
    >
    > Biff
    >
    > "nastech" <[email protected]> wrote in message
    > news:[email protected]...
    > > 060724 Match, Exact? NA error
    > > hi, trying to make an "exact" match for the case, of a character, e.g.:
    > > "A"
    > >
    > > where the following works, am trying to isolate capital "A"'s.
    > > =MATCH("A",$AE$1:$AE$1299,0)
    > >
    > > the following receives a NA error. Is there a correct way to represent?
    > > am open to inserting IF statements, EXACT, or ROW<=().. etc.
    > > =MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)
    > >
    > >
    > > example of an EXACT, that was able to get to work in combo with other:
    > > =SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

    >
    >
    >


  4. #4
    Biff
    Guest

    Re: Match, Exact? NA error

    OK.....

    If there's only one instance of "A":

    =SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))

    If there might be more than one instance of "A":

    This will find the first instance:

    Entered as an array using the key combination of CTRL,SHIFT,ENTER:

    =INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299,"A"),0))

    Biff

    "nastech" <[email protected]> wrote in message
    news:[email protected]...
    > hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
    > just relative position: yes if getting the row number, is what that
    > means.
    >
    > problems that arise, I believe are from actually doing work but some
    > functions only do half the job. e.g.: would think programming would have
    > allowed easy exclusion of top rows where having absolute cell references
    > that
    > control that column have:
    > A, a, B.. as part of the settings; but then, the formula will not work.
    > I would have allowed programming to use: EXACT, ROW<(), then ""
    > nothing...
    >
    > - either that or it's just some trick I haven't quite worked out.
    > - or uniqueness of problems, haven't been thought of by others, ...
    > thanks
    >
    >
    > "Biff" wrote:
    >
    >> What kind of data do you have in your range?
    >>
    >> Do the cells just contain the single letter "A" or do they contain
    >> strings
    >> like "Address" or "adult" ?
    >>
    >> So, if you're using MATCH then you just want the relative position of "A"
    >> within the array AE1:AE1299 ?
    >>
    >> Your posts always confuse me!
    >>
    >> Biff
    >>
    >> "nastech" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > 060724 Match, Exact? NA error
    >> > hi, trying to make an "exact" match for the case, of a character, e.g.:
    >> > "A"
    >> >
    >> > where the following works, am trying to isolate capital "A"'s.
    >> > =MATCH("A",$AE$1:$AE$1299,0)
    >> >
    >> > the following receives a NA error. Is there a correct way to
    >> > represent?
    >> > am open to inserting IF statements, EXACT, or ROW<=().. etc.
    >> > =MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)
    >> >
    >> >
    >> > example of an EXACT, that was able to get to work in combo with other:
    >> > =SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))

    >>
    >>
    >>




  5. #5
    nastech
    Guest

    Re: Match, Exact? NA error

    Hi, thanks.. sorry, many instances of each letter, so the 2nd example worked;

    but wondering if way to exclude top rows, in case have cells with same
    letter "A" as settings? trying to insert ROW>5...

    "Biff" wrote:

    > OK.....
    >
    > If there's only one instance of "A":
    >
    > =SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))
    >
    > If there might be more than one instance of "A":
    >
    > This will find the first instance:
    >
    > Entered as an array using the key combination of CTRL,SHIFT,ENTER:
    >
    > =INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299,"A"),0))
    >
    > Biff
    >
    > "nastech" <[email protected]> wrote in message
    > news:[email protected]...
    > > hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
    > > just relative position: yes if getting the row number, is what that
    > > means.
    > >
    > > problems that arise, I believe are from actually doing work but some
    > > functions only do half the job. e.g.: would think programming would have
    > > allowed easy exclusion of top rows where having absolute cell references
    > > that
    > > control that column have:
    > > A, a, B.. as part of the settings; but then, the formula will not work.
    > > I would have allowed programming to use: EXACT, ROW<(), then ""
    > > nothing...
    > >
    > > - either that or it's just some trick I haven't quite worked out.
    > > - or uniqueness of problems, haven't been thought of by others, ...
    > > thanks
    > >
    > >
    > > "Biff" wrote:
    > >
    > >> What kind of data do you have in your range?
    > >>
    > >> Do the cells just contain the single letter "A" or do they contain
    > >> strings
    > >> like "Address" or "adult" ?
    > >>
    > >> So, if you're using MATCH then you just want the relative position of "A"
    > >> within the array AE1:AE1299 ?
    > >>
    > >> Your posts always confuse me!
    > >>
    > >> Biff
    > >>
    > >> "nastech" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > 060724 Match, Exact? NA error
    > >> > hi, trying to make an "exact" match for the case, of a character, e.g.:
    > >> > "A"
    > >> >
    > >> > where the following works, am trying to isolate capital "A"'s.
    > >> > =MATCH("A",$AE$1:$AE$1299,0)
    > >> >
    > >> > the following receives a NA error. Is there a correct way to
    > >> > represent?
    > >> > am open to inserting IF statements, EXACT, or ROW<=().. etc.
    > >> > =MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)
    > >> >
    > >> >
    > >> > example of an EXACT, that was able to get to work in combo with other:
    > >> > =SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))
    > >>
    > >>
    > >>

    >
    >
    >


  6. #6
    Biff
    Guest

    Re: Match, Exact? NA error

    Just change the range to start at AE6:

    For the RELATIVE row location:

    =INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))

    For the ACTUAL row location:

    =INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))

    Biff

    "nastech" <[email protected]> wrote in message
    news:[email protected]...
    > Hi, thanks.. sorry, many instances of each letter, so the 2nd example
    > worked;
    >
    > but wondering if way to exclude top rows, in case have cells with same
    > letter "A" as settings? trying to insert ROW>5...
    >
    > "Biff" wrote:
    >
    >> OK.....
    >>
    >> If there's only one instance of "A":
    >>
    >> =SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))
    >>
    >> If there might be more than one instance of "A":
    >>
    >> This will find the first instance:
    >>
    >> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
    >>
    >> =INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299,"A"),0))
    >>
    >> Biff
    >>
    >> "nastech" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
    >> > just relative position: yes if getting the row number, is what that
    >> > means.
    >> >
    >> > problems that arise, I believe are from actually doing work but some
    >> > functions only do half the job. e.g.: would think programming would
    >> > have
    >> > allowed easy exclusion of top rows where having absolute cell
    >> > references
    >> > that
    >> > control that column have:
    >> > A, a, B.. as part of the settings; but then, the formula will not
    >> > work.
    >> > I would have allowed programming to use: EXACT, ROW<(), then ""
    >> > nothing...
    >> >
    >> > - either that or it's just some trick I haven't quite worked out.
    >> > - or uniqueness of problems, haven't been thought of by others, ...
    >> > thanks
    >> >
    >> >
    >> > "Biff" wrote:
    >> >
    >> >> What kind of data do you have in your range?
    >> >>
    >> >> Do the cells just contain the single letter "A" or do they contain
    >> >> strings
    >> >> like "Address" or "adult" ?
    >> >>
    >> >> So, if you're using MATCH then you just want the relative position of
    >> >> "A"
    >> >> within the array AE1:AE1299 ?
    >> >>
    >> >> Your posts always confuse me!
    >> >>
    >> >> Biff
    >> >>
    >> >> "nastech" <[email protected]> wrote in message
    >> >> news:[email protected]...
    >> >> > 060724 Match, Exact? NA error
    >> >> > hi, trying to make an "exact" match for the case, of a character,
    >> >> > e.g.:
    >> >> > "A"
    >> >> >
    >> >> > where the following works, am trying to isolate capital "A"'s.
    >> >> > =MATCH("A",$AE$1:$AE$1299,0)
    >> >> >
    >> >> > the following receives a NA error. Is there a correct way to
    >> >> > represent?
    >> >> > am open to inserting IF statements, EXACT, or ROW<=().. etc.
    >> >> > =MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)
    >> >> >
    >> >> >
    >> >> > example of an EXACT, that was able to get to work in combo with
    >> >> > other:
    >> >> > =SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  7. #7
    nastech
    Guest

    Re: Match, Exact? NA error

    Thankyou very much.. will be able to accomplish daily tasks (dozens -
    hundreds of them) much faster once finish getting set up. the formula's are
    part of a hyper-link I thought up with some help, to find values quickly.

    Hyperlinks to exact locations in a document (link that move as lines move):
    =HYPERLINK(IF(ROW($A$650)<=ROW($A65),
    "#"&CELL("address",OFFSET($A$650,-1,1)),"#"&CELL("address",OFFSET($A$650,$V$1,1))),"A")

    where $V$1 is a set number of lines to offset, with equation:
    =48-CELL("row",$A$17) to automate offset value

    where ROW($A65), must be the row formula link is currently in.
    NOTE: to make work "IN-DOCUMENT" lines, where link is in SAME TITLE LINE/
    After, as destination put 2nd half of formula 1st, and reverse <=ROW to >=
    (SAVES large amount of time, when working on large sheets).

    ----- for your help, item:

    The only thing that will remain is to include in column where muliple A B C
    D's repeat, are to negate them as I pass thru them with a date function.

    maybe something like: IF((NOW()+1)>(AZ9+AZ$5)),"",formula
    no problem.


    =HYPERLINK(IF(ISNA(INDEX(ROW($AE$1:$AE$1303),MATCH(TRUE,EXACT($AE$1:$AE$1303,"A"),0))),"",
    "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$V14),"$",""),ROW(),"")&INDEX(ROW($AE$1:$AE$1303),MATCH(TRUE,EXACT($AE$1:$AE$1303,"A"),0))),$V$1,0))),"A")

    will find the 1st capital "A" in that column. have to add exclusion you
    qouted for ROWS.


    -----------------------------

    "Biff" wrote:

    > Just change the range to start at AE6:
    >
    > For the RELATIVE row location:
    >
    > =INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))
    >
    > For the ACTUAL row location:
    >
    > =INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))
    >
    > Biff
    >
    > "nastech" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi, thanks.. sorry, many instances of each letter, so the 2nd example
    > > worked;
    > >
    > > but wondering if way to exclude top rows, in case have cells with same
    > > letter "A" as settings? trying to insert ROW>5...
    > >
    > > "Biff" wrote:
    > >
    > >> OK.....
    > >>
    > >> If there's only one instance of "A":
    > >>
    > >> =SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))
    > >>
    > >> If there might be more than one instance of "A":
    > >>
    > >> This will find the first instance:
    > >>
    > >> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
    > >>
    > >> =INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299,"A"),0))
    > >>
    > >> Biff
    > >>
    > >> "nastech" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
    > >> > just relative position: yes if getting the row number, is what that
    > >> > means.
    > >> >
    > >> > problems that arise, I believe are from actually doing work but some
    > >> > functions only do half the job. e.g.: would think programming would
    > >> > have
    > >> > allowed easy exclusion of top rows where having absolute cell
    > >> > references
    > >> > that
    > >> > control that column have:
    > >> > A, a, B.. as part of the settings; but then, the formula will not
    > >> > work.
    > >> > I would have allowed programming to use: EXACT, ROW<(), then ""
    > >> > nothing...
    > >> >
    > >> > - either that or it's just some trick I haven't quite worked out.
    > >> > - or uniqueness of problems, haven't been thought of by others, ...
    > >> > thanks
    > >> >
    > >> >
    > >> > "Biff" wrote:
    > >> >
    > >> >> What kind of data do you have in your range?
    > >> >>
    > >> >> Do the cells just contain the single letter "A" or do they contain
    > >> >> strings
    > >> >> like "Address" or "adult" ?
    > >> >>
    > >> >> So, if you're using MATCH then you just want the relative position of
    > >> >> "A"
    > >> >> within the array AE1:AE1299 ?
    > >> >>
    > >> >> Your posts always confuse me!
    > >> >>
    > >> >> Biff
    > >> >>
    > >> >> "nastech" <[email protected]> wrote in message
    > >> >> news:[email protected]...
    > >> >> > 060724 Match, Exact? NA error
    > >> >> > hi, trying to make an "exact" match for the case, of a character,
    > >> >> > e.g.:
    > >> >> > "A"
    > >> >> >
    > >> >> > where the following works, am trying to isolate capital "A"'s.
    > >> >> > =MATCH("A",$AE$1:$AE$1299,0)
    > >> >> >
    > >> >> > the following receives a NA error. Is there a correct way to
    > >> >> > represent?
    > >> >> > am open to inserting IF statements, EXACT, or ROW<=().. etc.
    > >> >> > =MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)
    > >> >> >
    > >> >> >
    > >> >> > example of an EXACT, that was able to get to work in combo with
    > >> >> > other:
    > >> >> > =SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >


  8. #8
    nastech
    Guest

    Re: Match, Exact? NA error

    As I did not think we would get this far, did not want to include too much,
    but searching for an error in 1 column (Similar formula) not working.

    is there something I am doing wrong with the IF(ISNA ... ,10000?
    not getting the rows to skip the header with your suggestion, in this
    circumstannce.
    Thanks much

    cannot quite figure out the following. may be has some to do with negating
    ROWS you showed, but think something else the problem as well.

    Finding error in column, (hyperlink) does not quite work:

    =HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303),ROW($AE$56:$AE$1303)-ROW(AE56)+1,10000))),"",
    "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN(IF(ISERROR($AE$56:$AE$1303),ROW($AE$56:$AE$1303),""))),$V$1,0))),"E")

    although have the following at the top, it is not the whole problem.
    =SUMPRODUCT(--(EXACT(LEFT($AE$115:$AE$1303,1),{"A","B","C"})))

    - comes up with same error, if error 1,000 lines below has an error,
    iterferes with the hyperlink search formula.
    - the next formula works before line 115 when there is an error, but the
    "NOT" is not right.
    - if no error, link goes to some erroneous position.

    The following works before line 115.

    =HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303),ROW($AE$1:$AE$1303),10000))),"",
    "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN(IF(ISERROR($AE$1:$AE$1303),ROW($AE$1:$AE$1303),""))),$V$1,0))),"E")

    ------------------------------------

    "Biff" wrote:

    > Just change the range to start at AE6:
    >
    > For the RELATIVE row location:
    >
    > =INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))
    >
    > For the ACTUAL row location:
    >
    > =INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))
    >
    > Biff
    >
    > "nastech" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi, thanks.. sorry, many instances of each letter, so the 2nd example
    > > worked;
    > >
    > > but wondering if way to exclude top rows, in case have cells with same
    > > letter "A" as settings? trying to insert ROW>5...
    > >
    > > "Biff" wrote:
    > >
    > >> OK.....
    > >>
    > >> If there's only one instance of "A":
    > >>
    > >> =SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))
    > >>
    > >> If there might be more than one instance of "A":
    > >>
    > >> This will find the first instance:
    > >>
    > >> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
    > >>
    > >> =INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299,"A"),0))
    > >>
    > >> Biff
    > >>
    > >> "nastech" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
    > >> > just relative position: yes if getting the row number, is what that
    > >> > means.
    > >> >
    > >> > problems that arise, I believe are from actually doing work but some
    > >> > functions only do half the job. e.g.: would think programming would
    > >> > have
    > >> > allowed easy exclusion of top rows where having absolute cell
    > >> > references
    > >> > that
    > >> > control that column have:
    > >> > A, a, B.. as part of the settings; but then, the formula will not
    > >> > work.
    > >> > I would have allowed programming to use: EXACT, ROW<(), then ""
    > >> > nothing...
    > >> >
    > >> > - either that or it's just some trick I haven't quite worked out.
    > >> > - or uniqueness of problems, haven't been thought of by others, ...
    > >> > thanks
    > >> >
    > >> >
    > >> > "Biff" wrote:
    > >> >
    > >> >> What kind of data do you have in your range?
    > >> >>
    > >> >> Do the cells just contain the single letter "A" or do they contain
    > >> >> strings
    > >> >> like "Address" or "adult" ?
    > >> >>
    > >> >> So, if you're using MATCH then you just want the relative position of
    > >> >> "A"
    > >> >> within the array AE1:AE1299 ?
    > >> >>
    > >> >> Your posts always confuse me!
    > >> >>
    > >> >> Biff
    > >> >>
    > >> >> "nastech" <[email protected]> wrote in message
    > >> >> news:[email protected]...
    > >> >> > 060724 Match, Exact? NA error
    > >> >> > hi, trying to make an "exact" match for the case, of a character,
    > >> >> > e.g.:
    > >> >> > "A"
    > >> >> >
    > >> >> > where the following works, am trying to isolate capital "A"'s.
    > >> >> > =MATCH("A",$AE$1:$AE$1299,0)
    > >> >> >
    > >> >> > the following receives a NA error. Is there a correct way to
    > >> >> > represent?
    > >> >> > am open to inserting IF statements, EXACT, or ROW<=().. etc.
    > >> >> > =MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)
    > >> >> >
    > >> >> >
    > >> >> > example of an EXACT, that was able to get to work in combo with
    > >> >> > other:
    > >> >> > =SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >


  9. #9
    Biff
    Guest

    Re: Match, Exact? NA error

    You've lost me!

    If you have errors in a range and that screws things up, fix the errors!

    Biff

    "nastech" <[email protected]> wrote in message
    news:[email protected]...
    > As I did not think we would get this far, did not want to include too
    > much,
    > but searching for an error in 1 column (Similar formula) not working.
    >
    > is there something I am doing wrong with the IF(ISNA ... ,10000?
    > not getting the rows to skip the header with your suggestion, in this
    > circumstannce.
    > Thanks much
    >
    > cannot quite figure out the following. may be has some to do with
    > negating
    > ROWS you showed, but think something else the problem as well.
    >
    > Finding error in column, (hyperlink) does not quite work:
    >
    > =HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303),ROW($AE$56:$AE$1303)-ROW(AE56)+1,10000))),"",
    > "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN(IF(ISERROR($AE$56:$AE$1303),ROW($AE$56:$AE$1303),""))),$V$1,0))),"E")
    >
    > although have the following at the top, it is not the whole problem.
    > =SUMPRODUCT(--(EXACT(LEFT($AE$115:$AE$1303,1),{"A","B","C"})))
    >
    > - comes up with same error, if error 1,000 lines below has an error,
    > iterferes with the hyperlink search formula.
    > - the next formula works before line 115 when there is an error, but the
    > "NOT" is not right.
    > - if no error, link goes to some erroneous position.
    >
    > The following works before line 115.
    >
    > =HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303),ROW($AE$1:$AE$1303),10000))),"",
    > "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN(IF(ISERROR($AE$1:$AE$1303),ROW($AE$1:$AE$1303),""))),$V$1,0))),"E")
    >
    > ------------------------------------
    >
    > "Biff" wrote:
    >
    >> Just change the range to start at AE6:
    >>
    >> For the RELATIVE row location:
    >>
    >> =INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))
    >>
    >> For the ACTUAL row location:
    >>
    >> =INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))
    >>
    >> Biff
    >>
    >> "nastech" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Hi, thanks.. sorry, many instances of each letter, so the 2nd example
    >> > worked;
    >> >
    >> > but wondering if way to exclude top rows, in case have cells with same
    >> > letter "A" as settings? trying to insert ROW>5...
    >> >
    >> > "Biff" wrote:
    >> >
    >> >> OK.....
    >> >>
    >> >> If there's only one instance of "A":
    >> >>
    >> >> =SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))
    >> >>
    >> >> If there might be more than one instance of "A":
    >> >>
    >> >> This will find the first instance:
    >> >>
    >> >> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
    >> >>
    >> >> =INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299,"A"),0))
    >> >>
    >> >> Biff
    >> >>
    >> >> "nastech" <[email protected]> wrote in message
    >> >> news:[email protected]...
    >> >> > hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
    >> >> > just relative position: yes if getting the row number, is what that
    >> >> > means.
    >> >> >
    >> >> > problems that arise, I believe are from actually doing work but some
    >> >> > functions only do half the job. e.g.: would think programming
    >> >> > would
    >> >> > have
    >> >> > allowed easy exclusion of top rows where having absolute cell
    >> >> > references
    >> >> > that
    >> >> > control that column have:
    >> >> > A, a, B.. as part of the settings; but then, the formula will not
    >> >> > work.
    >> >> > I would have allowed programming to use: EXACT, ROW<(), then ""
    >> >> > nothing...
    >> >> >
    >> >> > - either that or it's just some trick I haven't quite worked out.
    >> >> > - or uniqueness of problems, haven't been thought of by others, ...
    >> >> > thanks
    >> >> >
    >> >> >
    >> >> > "Biff" wrote:
    >> >> >
    >> >> >> What kind of data do you have in your range?
    >> >> >>
    >> >> >> Do the cells just contain the single letter "A" or do they contain
    >> >> >> strings
    >> >> >> like "Address" or "adult" ?
    >> >> >>
    >> >> >> So, if you're using MATCH then you just want the relative position
    >> >> >> of
    >> >> >> "A"
    >> >> >> within the array AE1:AE1299 ?
    >> >> >>
    >> >> >> Your posts always confuse me!
    >> >> >>
    >> >> >> Biff
    >> >> >>
    >> >> >> "nastech" <[email protected]> wrote in message
    >> >> >> news:[email protected]...
    >> >> >> > 060724 Match, Exact? NA error
    >> >> >> > hi, trying to make an "exact" match for the case, of a character,
    >> >> >> > e.g.:
    >> >> >> > "A"
    >> >> >> >
    >> >> >> > where the following works, am trying to isolate capital "A"'s.
    >> >> >> > =MATCH("A",$AE$1:$AE$1299,0)
    >> >> >> >
    >> >> >> > the following receives a NA error. Is there a correct way to
    >> >> >> > represent?
    >> >> >> > am open to inserting IF statements, EXACT, or ROW<=().. etc.
    >> >> >> > =MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)
    >> >> >> >
    >> >> >> >
    >> >> >> > example of an EXACT, that was able to get to work in combo with
    >> >> >> > other:
    >> >> >> > =SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))
    >> >> >>
    >> >> >>
    >> >> >>
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  10. #10
    nastech
    Guest

    Re: Match, Exact? NA error

    hi, sorry, giving too much info, rather not be asked why, guesse just the
    formula matters.. didn't want to confuse anyone, but have reasons that don't
    matter to tell why. you probably don't want to know the work / (amount of)
    that am doing, the amount of changes that take place, "not" errors? get
    induced.. divide by zero monsters you didn't see comming? anyways, just
    looking for a tool.
    the problem is what was trying to find.
    as said, it is in some line, 1000 lines or so, down somewhere.
    you might be asking for too much info. more?
    sometimes have intermittent induced errors,





    "Biff" wrote:

    > You've lost me!
    >
    > If you have errors in a range and that screws things up, fix the errors!
    >
    > Biff
    >
    > "nastech" <[email protected]> wrote in message
    > news:[email protected]...
    > > As I did not think we would get this far, did not want to include too
    > > much,
    > > but searching for an error in 1 column (Similar formula) not working.
    > >
    > > is there something I am doing wrong with the IF(ISNA ... ,10000?
    > > not getting the rows to skip the header with your suggestion, in this
    > > circumstannce.
    > > Thanks much
    > >
    > > cannot quite figure out the following. may be has some to do with
    > > negating
    > > ROWS you showed, but think something else the problem as well.
    > >
    > > Finding error in column, (hyperlink) does not quite work:
    > >
    > > =HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303),ROW($AE$56:$AE$1303)-ROW(AE56)+1,10000))),"",
    > > "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN(IF(ISERROR($AE$56:$AE$1303),ROW($AE$56:$AE$1303),""))),$V$1,0))),"E")
    > >
    > > although have the following at the top, it is not the whole problem.
    > > =SUMPRODUCT(--(EXACT(LEFT($AE$115:$AE$1303,1),{"A","B","C"})))
    > >
    > > - comes up with same error, if error 1,000 lines below has an error,
    > > iterferes with the hyperlink search formula.
    > > - the next formula works before line 115 when there is an error, but the
    > > "NOT" is not right.
    > > - if no error, link goes to some erroneous position.
    > >
    > > The following works before line 115.
    > >
    > > =HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303),ROW($AE$1:$AE$1303),10000))),"",
    > > "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN(IF(ISERROR($AE$1:$AE$1303),ROW($AE$1:$AE$1303),""))),$V$1,0))),"E")
    > >
    > > ------------------------------------
    > >
    > > "Biff" wrote:
    > >
    > >> Just change the range to start at AE6:
    > >>
    > >> For the RELATIVE row location:
    > >>
    > >> =INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))
    > >>
    > >> For the ACTUAL row location:
    > >>
    > >> =INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))
    > >>
    > >> Biff
    > >>
    > >> "nastech" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > Hi, thanks.. sorry, many instances of each letter, so the 2nd example
    > >> > worked;
    > >> >
    > >> > but wondering if way to exclude top rows, in case have cells with same
    > >> > letter "A" as settings? trying to insert ROW>5...
    > >> >
    > >> > "Biff" wrote:
    > >> >
    > >> >> OK.....
    > >> >>
    > >> >> If there's only one instance of "A":
    > >> >>
    > >> >> =SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))
    > >> >>
    > >> >> If there might be more than one instance of "A":
    > >> >>
    > >> >> This will find the first instance:
    > >> >>
    > >> >> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
    > >> >>
    > >> >> =INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299,"A"),0))
    > >> >>
    > >> >> Biff
    > >> >>
    > >> >> "nastech" <[email protected]> wrote in message
    > >> >> news:[email protected]...
    > >> >> > hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
    > >> >> > just relative position: yes if getting the row number, is what that
    > >> >> > means.
    > >> >> >
    > >> >> > problems that arise, I believe are from actually doing work but some
    > >> >> > functions only do half the job. e.g.: would think programming
    > >> >> > would
    > >> >> > have
    > >> >> > allowed easy exclusion of top rows where having absolute cell
    > >> >> > references
    > >> >> > that
    > >> >> > control that column have:
    > >> >> > A, a, B.. as part of the settings; but then, the formula will not
    > >> >> > work.
    > >> >> > I would have allowed programming to use: EXACT, ROW<(), then ""
    > >> >> > nothing...
    > >> >> >
    > >> >> > - either that or it's just some trick I haven't quite worked out.
    > >> >> > - or uniqueness of problems, haven't been thought of by others, ...
    > >> >> > thanks
    > >> >> >
    > >> >> >
    > >> >> > "Biff" wrote:
    > >> >> >
    > >> >> >> What kind of data do you have in your range?
    > >> >> >>
    > >> >> >> Do the cells just contain the single letter "A" or do they contain
    > >> >> >> strings
    > >> >> >> like "Address" or "adult" ?
    > >> >> >>
    > >> >> >> So, if you're using MATCH then you just want the relative position
    > >> >> >> of
    > >> >> >> "A"
    > >> >> >> within the array AE1:AE1299 ?
    > >> >> >>
    > >> >> >> Your posts always confuse me!
    > >> >> >>
    > >> >> >> Biff
    > >> >> >>
    > >> >> >> "nastech" <[email protected]> wrote in message
    > >> >> >> news:[email protected]...
    > >> >> >> > 060724 Match, Exact? NA error
    > >> >> >> > hi, trying to make an "exact" match for the case, of a character,
    > >> >> >> > e.g.:
    > >> >> >> > "A"
    > >> >> >> >
    > >> >> >> > where the following works, am trying to isolate capital "A"'s.
    > >> >> >> > =MATCH("A",$AE$1:$AE$1299,0)
    > >> >> >> >
    > >> >> >> > the following receives a NA error. Is there a correct way to
    > >> >> >> > represent?
    > >> >> >> > am open to inserting IF statements, EXACT, or ROW<=().. etc.
    > >> >> >> > =MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)
    > >> >> >> >
    > >> >> >> >
    > >> >> >> > example of an EXACT, that was able to get to work in combo with
    > >> >> >> > other:
    > >> >> >> > =SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))
    > >> >> >>
    > >> >> >>
    > >> >> >>
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >


  11. #11
    nastech
    Guest

    Re: Match, Exact? NA error

    um.. retract, up 24 hours... items searched for in errors keep popping up
    (different ways), just trying to quickly find the lines to quickly fix /
    delete. am having some trouble adding idea into formula's: -ROW(AE6)+1),
    just attention to detail thing with different variations. will work on it,
    thanks.

    "Biff" wrote:

    > You've lost me!
    >
    > If you have errors in a range and that screws things up, fix the errors!
    >
    > Biff
    >
    > "nastech" <[email protected]> wrote in message
    > news:[email protected]...
    > > As I did not think we would get this far, did not want to include too
    > > much,
    > > but searching for an error in 1 column (Similar formula) not working.
    > >
    > > is there something I am doing wrong with the IF(ISNA ... ,10000?
    > > not getting the rows to skip the header with your suggestion, in this
    > > circumstannce.
    > > Thanks much
    > >
    > > cannot quite figure out the following. may be has some to do with
    > > negating
    > > ROWS you showed, but think something else the problem as well.
    > >
    > > Finding error in column, (hyperlink) does not quite work:
    > >
    > > =HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303),ROW($AE$56:$AE$1303)-ROW(AE56)+1,10000))),"",
    > > "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN(IF(ISERROR($AE$56:$AE$1303),ROW($AE$56:$AE$1303),""))),$V$1,0))),"E")
    > >
    > > although have the following at the top, it is not the whole problem.
    > > =SUMPRODUCT(--(EXACT(LEFT($AE$115:$AE$1303,1),{"A","B","C"})))
    > >
    > > - comes up with same error, if error 1,000 lines below has an error,
    > > iterferes with the hyperlink search formula.
    > > - the next formula works before line 115 when there is an error, but the
    > > "NOT" is not right.
    > > - if no error, link goes to some erroneous position.
    > >
    > > The following works before line 115.
    > >
    > > =HYPERLINK(IF(ISNA(MIN(IF(ISERROR($AE$1:$AE$1303),ROW($AE$1:$AE$1303),10000))),"",
    > > "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",AE15),"$",""),ROW(),"")&MIN(IF(ISERROR($AE$1:$AE$1303),ROW($AE$1:$AE$1303),""))),$V$1,0))),"E")
    > >
    > > ------------------------------------
    > >
    > > "Biff" wrote:
    > >
    > >> Just change the range to start at AE6:
    > >>
    > >> For the RELATIVE row location:
    > >>
    > >> =INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))
    > >>
    > >> For the ACTUAL row location:
    > >>
    > >> =INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))
    > >>
    > >> Biff
    > >>
    > >> "nastech" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > Hi, thanks.. sorry, many instances of each letter, so the 2nd example
    > >> > worked;
    > >> >
    > >> > but wondering if way to exclude top rows, in case have cells with same
    > >> > letter "A" as settings? trying to insert ROW>5...
    > >> >
    > >> > "Biff" wrote:
    > >> >
    > >> >> OK.....
    > >> >>
    > >> >> If there's only one instance of "A":
    > >> >>
    > >> >> =SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))
    > >> >>
    > >> >> If there might be more than one instance of "A":
    > >> >>
    > >> >> This will find the first instance:
    > >> >>
    > >> >> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
    > >> >>
    > >> >> =INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299,"A"),0))
    > >> >>
    > >> >> Biff
    > >> >>
    > >> >> "nastech" <[email protected]> wrote in message
    > >> >> news:[email protected]...
    > >> >> > hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
    > >> >> > just relative position: yes if getting the row number, is what that
    > >> >> > means.
    > >> >> >
    > >> >> > problems that arise, I believe are from actually doing work but some
    > >> >> > functions only do half the job. e.g.: would think programming
    > >> >> > would
    > >> >> > have
    > >> >> > allowed easy exclusion of top rows where having absolute cell
    > >> >> > references
    > >> >> > that
    > >> >> > control that column have:
    > >> >> > A, a, B.. as part of the settings; but then, the formula will not
    > >> >> > work.
    > >> >> > I would have allowed programming to use: EXACT, ROW<(), then ""
    > >> >> > nothing...
    > >> >> >
    > >> >> > - either that or it's just some trick I haven't quite worked out.
    > >> >> > - or uniqueness of problems, haven't been thought of by others, ...
    > >> >> > thanks
    > >> >> >
    > >> >> >
    > >> >> > "Biff" wrote:
    > >> >> >
    > >> >> >> What kind of data do you have in your range?
    > >> >> >>
    > >> >> >> Do the cells just contain the single letter "A" or do they contain
    > >> >> >> strings
    > >> >> >> like "Address" or "adult" ?
    > >> >> >>
    > >> >> >> So, if you're using MATCH then you just want the relative position
    > >> >> >> of
    > >> >> >> "A"
    > >> >> >> within the array AE1:AE1299 ?
    > >> >> >>
    > >> >> >> Your posts always confuse me!
    > >> >> >>
    > >> >> >> Biff
    > >> >> >>
    > >> >> >> "nastech" <[email protected]> wrote in message
    > >> >> >> news:[email protected]...
    > >> >> >> > 060724 Match, Exact? NA error
    > >> >> >> > hi, trying to make an "exact" match for the case, of a character,
    > >> >> >> > e.g.:
    > >> >> >> > "A"
    > >> >> >> >
    > >> >> >> > where the following works, am trying to isolate capital "A"'s.
    > >> >> >> > =MATCH("A",$AE$1:$AE$1299,0)
    > >> >> >> >
    > >> >> >> > the following receives a NA error. Is there a correct way to
    > >> >> >> > represent?
    > >> >> >> > am open to inserting IF statements, EXACT, or ROW<=().. etc.
    > >> >> >> > =MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)
    > >> >> >> >
    > >> >> >> >
    > >> >> >> > example of an EXACT, that was able to get to work in combo with
    > >> >> >> > other:
    > >> >> >> > =SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))
    > >> >> >>
    > >> >> >>
    > >> >> >>
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >


  12. #12
    nastech
    Guest

    Re: Match, Exact? NA error

    hi, thanks for your help before, found pieces for what was doing.. not sure
    if interested buy just in case. reasons were not for errors so much as tests
    for negative conditions that pop up often; needed a way to quickly navigate.
    here is a copy of what I found... thanks for the help.


    ANSWER TO: Find Error result in column, where calculated (pick a start
    point & exclude a range), Hyperlink to it (with row number as friendly name
    in hyperlink)

    =HYPERLINK(IF(AND(
    ISNA(INDEX(ROW($AX$173:$AX$540)-ROW(AX173)+1,MATCH(TRUE,EXACT($AX$173:$AX$540,"X"),0))),
    ISNA(INDEX(ROW($AX$567:$AX$1168)-ROW(AX567)+1,MATCH(TRUE,EXACT($AX$567:$AX$1168,"X"),0)))),"",
    "#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$W14),"$",""),ROW(),"")&INDEX(ROW($AX$173:$AX$1168),MATCH(TRUE,EXACT($AX$173:$AX$1168,"X"),0))),$W$1,0))),
    IF($BX$15>0,IF(AND(
    ISNA(INDEX(ROW($AX$173:$AX$540)-ROW(AX173)+1,MATCH(TRUE,EXACT($AX$173:$AX$540,"X"),0))),
    ISNA(INDEX(ROW($AX$567:$AX$1168)-ROW(AX567)+1,MATCH(TRUE,EXACT($AX$567:$AX$1168,"X"),0)))),"",
    INDEX(ROW($AX$173:$AX$1168),MATCH(TRUE,EXACT($AX$173:$AX$1168,"X"),0))),"-"))

    note: $W14 is the row that that formula resides in, W is column you want
    cursor to end up in.

    Quantity of Errors:
    =IF($G$7="x",SUMPRODUCT(--(LEFT($AX$173:$AX$540,1)="x"))+SUMPRODUCT(--(LEFT($AX$567:$AX$1168,1)="x")),
    SUMPRODUCT(--(LEFT($AX$173:$AX$540,1)="x"),--($G$173:$G$540<>"x")))+SUMPRODUCT(--(LEFT($AX$567:$AX$1168,1)="x"),--($G$567:$G$1168<>"x"))

    SAMPLE: Test for errors:
    =IF(AND(S9<>".",OR($G$7="x",G9<>"x"),OR(CP9={"",0})),"X",

    OTHER:
    =HYPERLINK("#"&CELL("address",OFFSET($A$69,-1,1)),"top")

    IN-DOCUMENT Hyperlink: GETS YOU BACK TO EXACT SAME SPOT, EVERYTIME.
    =HYPERLINK(IF(ROW($A$607)<(ROW($A50)-($W$1/2)),"#"&CELL("address",OFFSET($A$607,-1,1)),"#"&CELL("address",OFFSET($A$607,$W$1,1))),"A")

    ($A50 is row formula resides in)
    Utility: $W$1 method for screen size / from freezepane (any # minus size
    of freezepane = rows showing)
    =48-CELL("row",$A$17)



    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

    "Biff" wrote:

    > Just change the range to start at AE6:
    >
    > For the RELATIVE row location:
    >
    > =INDEX(ROW(AE6:AE1299)-ROW(AE6)+1),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))
    >
    > For the ACTUAL row location:
    >
    > =INDEX(ROW(AE6:AE1299),MATCH(TRUE,EXACT(AE6:AE1299,"A"),0))
    >
    > Biff
    >
    > "nastech" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi, thanks.. sorry, many instances of each letter, so the 2nd example
    > > worked;
    > >
    > > but wondering if way to exclude top rows, in case have cells with same
    > > letter "A" as settings? trying to insert ROW>5...
    > >
    > > "Biff" wrote:
    > >
    > >> OK.....
    > >>
    > >> If there's only one instance of "A":
    > >>
    > >> =SUMPRODUCT(--(EXACT(AE1:AE1299,"A")),ROW(AE1:AE1299))
    > >>
    > >> If there might be more than one instance of "A":
    > >>
    > >> This will find the first instance:
    > >>
    > >> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
    > >>
    > >> =INDEX(ROW(AE1:AE1299),MATCH(TRUE,EXACT(AE1:AE1299,"A"),0))
    > >>
    > >> Biff
    > >>
    > >> "nastech" <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > hi, yes to all.. just the letters: A, a, B, b, C, c, D, d
    > >> > just relative position: yes if getting the row number, is what that
    > >> > means.
    > >> >
    > >> > problems that arise, I believe are from actually doing work but some
    > >> > functions only do half the job. e.g.: would think programming would
    > >> > have
    > >> > allowed easy exclusion of top rows where having absolute cell
    > >> > references
    > >> > that
    > >> > control that column have:
    > >> > A, a, B.. as part of the settings; but then, the formula will not
    > >> > work.
    > >> > I would have allowed programming to use: EXACT, ROW<(), then ""
    > >> > nothing...
    > >> >
    > >> > - either that or it's just some trick I haven't quite worked out.
    > >> > - or uniqueness of problems, haven't been thought of by others, ...
    > >> > thanks
    > >> >
    > >> >
    > >> > "Biff" wrote:
    > >> >
    > >> >> What kind of data do you have in your range?
    > >> >>
    > >> >> Do the cells just contain the single letter "A" or do they contain
    > >> >> strings
    > >> >> like "Address" or "adult" ?
    > >> >>
    > >> >> So, if you're using MATCH then you just want the relative position of
    > >> >> "A"
    > >> >> within the array AE1:AE1299 ?
    > >> >>
    > >> >> Your posts always confuse me!
    > >> >>
    > >> >> Biff
    > >> >>
    > >> >> "nastech" <[email protected]> wrote in message
    > >> >> news:[email protected]...
    > >> >> > 060724 Match, Exact? NA error
    > >> >> > hi, trying to make an "exact" match for the case, of a character,
    > >> >> > e.g.:
    > >> >> > "A"
    > >> >> >
    > >> >> > where the following works, am trying to isolate capital "A"'s.
    > >> >> > =MATCH("A",$AE$1:$AE$1299,0)
    > >> >> >
    > >> >> > the following receives a NA error. Is there a correct way to
    > >> >> > represent?
    > >> >> > am open to inserting IF statements, EXACT, or ROW<=().. etc.
    > >> >> > =MATCH(EXACT($AE$1:$AE$1299,"A"),$AE$1:$AE$1299,0)
    > >> >> >
    > >> >> >
    > >> >> > example of an EXACT, that was able to get to work in combo with
    > >> >> > other:
    > >> >> > =SUMPRODUCT(--(EXACT(LEFT($AE$112:$AE$1300,1),"A")))
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >


+ 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