sorting: cleared/null v/s ""

C

cate

When you sort in Ex2010, a cell with a value of "cleared/null" (set so
via mouse menu "Clear Contents" or vba range.ClearContents.) gets you
a result with nulls last in both sorts - ascending and descending.

However, if you set the cell's "empty value" as an empty string, = "",
they are considered "values" and sort. I see posts that claim "" ==
null. It doesn't seem so.

I have a formula that, based on existence of a date, sets a value in a
cell. I want either a date or CLEAR/NULL but can't find something
that actually clears or nulls the value. I'm guessing I'll have to
create a UDF (if I can actually set a null with a return "value"). Is
that correct?

Have

=IF(
AND(
NOT(ISNA(VLOOKUP(D10,nt_po_number,2,TRUE)))
),
VLOOKUP(D10,nt_po_number,2,TRUE),
""
)

Want

=IF(
AND(
NOT(ISNA(VLOOKUP(D10,nt_po_number,2,TRUE)))
),
VLOOKUP(D10,nt_po_number,2,TRUE),
NULL
)
 
C

cate

It looks like I'm dead - no go. The new question is then: is there a
sort configuration that say empties last? (nulls last)
Thank you for any suggestions.
 
K

KenCowen

Cate
You could possibly add an additional column, with an IF formula that equalsthe intended sort column, unless the sort column is blank or null or otherwise problematic, in which case the formula returns ZZZ or something that will sort as you wish; then sort by the new column.
Ken
 
C

cate

Cate
You could possibly add an additional column, with an IF formula that equals the intended sort column, unless the sort column is blank or null or otherwise problematic, in which case the formula returns ZZZ or something thatwill sort as you wish; then sort by the new column.
Ken

Good idea. Thank you Ken.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top