0

In Google Sheets I have a column inProduction that contains one of the following text values: TRUE, FALSE, Z N/A.

When I sort my range I expect the following order:

# Ascending   # Descending

inProduction  inProduction
    FALSE         Z N/A
    TRUE          TRUE
    Z N/A         FALSE

However, when I sort my data range using Data > Sort range > Advanced range sorting options, in descending order by inProduction with "Data has header row" selected, Z N/A is incorrectly sorted to the bottom instead of the top.

I've confirmed that the inProduction column is formatted as text.

How can I get the correct sort?

Link to example spreadsheet

Example Incorrect Result:

inProduction   velocity(%)
TRUE           0.027192
TRUE           0.026422
FALSE          0.001519
FALSE          0.000254
FALSE          0.001207
Z N/A          18.17028428
2

1 Answer 1

0

Chances are that the values TRUE and FALSE are not text but Boolean values. The sort order used by Data > Sort sheet and Data > Sort range puts Booleans after text strings, when when you sort descending, they go to top.

You can verify whether those values are text or Boolean with the =istext() and islogical() functions.

To make it work, first format the column as Format > Number > Plain text, then enter the values as true and false or TRUE and FALSE. Alternatively, use formulas like ="true".

4
  • HI @doubleunary, you can enter the examples spreadsheet above. The TRUE FALSE cells are all set to text
    – WurmD
    Oct 4, 2022 at 15:28
  • There are no TRUE or FALSE values in the sample spreadsheet, and it is view-only. Choose View > Freeze > 1 row to make it sort correctly. Oct 4, 2022 at 15:34
  • @doubleunary I can't reproduce the original issue. Sorting the data without freezing any rows works as expected. Desc: Z first. Asc: Z last. Did you confirm OP's issue in 2022? Do you think there was a bug or something changed?
    – Blindspots
    Feb 28 at 15:59
  • Freezing rows makes a difference when using Data > Sort sheet, which is almost always preferable over Data > Sort range, including this OP's setup. I don't think there was an issue with either of those commands. Feb 28 at 16:40

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.