Sorting not working properly and I'm under a deadline 😳

For some reason when I try and sort columns in either my sheet or a report based on the sheet, the sort does not work properly.

NOTE: there are no hierarchies in this sheet.

It seems as if the Sort is not respecting the orders of magnitude. So 36 is somehow senior to 344, for example.


At first I thought it was because the sheet had hypertexted numbers in the column I was trying to sort Descending order. But then I created a "sort #" sheet based on that column (as you can see above).

No matter what I try, I can't sort on the column I wish to.

There are no locked columns (there were, but I unlocked them and have saved several times since.

I created a test sheet and pasted the missorted #'s into it and those sort fine.

What would cause this sorting issue?

Anyone have any ideas?

Best Answer

  • MCorbin
    MCorbin Overachievers Alumni
    Answer ✓

    It's because it's looking at the data as text data (you can tell because it's left justified), and it's sorting data as a text, not a number. So as text, it would sort:

    3

    31

    32

    33

    331

    332

    and so on


    To convert it to numeric, you could update the formula in your Sort # column to say =VALUE(hyperlinked@row)

    (I didn't know the name of your original column, so naturally you would substitute that column in the Sort # formula)

Answers

  • MCorbin
    MCorbin Overachievers Alumni
    Answer ✓

    It's because it's looking at the data as text data (you can tell because it's left justified), and it's sorting data as a text, not a number. So as text, it would sort:

    3

    31

    32

    33

    331

    332

    and so on


    To convert it to numeric, you could update the formula in your Sort # column to say =VALUE(hyperlinked@row)

    (I didn't know the name of your original column, so naturally you would substitute that column in the Sort # formula)

  • @MCorbin That did it! Thank you so much. I knew it had to be something simple and I was looking right at it, but missed that nuance. Very much appreciate you taking the time to answer.

  • MCorbin
    MCorbin Overachievers Alumni

    You are welcome! The only reason I saw it so fast is that I've had the same issue before :-)