Help on is it poss to pick the (>) letter from a row ???
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Help on is it poss to pick the (>) letter from a row ???

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Scotland
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Help
    I am attempting without success to set up a sheet that will pick out the highest letter from a row or array similar to the example below

    A B C D E Answer = E
    A B D Answer = D
    A B D F G H Answer = H
    I would appreciate any help on this problem
    Robert
    Scotland

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,872
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-11 22:58, rmtaylor wrote:
    Help
    I am attempting without success to set up a sheet that will pick out the highest letter from a row or array similar to the example below

    A B C D E Answer = E
    A B D Answer = D
    A B D F G H Answer = H
    I would appreciate any help on this problem
    Robert
    Scotland
    Array-enter:

    =CHAR(MAX(CODE(A2:E2)))

    where each cell in A2:E2 houses a single letter.

    In order to array-enter a formula, hit control+shift+enter at the same time, not just enter.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Scotland
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I attempted this by adding the formula to a sheet as a test as read no sucess???

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Scotland
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-11 22:58, rmtaylor wrote:
    Help
    I am attempting without success to set up a sheet that will pick out the highest letter from a row similar to the example below

    A B C D E Answer = E
    A B D Answer = D
    A B D F G H Answer = H
    I would appreciate any help on this problem
    Robert
    Scotland
    I attempted this by adding the formula to a sheet as a test as read no sucess???



    [ This Message was edited by: rmtaylor on 2002-04-12 00:12 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,872
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-12 00:11, rmtaylor wrote:
    On 2002-04-11 22:58, rmtaylor wrote:
    Help
    I am attempting without success to set up a sheet that will pick out the highest letter from a row similar to the example below

    A B C D E Answer = E
    A B D Answer = D
    A B D F G H Answer = H
    I would appreciate any help on this problem
    Robert
    Scotland
    I attempted this by adding the formula to a sheet as a test as read no sucess???
    [ This Message was edited by: rmtaylor on 2002-04-12 00:12 ]
    What did you get? An error, #VALUE! maybe?

    Aladin

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Posts
    2,314
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, this is a long way around it...

    If your text is in column 'A' and has 5 letters then in columns B,C,D,E & F enter;
    =MID($B10,1,1)
    =MID($B10,2,1)
    =MID($B10,3,1)
    =MID($B10,4,1)
    =MID($B10,5,1)

    Then in column G enter;
    =IF(B1<>"",CODE(B1),1)
    and fill right to column 'K'

    then in column (L)enter;
    =MAX(G1:K1)

    Finally in column 'M' enter;
    =CHAR(L1)
    this will give you the highest letter...


  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-12 02:22, jimboy wrote:
    OK, this is a long way around it...

    If your text is in column 'A' and has 5 letters then in columns B,C,D,E & F enter;
    =MID($B10,1,1)
    =MID($B10,2,1)
    =MID($B10,3,1)
    =MID($B10,4,1)
    =MID($B10,5,1)

    Then in column G enter;
    =IF(B1<>"",CODE(B1),1)
    and fill right to column 'K'

    then in column (L)enter;
    =MAX(G1:K1)

    Finally in column 'M' enter;
    =CHAR(L1)
    this will give you the highest letter...

    =CHAR(MAX(CODE(MID(A2,{1,2,3,4,5},1))))
    will do the same job,
    BUT I think the problem lies in the fact that if there is only 4 characters in the cell (or over 4 columns, as in Aladins answer), it will return #VALUE!.

    I'm trying to figure a way round this, hang fire.
    "Have a good time......all the time"
    Ian Mac

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,872
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-12 02:54, Ian Mac wrote:
    On 2002-04-12 02:22, jimboy wrote:
    OK, this is a long way around it...

    If your text is in column 'A' and has 5 letters then in columns B,C,D,E & F enter;
    =MID($B10,1,1)
    =MID($B10,2,1)
    =MID($B10,3,1)
    =MID($B10,4,1)
    =MID($B10,5,1)

    Then in column G enter;
    =IF(B1<>"",CODE(B1),1)
    and fill right to column 'K'

    then in column (L)enter;
    =MAX(G1:K1)

    Finally in column 'M' enter;
    =CHAR(L1)
    this will give you the highest letter...

    =CHAR(MAX(CODE(MID(A2,{1,2,3,4,5},1))))
    will do the same job,
    BUT I think the problem lies in the fact that if there is only 4 characters in the cell (or over 4 columns, as in Aladins answer), it will return #VALUE!.

    I'm trying to figure a way round this, hang fire.
    Ian

    I assumed a single letter per cell in my original reply and I said so. If it is just a string in a single cell and you want the "letter" with highest ASCII value, the following array-formula will do:

    =CHAR(MAX(CODE(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1))))

    Aladin

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-12 03:06, Aladin Akyurek wrote:
    On 2002-04-12 02:54, Ian Mac wrote:
    On 2002-04-12 02:22, jimboy wrote:
    OK, this is a long way around it...

    If your text is in column 'A' and has 5 letters then in columns B,C,D,E & F enter;
    =MID($B10,1,1)
    =MID($B10,2,1)
    =MID($B10,3,1)
    =MID($B10,4,1)
    =MID($B10,5,1)

    Then in column G enter;
    =IF(B1<>"",CODE(B1),1)
    and fill right to column 'K'

    then in column (L)enter;
    =MAX(G1:K1)

    Finally in column 'M' enter;
    =CHAR(L1)
    this will give you the highest letter...

    =CHAR(MAX(CODE(MID(A2,{1,2,3,4,5},1))))
    will do the same job,
    BUT I think the problem lies in the fact that if there is only 4 characters in the cell (or over 4 columns, as in Aladins answer), it will return #VALUE!.

    I'm trying to figure a way round this, hang fire.
    Ian

    I assumed a single letter per cell in my original reply and I said so. If it is just a string in a single cell and you want the "letter" with highest ASCII value, the following array-formula will do:

    =CHAR(MAX(CODE(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1))))

    Aladin
    I also was considering them in single cell, I must admit I was playing with both idea and was getting close, alas! you've done it again.
    What about the other 1 though (single cells!)

    I've been trying to use INDIRECT() in the CODE() part:

    =CHAR(MAX(CODE(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1))&":"&ADDRESS(ROW(A1),COLUMN()-1)))))

    is where I'm at. unfortunatly it doesn't work and I can't figure out why because if I use

    =CHAR(MAX(CODE(INDIRECT(G2&":"&G3))))

    where G2 houses

    =ADDRESS(ROW(A1),COLUMN(A1))

    and G3

    =ADDRESS(ROW(A1),COLUMN()-1)

    which I thought would be the same thing.

    Any Ideas, is the INDIRECT having problems with the Array Formula entry?
    "Have a good time......all the time"
    Ian Mac

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com