Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Yet again..sorting problem (2000)

    hello again guys ...

    the problem is i try to sort a range of data in my wb..and it seems to be not what i want to do

    the problem occured when i try to sort it in column ad4..

    can you guys please help me..thanks

    please refer to my attachment

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Yet again..sorting problem (2000)

    How do you want it to look and sort, it sorts as expected. The resolution should be similar scheme to what was proposed in <post#=370494>post 370494</post#>

    If you sort ascending, the the zero first (which you have chosen to not display, so it looks "blank", but is still a zero), It increases in the numbers from 0.97 to 4.85, it then places all the null strings ("") [again these "look "blank" but have a zero-length string in them] . If you sort descending they sort the opposite way (null strings, 4.85-0.97,0).

    You could keep the "0" [From "item 1"] with the other "blanks", by changing the formula to in AD4 to:
    =IF(ISERROR(G4+L4+Q4+V4+AA4),0,G4+L4+Q4+V4+AA4)
    and copying it down the column.

    You could sort on col AD primarily (desc) and then by B (desc) to group all the "non-zeroes" together. Then define the range based on column B and only sort this range (see the post above for the example code to do this).

    If you want to separate a zero from a "blank" keep the formula as it is. You then would need to sort asc to put the "blanks" at the bottom, define a new range based on Col B, then sort as desired.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Kuala Lumpur , MALAYSIA
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Yet again..sorting problem (2000)

    wowwwwwwwwwwwwww..

    you are awesome steve..thanks a zillion..yet again your save me..

    why i didnt think about the correct formula ..the one that you provide:

    =IF(ISERROR(G4+L4+Q4+V4+AA4),0,G4+L4+Q4+V4+AA4)

    instead of doing that i use.. this formula :

    =IF(ISERROR(G4+L4+Q4+V4+AA4),"0",G4+L4+Q4+V4+AA4)

    my problem solved..god bless you steve..cheers

Posting Permissions

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