+ Reply to Thread
Results 1 to 2 of 2

How NOT to Sort Blanks that aren't Blanks

  1. #1
    Registered User
    Join Date
    Fort Worth, TX

    How NOT to Sort Blanks that aren't Blanks

    Occasionally I copy data in a column to another sheet that is prepared to rank the values with the simple RANK function. Because my data can vary, sometimes BLANK cells are copied over. To ensure formatting is preserved, I always paste as value in this copy-paste. For some stupid reason, excel always sorts the blank cells (typically the bottom of the column) towards the top. I've double and triple checked when this occurs that there is absolutely no data in the blank cells, not even a space. It is the equivalent of ="", and is most certainly not =" ". Even when I ensure that sorting blanks is nto included, or attempt to sort blanks to the bottom, it always managers to somehow stupidly sort blanks to the top. Is there a reason for this?

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    MS-Off Ver
    office 97 ,2007

    Re: How NOT to Sort Blanks that aren't Blanks

    yep its not blank even if you copy paste the result of say if(a1=1000,"y","") and a1<>1000
    the cell y0u paste into is no longer blank its got "" in it even if you cant see it
    try =isblank(a1) where one of your "" values has been pasted it will say false
    then select cell and hit delete it will say true
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


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