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

  • Thread starter Thread starter cate
  • Start date Start date
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
 
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
Click to expand...
 
Back
Top