1

The following query works for me in google sheets:

=QUERY(A3:A,"SELECT A order by A desc")

Changing desc to asc however does not, instead returning #REF!:

=QUERY(A3:A,"SELECT A order by A asc")

The columns are filled with only integers.

Here is the error:

#REF! Result was not automatically expanded, please insert more rows (9).
0

3 Answers 3

1

To leave out rows where column A is blank, add a where clause, like this:

=query(A3:A, "where A is not null order by A asc", 0)

See query().

0
1

Solution

=SORT(A3:A)

Explanation: SORT Function

An approach using the SORT function would give you the results you want with a much shorter and simpler formula.

=SORT(range, sort_column, is_ascending,
     [sort_column2, is_ascending2, ...])

Ascending

The sort_column and is_ascending arguments defaults can be omitted or included as you choose.

=SORT(A3:A)       // defaults omitted

=SORT(A3:A,1,1)   // defaults included

Descending

The is_ascending argument is not the default therefore the sort_column argument must also be included.

=SORT(A3:A,1,0)
0

The issue was solved- as it turns out, the data range A3:A includes not only the filled cells but also all of the blank cells in the rest of A, to the end of the document.

The above query was run in a cell past row 3, so it was impossible to display the full result, because the empty cells were being sorted above the filled ones. Event if the document automatically expanded to fit the output, the expanded rows would now also need to be added, and so on to infinity.

Interestingly, the reason descending order worked was that empty cells are ignored when they are sorted at the end of a data table. Sheets does not care if it cannot fit them, because they are empty.

The real solution around this, if anyone needs to run the above query at a row lower than the data, is to reduce the input data to a finite range (e.g. A3:A99 instead of A3:A), or add code to eliminate unwanted empty cells.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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