return "nothing"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

themike - 30 Sep 2005 16:22 GMT

Hi.

I'm comparing two columns (A and B) of stock ticker symbols (text
strings). I want to create an if statement in Column C where the
"value if false" is NOTHING. Currently, the statement (in C1) looks
like this:

=if(B1=A1,B1,"")

This formula has been copied from C1 to C100, so that some of the
return values are text values, and some are "". The formula works, but
when I sort Column C, ascending, the "" values precede the text string
values. How can I change my if statement so that a sort (ascending)
will place my first text string (i.e. ABC) in cell C1?

Thanks.
themike

Reply to this Message

Harlan Grove - 30 Sep 2005 19:00 GMT

themike wrote...

>I'm comparing two columns (A and B) of stock ticker symbols (text
>strings). I want to create an if statement in Column C where the
[quoted text clipped - 8 lines]
>values. How can I change my if statement so that a sort (ascending)
>will place my first text string (i.e. ABC) in cell C1?

There's only one practical approach to doing this: use another column
containing a calculated sort key like

=IF(X1="","zzzzzzzzzz",X1)

Excel uses a perverse collation sequence, so lower case z's are the
last character in ascending sort order.

Reply to this Message

Cutter - 30 Sep 2005 21:50 GMT

OR

replace the "" in your original formula with the "zzzzzzzzz" as Harlan
suggested.
Then apply a conditional format (to change font colour to match cell's
background colour) on all the cells containing the formula so that when
the result of the formula is "zzzzzzzzz" it won't show.
Now when you do your sort all the cells containing "zzzzzzzzz" will be
last and they'll look like they're empty.

Signature

Cutter

Reply to this Message