Autofilter & sorting anomaly
Autofilter & sorting anomaly
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?
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?
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.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Autofilter & sorting anomaly
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.
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.
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.
Re: Autofilter & sorting anomaly
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.
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.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Autofilter & sorting anomaly
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.
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.
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.
Re: Autofilter & sorting anomaly
I copied the relevant cells to a new spreadsheet & uploaded that.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.
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.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Autofilter & sorting anomaly
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.
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.
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.
Re: Autofilter & sorting anomaly
I have added a new trainer to the s/s whose horse's results sort correctly.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.
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.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Autofilter & sorting anomaly
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.
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.
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.
Re: Autofilter & sorting anomaly
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:
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:
Autofilter will sort the filtering options in the filter selection dropdown. It will not sort the data rows.[...] it seems that Autofilter also sorts my columns for each horse's finishing position, the number of horses that ran and their price. [...]
Re: Autofilter & sorting anomaly
But why is the sorting of the 3 columns I am referring to sorted correctly some times and incorrectly some times?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.
I don't understand what you mean by "data rows". All rows are data. All columns are data.keme wrote:IThere may be a misconception:Autofilter will sort the filtering options in the filter selection dropdown. It will not sort the data rows.[...] it seems that Autofilter also sorts my columns for each horse's finishing position, the number of horses that ran and their price. [...]
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.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Autofilter & sorting anomaly
The structure of the 3 columns I have provided is as per my s/s, with one text column missing.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.
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.
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).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.
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.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.
Re: Autofilter & sorting anomaly
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 gridSkompy wrote:[...]
I don't understand what you mean by "data rows". All rows are data. All columns are data.
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.
Re: Autofilter & sorting anomaly
Skompy wrote:[...]
I don't understand what you mean by "data rows". All rows are data. All columns are data.
I am no clearer with your explanation.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
OK but my question, again, is why is this happening intermittently and not all the time?keme wrote:Autofilter will not sort data in the cell grid. You need to explicitly request sorting, using Data - Sort, as previously indicated.
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.
Mac OS X 10.13.6 (High Sierra) 32GB RAM
Saying "Never say never" says it.