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

On 6 Nov 2017 at 4:24, Brian Barker wrote:

Date sent:              Mon, 06 Nov 2017 04:24:47 +0000
To:                     users@global.libreoffice.org
From:                   Brian Barker <b.m.barker@btinternet.com>
Subject:                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.

I checked it out, and noticed the same thing with the autofilter and with just 
the regular sort. Sorting on the column with the empty cell always puts the 
empty cell at the bottom. Doesn't matter if you short ascending or decending. 
The empty cell shows up at the bottom. If you put a 0 in the cell, an 
ascending sort puts it first, and a decending sort puts it last.  Couldn't see 
any option to have it sort treating empty cell as a blank or zero value?? 

Interestingly, the autofilter has a empty / not empty option.





> > 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


+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor (Retired)     
 mailto:mikes@guam.net                            
 mailto:msetzerii@gmail.com
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer 
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+

http://setiathome.berkeley.edu (Original)
Number of Seti Units Returned:  19,471
Processing time:  32 years, 290 days, 12 hours, 58 minutes
(Total Hours: 287,489)

BOINC@HOME CREDITS

ABC          16613838.513356 | EINSTEIN    140425825.499240
ROSETTA      64148516.145122 | SETI        109183341.383293


-- 
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