LibreOffice calc ( not sorting correctly

I have a spreadsheet that I think had been sorting correctly until I added two new rows to the worksheet today. They sort at the end whatever I do. Both are text columns (confirmed), my sort is simple, just on the two columns. There are only 11 columns used and now 287 rows including the header row (specified in the sort). I have tried resetting my user profile (and it definitely reset) and the issue is there even after exporting the worksheet to csv and sorting that, or the ods file saved from the csv. (csv here and ods here) Opening the csv in a text editor (emacs) I see nothing wrong and importing the csv into R it seems fine and in R it sorts fine. I think this is a bug! LO version is Ubuntu package version 1: Hm, I can confirm that the same seems to be true with version running in Windows 10 running in a VirtualBox VM on the same Ubuntu machine.

sounds like they are just not part of the selection for sorting :thinking:

I accept that I may well be doing something stupid but honestly it’s not that. I get the same failure to sort if I select the entire worksheet or just the rows with data in them. Do pull the ods or csv file down and see what you get. That may narrow things down (or show some other idiot thing I am missing).

An illusion that often occurs. :slightly_smiling_face:
You sort first by column D. Please look at the last two non-blank cells of this column - there is no "Y" there.

I am baffled and perhaps I am having a complete brain failure. What is the illusion I am seeing? I am sorting first by column D, “Done” and then by column A, “Term”. This is what I see there: in rows 286 and 287 D286 and D287 appear to contain “Y” and yet sorting by D and then A puts those last two rows, in which values of Term are “Average absolute deviation” and “Dataset and database” after row 285 where A285 is “z-score/transform”. As I say, sorry if I am missing the blindingly obvious.

Select D286 and press F2. How many characters are in a cell? My answer: 2.
So "Y " (letter Y, then space).

Brilliant. I should have known it couldn’t be such a simple bug. OK. I should have known that spaces are spaces are spaces! What I should perhaps have also known is that the default in read_csv() in R is to trim leading and trailing whitespace so trying to be thorough thoroughly misled me(!) Ouch. Got it and will certainly remember this one!! Thanks sokol92.

1 Like

Seems there is an option to trim spaces in the import window for csv files.