How to sort only those rows which have no blank cell?
I have a Google Spreadsheet with two separate sheets. The first one is just a big list of names and data, and the second is supposed to be a sorted listing of all the data on the first sheet (sorted by, say, last name). Here is the current way I am defining the second sheet:
=sort(sheet1!A2:L100, sheet1!D2:D100, TRUE)
Which works fine for the most part, except for one issue: in sheet1, some of the cells in 4th column (column D) are blank. How can I change the formula so that the sorting ignores such rows which has a blank cell in column D?
The formulas i tried but got undesirable results :
=arrayformula(if(istext(sheet1!D2:D100), sort(sheet1!A2:L100, sheet1!D2:D100, true), ""))
It sorted as desired but with one issue - blank cells were not pushed at the end but scattered in between the rows.
=arrayformula(sort(filter(sheet1!A2:L100, istext(sheet1!D2:D100)),sheet1!D2:D100, true))
Though the filter part does its job perfectly but when coupled with sort, it is giving an error : Mismatched range lengths.
Answer
To filter out the rows with blank cells in column D, you could do something like #2, but as the error message suggested, the second argument would need to be filtered as well to ensure the ranges are the same length. Fortunately there is an easier way, and that is to use column indices rather than ranges:
=SORT(FILTER(sheet1!A2:L100;ISTEXT(sheet1!D2:D100));4;TRUE)
Alternatively you can use the QUERY function for this sort of thing:
=QUERY(sheet1!A2:L100;"select * where D != '' order by D";0)