2

In my sheet, there are 5 columns of data. Some rows are fully filled and some rows have some empty cells. Now I want to sort my sheet from most-filled row to least-filled row. How can I do it?

  • can you share a copy of your sheet? – user0 Sep 1 '18 at 17:29
  • did I? was my solution somewhat not working for you? – user0 Sep 2 '18 at 18:44
2

Simply use =COUNTA(range) in a new column, and then use that column to sort.

  X      X      X      X      X      =COUNTA(A1:E1)
  X      X             X      X      =COUNTA(A2:E2)
  X                                  =COUNTA(A3:E3)
  X             X             X      =COUNTA(A4:E4)
  X             X                    =COUNTA(A5:E5)
  X                           X      =COUNTA(A6:E6)

Results in

  X      X      X      X      X      5
  X      X             X      X      4
  X                                  1
  X             X             X      3
  X             X                    2
  X                           X      2

Then use column F to sort your data.

0

you will need +1 column to generate sorting sequence used in =QUERY which will return your data

assuming your dataset somehow looks like:

paste this formula in cell F1:

=ARRAYFORMULA(IF(LEN(A:A&B:B&C:C&D:D&E:E);
              MMULT(--(LEN(A:E)>0);
              TRANSPOSE(SIGN(COLUMN(A1:E1))));
              IFERROR(1/0)))

and then paste this formula wherever you need to:

=QUERY(A1:F; "select A, B, C, D, E where F is not null order by F desc"; 0)



if overall visual requires so, you can easily hide the whole F column:

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.