Calc: Autofilter sorting fails to expand selection when a column has empty cells at the bottom

LibreOffice 5.4.2.2

Create a new spreadsheet. Three columns of data.

Column A: col1, a, b, c, d
Column B: col2, 1, 2, 3 <--- leave cell B5 empty
Column C: col3, z, y, x, w

Select the 15 cells, select Data -> Autofilter. We have a 3 x 4 table with
headings.

Sort column A descending. Data in all 3 columns x 4 rows moves, keeping the
cells together in rows. Correct result.

Sort column C descending. Data in all 3 columns x 4 rows moves, keeping the
cells together in rows. Correct result.

Sort column B descending. Data in only 3 columns x 3 rows moves. Cells A5
and C5 are not picked up in the sort. Incorrect result.

This was certainly unexpected, and caused much frustration. Fortunately, my
data was pretty simple and I was able to sort it out quickly once I
discovered how my rows were getting messed up. This could really put the
hurt on someone with a lot of data in a table.

LibreOffice 5.4.2.2

Create a new spreadsheet. Three columns of data.

Column A: col1, a, b, c, d
Column B: col2, 1, 2, 3 <--- leave cell B5 empty
Column C: col3, z, y, x, w

Select the 15 cells, select Data -> Autofilter. We have a 3 x 4 table with headings.

I don't see the significance of this process to your enquiry: I think the results are the same without this.

Sort column A descending. Data in all 3 columns x 4 rows moves, keeping the cells together in rows. Correct result.

So surely the selection *must be* being expanded correctly (contrary to your claim in the Subject header)?

Sort column C descending. Data in all 3 columns x 4 rows moves, keeping the cells together in rows. Correct result.

Sort column B descending. Data in only 3 columns x 3 rows moves. Cells A5 and C5 are not picked up in the sort.

You can see the selection expansion before you commit the sort. Surely it is similarly being done correctly?

Since B5 is empty, where are you expecting it to appear in the new sorted (numerical) list? If it were treated as zero (which it isn't), you would expect it to stay where it was, after your lowest number (1), wouldn't you? In fact, empty cells appear to be sorted after any occupied cells, no matter whether the sort is ascending or descending.

Incorrect result.

Not so. The empty cell, B5, stays where it is - at the end of the data. So the values in A5 and C5 - being controlled by the movement of values in column B in the sorting process, stay where they are, too. It is not that they are "not picked up in the sort", but rather that their appropriate place after the sort is exactly where they are.

The confusing thing here is perhaps that the empty cell moves in your first two sort processes. But that is because there *are* values in A5 and C5 to require that.

This was certainly unexpected, and caused much frustration. Fortunately, my data was pretty simple and I was able to sort it out quickly once I discovered how my rows were getting messed up. This could really put the hurt on someone with a lot of data in a table.

I'm unsure what you were expecting or need here. Exactly where in a sort on a column with empty cells do you expect them to appear? Surely the only options are either after everything else (as happens) or before? If it were before, a sort on an entire column (as we know you can do) would banish all significant data to rows starting back from row 1,048,576 !

I trust this helps.

Brian Barker

Date sent: Mon, 06 Nov 2017 04:24:47 +0000

I expect it to work like a table in Excel.

I believe others would expect the same as well. It is not logical that
sorting the middle column sorts across less rows than on either side.

I expect it to work like a table in Excel.

And how is that, please? Exactly where does Excel place empty cells in a sort of numerical values? Empty, 1, 2, 3? 1, 2, 3, empty? 1, 2, empty, 3? We need to know! If Excel doesn't put all empty cells after any cells with values, that would mean - as I mentioned - that you cannot sort a column by clicking the column header, so that the entire column is selected. If empty cells were seen as zero values, they might come before some data, in which case that data would be moved a million-plus rows down! I can't believe any spreadsheet program would behave that way.

I believe others would expect the same as well.

We cannot know whether that is the case until we know precisely what you expect.

It is not logical that sorting the middle column sorts across less rows than on either side.

It is indeed not logical - and it doesn't happen! You have missed the point. In your example, B5 was empty. Contrary to what you believe, row 5 *is* included in your third sort. The final sorted position for the empty value happens to be in the same place: B5. So the values in A5 and C5 retain their places to match. It just happens that row 5 stays - correctly - in the same place after the sort.

It may help you to understand this if you experiment with a column which has a blank cell or cells somewhere other than in the last row of the table. Instead of 1, 2, 3, empty, try 1, 2, empty, 4. See what happens? The values in the row with the empty cell are indeed moved.

To solve your problem, you need first to establish exactly what you want to happen to an empty cell in such a sort. (You haven't admitted this.) Then you may have to insert a suitable value in the otherwise empty cell to arrange what you need.

Brian Barker