4

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.

3

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.

2

An alternative regarding blanks sinking to the bottom is to use QUERY, where they float to the top. So starting with:

WA67363 example

this formula in D1:

=query(A1:A3,"Select * order by A")  

will return a blank in D1, i in D2 and x in D3.

1

One easy way to make the blank cells appear at the top of the sheet when it is sorted is to enter a ' in the cell if it is currently supposed to have nothing in that cell. The ' will not show as text in the cell, therefore appearing to be a blank cell.

When you sort in ascending or descending order, it will put the blank cell either at the top or at the bottom of the sheet.

0
=sort(UNIQUE(B1:C42); 3; FALSE)

The 3 prioritizes blank cells, and FALSE makes the 'sort order' descending.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.