How to sort only those rows which have no blank cell?

11
votes

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 :

  1. =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.

  2. =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.

2
sortingfiltergoogle-sheetsarray-filter
As i was desperate for the answer, i also posted this query on Google Docs forum. i hope it does not void any rule here. – viv227295

2 Answers

19
votes

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)

13
votes

For anyone looking this, the accepted answer works great if filtering out blank cells that are truly blank, but if the cells contain formulas that evaluate to blank (""), ISTEXT will evaluate to TRUE and blanks will not be filtered out. I modified the accepted answer slightly to work in my situation, in which I had cells containing formulas (that evaluated to "") that I wanted to filter out:

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