Autofilter & sorting anomaly

Discuss the spreadsheet application
Post Reply
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Autofilter & sorting anomaly

Post by Skompy »

I have added Autofilter to my horse racing s/s headers and while looking through some of the groupings of trainers, it seems that Autofilter also sorts my columns for each horse's finishing position, the number of horses that ran and their price.
But I have found 3 trainers results (so far) where this is not so. I attach 3 screen shots of what I have found.

I have checked all the cells shown as "1" for the 1st column (finishing position) in the screen shots and they are all formatted as numbers. The cells in the second column are also all numbers.
I also carried out the 2-step format for the 1st & 2nd columns before I had a look at the trainers with Autofilter.
That is, with the 1st column highlighted, formatting the cells as numbers (-1234) and then Text to Columns with the Tab option checked.

I have looked randomly at about 20 other trainers and have not found any other errors (so far).

Any idea why this might be happening?
Attachments
HRDB-Autofilter Sort error-3.jpg
HRDB-Autofilter Sort error-2.jpg
HRDB-Autofilter Sort error-2.jpg (28.12 KiB) Viewed 1686 times
HRDB-Autofilter Sort error-1.jpg
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Autofilter & sorting anomaly

Post by RusselB »

For the best chances of an accurate response, please attach a copy of the actual spreadsheet file, rather than a picture
While "a picture is worth a thousand words", in a case like this, the person speaking might as well be mute... ie: it's next to useless.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Autofilter & sorting anomaly

Post by Skompy »

File attached.
I have brought the data into a new s/s and noticed the middle column became automatically formatted with bold & underlining. Don't know why that happened.
Attachments
HRDB-Autofilter sort anomalies.ods
(15.8 KiB) Downloaded 98 times
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Autofilter & sorting anomaly

Post by RusselB »

Appreciate the file, however the auto-filter settings are missing and I can't implement them, since they require a header line, which is also missing.
It almost looks like you just made a copy of the screenshot and put it into a spreadsheet, rather than giving me the spreadsheet that the screenshot was made from.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Autofilter & sorting anomaly

Post by Skompy »

RusselB wrote:Appreciate the file, however the auto-filter settings are missing and I can't implement them, since they require a header line, which is also missing.
It almost looks like you just made a copy of the screenshot and put it into a spreadsheet, rather than giving me the spreadsheet that the screenshot was made from.
I copied the relevant cells to a new spreadsheet & uploaded that.
And the headers can easily be created from my original post.
I have added them and attached the updated file.
But it shouldn't matter, I think, for the cells that are not sorting correctly.
Attachments
HRDB-Autofilter sort anomalies.ods
(18.3 KiB) Downloaded 82 times
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Autofilter & sorting anomaly

Post by RusselB »

