return "nothing"

[themike]

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

 

[Harlan Grove]

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
"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?

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.

 

[Cutter]

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.