sorting; blanks at the top; what is a "blank"

C

cate

I have multiple columns which are populated with function return
values. The cells are text format. If the function (vlookup) doesn't
find it's target, I enter a "blank" (two double quotes) - or I think I
do. But these blanks do not always sort last. If I copy and paste
into notepad I get a newline. Is there a function that returns
"blank" I can use to populate a cell?

I've also tried the general format.

This is a very simple sort: select X rows Data->Sort [Sort Col A,
Ascending, No header row]

Thank you
 
D

Dave Peterson

Those empty strings will sort to the top of the data (ascending order). But the
real empty cells (no formulas, no values) will sort to the bottom.

Maybe you could change the value returned to something that would put them at
the bottom of the sort:

=if(isna(vlookup(...)),"zzzz",vlookup(...)))

Then do what you have to do after the sort (delete those rows or adjust the
formula???).

Another option would be to add a helper column and fill it with a formula like:

=if(a2="","zzzzzzzz",a2)
And drag down.

Then sort your data by this column. Delete the column (or hide it) when you
don't need it.
I have multiple columns which are populated with function return
values. The cells are text format. If the function (vlookup) doesn't
find it's target, I enter a "blank" (two double quotes) - or I think I
do. But these blanks do not always sort last. If I copy and paste
into notepad I get a newline. Is there a function that returns
"blank" I can use to populate a cell?

I've also tried the general format.

This is a very simple sort: select X rows Data->Sort [Sort Col A,
Ascending, No header row]

Thank you
 

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