My apologies... I should've made it clear that, in order to try to determine the problem, a sample of data that does not show the problem can be very helpful.
The Trainer dropdown list only has one name in it.
The Auto-Filter (which I personally don't care for), restricts the display to those items that match all of the selected Auto-Filter settings.
I don't know if that helps you or not. As to sorting, when using Data -> Sort, everything sorts as expected.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Autofilter & sorting anomaly

Post by Skompy »

RusselB wrote:My apologies... I should've made it clear that, in order to try to determine the problem, a sample of data that does not show the problem can be very helpful.
The Trainer dropdown list only has one name in it.
The Auto-Filter (which I personally don't care for), restricts the display to those items that match all of the selected Auto-Filter settings.
I don't know if that helps you or not. As to sorting, when using Data -> Sort, everything sorts as expected.
I have added a new trainer to the s/s whose horse's results sort correctly.
The 3 groups of data I had previously included are what results I get when I use autofilter and then select those 3 trainers, one by one, from the autofilter drop down name list.
Yes, doing a sort from what I provided you does sort correctly. The problem is that it is not doing so on my real s/s.
I have since found more trainers with sorting errors.

In the latest update to the sample s/s, I have removed the empty rows between the trainers and then selected the trainers one by one to see what results I get.
The anomaly now shows up on two trainers, which you will see too (Smart & Hills).
Attachments
HRDB-Autofilter sort anomalies.ods
(19.54 KiB) Downloaded 101 times
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Autofilter & sorting anomaly

Post by RusselB »

If what you are providing me is different in layout from your actual spreadsheet, then I'm going to be unable to help you.
The data isn't of particular importance, but the structure of your spreadsheet is.
Again, based on the information in the latest spreadsheet you have provided, I can find nothing wrong.
Picking a trainer shows the entries for that trainer.
Applying other auto-filters reduces the number of displayed entries based on the combined results of the auto-filters.
Applying Data -> Sort to the information provided, sorts as expected. Lower numbers (1, 2, 3, etc. being lower than 7, 8, 9) and text/letters coming at the bottom.

Looking at the two names you specified, I can not see the anomaly you speak of.

If you can recreate the anomaly using the same spreadsheet that you have provided, please explain which auto-filter settings you are using and/or how the sorting is being done.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Autofilter & sorting anomaly

Post by keme »

I can confirm the report from RusselB.
I see that in the file you provided, the list for Smart and Hills have the "result 1" in the wrong spot, but when we apply sorting it goes where it should go.

There may be a misconception:
[...] it seems that Autofilter also sorts my columns for each horse's finishing position, the number of horses that ran and their price. [...]
Autofilter will sort the filtering options in the filter selection dropdown. It will not sort the data rows.
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Autofilter & sorting anomaly

Post by Skompy »

keme wrote:I can confirm the report from RusselB.
I see that in the file you provided, the list for Smart and Hills have the "result 1" in the wrong spot, but when we apply sorting it goes where it should go.
But why is the sorting of the 3 columns I am referring to sorted correctly some times and incorrectly some times?
keme wrote:IThere may be a misconception:
[...] it seems that Autofilter also sorts my columns for each horse's finishing position, the number of horses that ran and their price. [...]
Autofilter will sort the filtering options in the filter selection dropdown. It will not sort the data rows.
I don't understand what you mean by "data rows". All rows are data. All columns are data.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Autofilter & sorting anomaly

Post by Skompy »

RusselB wrote:If what you are providing me is different in layout from your actual spreadsheet, then I'm going to be unable to help you.
The data isn't of particular importance, but the structure of your spreadsheet is.
The structure of the 3 columns I have provided is as per my s/s, with one text column missing.
For the problem in question, it should not matter that what I have given you is a small segment of my s/s, since the other columns are not involved in the problem.

RusselB wrote:Looking at the two names you specified, I can not see the anomaly you speak of.
RusselB wrote: If you can recreate the anomaly using the same spreadsheet that you have provided, please explain which auto-filter settings you are using and/or how the sorting is being done.
Well, I have recreated the anomaly and you can too. If you select each of the two trainers I mentioned, you will see one row for each trainer which is not in the right position (both of them are at/near the bottom but they should be with the other "1"s).
My question is why does this incorrect sorting happen only sometimes?

I don't know why the other anomaly I found is not showing in this sample s/s.
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Autofilter & sorting anomaly

Post by keme »

Skompy wrote:[...]
I don't understand what you mean by "data rows". All rows are data. All columns are data.
I mentioned "Data rows" as opposed to the dropdown list you get from the heading. Of course, the entries in that dropdown list are also "data" as such. However, they are not "first order data" but extracted, using the cell grid
The autofilter will sort the filtering options you get when you click the heading, so it is easy to find the one you want.
Autofilter will not sort data in the cell grid. You need to explicitly request sorting, using Data - Sort, as previously indicated.
Skompy
Posts: 110
Joined: Tue Feb 18, 2014 10:35 am

Re: Autofilter & sorting anomaly

Post by Skompy »

Skompy wrote:[...]
I don't understand what you mean by "data rows". All rows are data. All columns are data.
keme wrote: I mentioned "Data rows" as opposed to the dropdown list you get from the heading. Of course, the entries in that dropdown list are also "data" as such. However, they are not "first order data" but extracted, using the cell grid
I am no clearer with your explanation.
keme wrote:Autofilter will not sort data in the cell grid. You need to explicitly request sorting, using Data - Sort, as previously indicated.
OK but my question, again, is why is this happening intermittently and not all the time?
And also, why is there only one row for each trainer that this is anomaly is occurring with? The other 1s are sorted correctly for each trainer I have seen & provided (and other trainers subsequently).
Apache OpenOffice 4.1.6 AOO416m1(Build:9790) - Rev. 1844436
Mac OS X 10.13.6 (High Sierra) 32GB RAM

Saying "Never say never" says it.
Post Reply