Re: [libreoffice-users] Calc: Autofilter sorting fails to expand selection when a column has empty cells at the bottom

At 19:42 05/11/2017 -0700, Jim Gallagher wrote:
> 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


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted