How to sort only those rows that do not have an empty cell?

Question

I have a Google Spreadsheet with two separate sheets. The first is just a large list of names and data, and the second is a sorted list of all the data on the first sheet (sorted, say, last name). Here is the current way that I define the second sheet:

= sort (sheet1! A2: L100, sheet1! D2: D100, TRUE)

Which works fine for the most part, except for one problem: in sheet 1, some cells in column 4 (column D) are empty. How to change the formula so that the sorting ignores rows that have an empty cell in column D?

Formulas I tried but got unwanted results:

• = arrayformula (if (istext (sheet1! D2: D100), sort (sheet1! A2: L100, sheet1! D2: D100, true), ""))

  It is sorted at will, but with one problem - empty cells were not pressed at the end, but scattered between rows.

• = arrayformula (sort (filter1) A2: L100, istext (sheet1! D2: D100)), sheet1! D2: D100, true))

  Despite the fact that part of the filter does its job perfectly, but in combination with sorting, it gives an error: inappropriate range lengths.

Answer 1

To filter rows with empty cells in column D, you could do something like # 2, but as the error message suggested, you also need to filter out the second argument to ensure that the ranges are the same length. Fortunately, there is an easier way, namely using column indices rather than ranges:

=SORT(FILTER(sheet1!A2:L100;ISTEXT(sheet1!D2:D100));4;TRUE)

Alternatively, you can use the QUERY function for these kinds of things:

=QUERY(sheet1!A2:L100;"select * where D != '' order by D";0)

Answer 2

For those who are looking at this, the accepted answer works fine if you filter out empty cells that are really empty, but if the cells contain formulas that evaluate to empty ("), ISTEXT will evaluate to TRUE, and spaces will not be filtered out, I’m a little modified the accepted answer to work in my situation in which I had cells containing formulas (which were rated as "") that I wanted to filter out:

 =SORT(FILTER(sheet1!A2:L100,sheet1!D2:D100 <> ""),4,TRUE)