When I sort a column in Google Spreadsheets, the blanks always seem to sink to the bottom. Can I have rows with blank cels be organized first / highest?
Alternatively, is there a function wherein I can specify a sort order based on an array of options, like
[
[1,''],
[2,'i'],
[3,'x']
]
where 1,2,3 is the sort order DESC. the string is the char to match and associate with that sort order.
I do not think you have any way to control the sort order directly, other than ascending or descending, so suggest a lookup table and a helper column (to determine the sort order) with a formula in that such as:
=if(B1="",1,vlookup(left(B1,1),E:F,2,0))
where ColumnB is the one to be sorted, ColumnE the sort order and ColumnF a number starting at 1 and incrementing by one for each row.
One easy way to make the blank cells appear at the top of the sheet when sorted is to enter an apostrophe ' in the empty cells.
The apostrophe ' will not be displayed in the cell, so it will continue to appear to be a blank cell however when you sort in ascending or descending order, it will put the pseudo-blank cell either at the top or at the bottom of the sheet.
An alternative regarding blanks sinking to the bottom is to use QUERY, where they float to the top. So starting with:
this formula in D1:
=query(A1:A3,"Select * order by A")
will return a blank in D1, i in D2 and x in D3.