Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: i got this problem and would like to solve it, but i need yo

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i got the following list, is there any way that i can have the largest number at the top and all the #N/A at the bottom, i tried so many times and it still won't work.
    Please let me know if there is a way to overcome this problem:

    15
    117
    151
    152
    215
    237
    249
    266
    270
    279
    345
    361
    481
    573
    660
    880
    1510
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    Your initial sort put the #N/A at the bottom.

    Select the range of just numbers and sort
    descending.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    1. Sort the list in Ascending order
    2. Select the list and choose Edit | Go To... Special Constants Numbers (checked only)
    3. Sort the resultant selection in Descending order.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    the method works, but still not quite to solve my problem, my actual problem is this:

    A 1510
    B 660
    C 880
    D 249
    E 573
    F 481
    G 266
    h #N/A
    I 345
    j #N/A
    k 361
    l 270
    m 117
    n 279
    o 215
    p 151
    q 152
    r 237
    s 15
    t #N/A
    u #N/A
    v #N/A


    can u do a sort on this? the largest number at the top... pls help

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i did, i have tried it , but it doesn't work to my actual problem...

    A 1510
    B 660
    C 880
    D 249
    E 573
    F 481
    G 266
    h #N/A
    I 345
    j #N/A
    k 361
    l 270
    m 117

    I can't get it sort, because it only select the number, but i need the letter next to it to be selected as well, so that i can have it sort.

    the thing is i can't get it to do it, i need to save this as a macro, but excel can only read me selecting A2: B18 range, not the thing that i want...

    pls help

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Replace #N/A with blanks, sort descending, then re-enter #N/A in the blank cells.

    I trust this won't change the 'actual problem'!

    [ This Message was edited by: Mark W. on 2002-04-10 15:58 ]

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    how do u do it as a command, so that i can do it in my macro as well

    can any ppl pls help me please??



    [ This Message was edited by: LMF on 2002-04-10 16:01 ]

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,424
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    Ensure that you select all relevant columns (A and B?) and sort as mentioned above with primarly sort on Column B.

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-10 15:59, LMF wrote:
    how do u do it as a command, so that i can do it in my macro as well

    can any ppl pls help me please??

    [img]/board/images/smiles/icon_smile.gif[/img]

    [ This Message was edited by: LMF on 2002-04-10 16:01 ]
    Range("B1:B22").Select
    Selection.SpecialCells(xlCellTypeConstants, 16).Select
    Selection.ClearContents
    Range("B1").Select
    Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("B1:B22").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "#N/A"

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Posts
    73
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i did what he said...
    first select both columns A and B, sort Ascending to column B, then select constant as number only. it only select all the number on column B...

    now, since i need to do sort with conlumn A as well, i select column A, but the problem is, i need to set this as a macro... and the excel can only read me doing it as manually.

    means that it reads range("A2:B18"), which is not what i want in my macro. what happen if there are more #N/A, or less #N/A in the list???

    so i really would like some1 can show me how this can be done???

    pls let me know

Some videos you may like

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
  •