How to order rows into groups and filter them
How to order rows into groups and filter them
I've attached a horse racing data sheet as an example.
Everything from columns B to K is data that I paste into the sheet.
Column A I've filled in manually to show what I want to do.
I want to number (or group) the races but I'm struggling to do this because there's always a different amount of horses in a race.
As you can see Race 1 has 3 horses (same time/location in column C), Race 2 also has 3 horses, but Races 3 and 4 only have 1 horse etc etc....
The end result I'm after is to be able to filter out races with less than 3 horses, and then there will be some more filtering in column K.
The races must remain in chronological order.
(Ignore data in column E, these are not dates, it's a formatting problem.)
Any suggestions on where to begin?
Everything from columns B to K is data that I paste into the sheet.
Column A I've filled in manually to show what I want to do.
I want to number (or group) the races but I'm struggling to do this because there's always a different amount of horses in a race.
As you can see Race 1 has 3 horses (same time/location in column C), Race 2 also has 3 horses, but Races 3 and 4 only have 1 horse etc etc....
The end result I'm after is to be able to filter out races with less than 3 horses, and then there will be some more filtering in column K.
The races must remain in chronological order.
(Ignore data in column E, these are not dates, it's a formatting problem.)
Any suggestions on where to begin?
- Attachments
-
- Race groups.ods
- (15.62 KiB) Downloaded 123 times
Last edited by withnail on Mon Jul 01, 2019 10:50 pm, edited 2 times in total.
OpenOffice 4.1.7
Windows 10
Windows 10
Re: How to order rows into groups and filter them
Believe it or not, it almost sounds like you gave your own answer, but to be more specific, I would suggest using Data -> Filter -> Standard Filter
Before using that, I would like to recommend that you change your Dates (column B) and Times (column C) to be numbers rather than text.
For future reference, since you are pasting the information into the sheet, use Ctrl+Shift+V (Paste Special) and choose Unformatted Text. That way your dates and times should paste as numbers, rather than text.
If they don't, please provide a sample of the document used that the information is coming from.
Before using that, I would like to recommend that you change your Dates (column B) and Times (column C) to be numbers rather than text.
For future reference, since you are pasting the information into the sheet, use Ctrl+Shift+V (Paste Special) and choose Unformatted Text. That way your dates and times should paste as numbers, rather than text.
If they don't, please provide a sample of the document used that the information is coming 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: How to order rows into groups and filter them
Hi Russel,
I don't get what you mean I'm afraid...
I've been looking at using standard filter, but how can I for instance filter out races 7,8, and 9?
I know I can do it manually in this example but the real data contains thousands of rows, I want to be able to automatically remove all rows/races which have less than 3 horses in the race.
I was thinking along the lines of entering formula into column C where if there were 3 or more of the same consecutive values they would remain, and the rest would be deleted or filtered out.
Also, I selected column B, clicked 'format' and chose date format 12/06/1999, but it remains the same....
I'm scraping the data from database results on a website.
I don't get what you mean I'm afraid...
I've been looking at using standard filter, but how can I for instance filter out races 7,8, and 9?
I know I can do it manually in this example but the real data contains thousands of rows, I want to be able to automatically remove all rows/races which have less than 3 horses in the race.
I was thinking along the lines of entering formula into column C where if there were 3 or more of the same consecutive values they would remain, and the rest would be deleted or filtered out.
Also, I selected column B, clicked 'format' and chose date format 12/06/1999, but it remains the same....
I'm scraping the data from database results on a website.
OpenOffice 4.1.7
Windows 10
Windows 10
Re: How to order rows into groups and filter them
I'll make a spreadsheet with your sample data and an example of the data settings.
I think the settings will transfer in the spreadsheet, but I'll include information about the settings just in case.
Unfortunately I can't do it right now.
As to your attempt to reformat column B, you need to reimport the date information first as text will not change and text is what you currently have.
If you can't reimport the data, then getting the date information will take more work, but is possible
I think the settings will transfer in the spreadsheet, but I'll include information about the settings just in case.
Unfortunately I can't do it right now.
As to your attempt to reformat column B, you need to reimport the date information first as text will not change and text is what you currently have.
If you can't reimport the data, then getting the date information will take more work, but is possible
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: How to order rows into groups and filter them
Thanks!
If reformatting is a pain then I should say I don't really require the dates in column B for my tests.
It's the time/location in column C that would be used to do the sorting, and if easier the location (text) could just be erased as no races take place at exactly the same time anyway.
If the results retained their original row numbers then they would remain in chronological order anyway.
If reformatting is a pain then I should say I don't really require the dates in column B for my tests.
It's the time/location in column C that would be used to do the sorting, and if easier the location (text) could just be erased as no races take place at exactly the same time anyway.
If the results retained their original row numbers then they would remain in chronological order anyway.
OpenOffice 4.1.7
Windows 10
Windows 10
Re: How to order rows into groups and filter them
I still can't get this sorted out, so bump
OpenOffice 4.1.7
Windows 10
Windows 10
Re: How to order rows into groups and filter them
Insert a new row on top above the first row.I've been looking at using standard filter, but how can I for instance filter out races 7,8, and 9?
Type in column headers, for instance race, date, horse, etc.
menu:Data>Filter>AutoFilter...
LibreOffice lets you pick arbitrary crieria selections from the drop-down arrows, for instance Race1, Race5, Race7.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to order rows into groups and filter them
Thanks Villeroy.
I'm aware that I can manually filter races with less than 3 horses, but I want to be able to do it automatically as I have thousands of races to filter..
I'm aware that I can manually filter races with less than 3 horses, but I want to be able to do it automatically as I have thousands of races to filter..
OpenOffice 4.1.7
Windows 10
Windows 10
Re: How to order rows into groups and filter them
filter by which criteria? number of horses?
Is there any chance that the same race mentions the same horse twice?
Race99 ... Flying Dutchman ...
Race99 ... Flying Dutchman ...
Are duplicates like this possible or not?
Is there any chance that the same race mentions the same horse twice?
Race99 ... Flying Dutchman ...
Race99 ... Flying Dutchman ...
Are duplicates like this possible or not?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to order rows into groups and filter them
No, the same name will not appear twice in the same race. Names may be repeated further down the list in other races.
Yes, filter criteria is the number of horses.
Race 1, Race 2 etc in column A is just what I've entered manually to show more clearly what I want to do. The races don't need to be numbered or labelled in any way.
I just want any race that has 3 or more horses to remain (e.g., Race 1,2,6,10), and all races with less than 3 horses to be deleted (Race 3,4,5,7,8,9).
One way to do it could be by checking the number of duplicates in column C. So in column C, if "12:00 Tramore" appears 3 or more times in succession it would remain, if there were less than 3 duplicates they would be deleted.
Yes, filter criteria is the number of horses.
Race 1, Race 2 etc in column A is just what I've entered manually to show more clearly what I want to do. The races don't need to be numbered or labelled in any way.
I just want any race that has 3 or more horses to remain (e.g., Race 1,2,6,10), and all races with less than 3 horses to be deleted (Race 3,4,5,7,8,9).
One way to do it could be by checking the number of duplicates in column C. So in column C, if "12:00 Tramore" appears 3 or more times in succession it would remain, if there were less than 3 duplicates they would be deleted.
OpenOffice 4.1.7
Windows 10
Windows 10
Re: How to order rows into groups and filter them
See attached file. Last field is a counter. Apply a filter to that field.
Edit: My "solution" is rubbish. If the combination of horses and races is unique (not the same horse twice in the same race), the count of both can give nothing but 1. We just need to count the rows that belong to some race in order to count the horses. |
- Attachments
-
- Race groups_Counter.ods
- (20.84 KiB) Downloaded 99 times
Last edited by Villeroy on Tue Jul 02, 2019 9:41 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to order rows into groups and filter them
Thanks.
I get the idea but when I open the file the counter column just shows '1' all the way down.
If I try to filter it I can only choose 1, Empty or Not Empty.
It doesn't seem to be counting anything.
I'm using Libre Office 6.2
I get the idea but when I open the file the counter column just shows '1' all the way down.
If I try to filter it I can only choose 1, Empty or Not Empty.
It doesn't seem to be counting anything.
I'm using Libre Office 6.2
OpenOffice 4.1.7
Windows 10
Windows 10
Re: How to order rows into groups and filter them
Because in your example file each combination of race and horse occurs only once. If you apply this to your big data set, the result should be more significant.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to order rows into groups and filter them
Ah I see, our wires are crossed...
I don't need combination of race and horse, just races with 3 or more horses (regardless of horse names)
But I think I can modify your formula to suit, just needed an idea of how to go about it
EDIT: this code does exactly what I want =COUNTIFS($C$2:$C$23,$C2)
Thanks!
I don't need combination of race and horse, just races with 3 or more horses (regardless of horse names)
But I think I can modify your formula to suit, just needed an idea of how to go about it
EDIT: this code does exactly what I want =COUNTIFS($C$2:$C$23,$C2)
Thanks!
OpenOffice 4.1.7
Windows 10
Windows 10
Re: [Solved] How to order rows into groups and filter them
Hmm, I spoke too soon.....
I just used above formula and pasted a load of data into a sheet but it's not counting correctly.
If you scroll down attached sheet you can see it's counting 10, 11,13 or whatever for races that only have 2 or 3 horses.
I just used above formula and pasted a load of data into a sheet but it's not counting correctly.
If you scroll down attached sheet you can see it's counting 10, 11,13 or whatever for races that only have 2 or 3 horses.
- Attachments
-
- Race Groups Counter wrong.ods
- (66.85 KiB) Downloaded 85 times
OpenOffice 4.1.7
Windows 10
Windows 10
Re: [Solved] How to order rows into groups and filter them
Your formula works correctly. It counts how often this rows's horse occurs in the whole horse column.
You removed the column with the race info. Now we can not calculate how many horses appear in a given race
You removed the column with the race info. Now we can not calculate how many horses appear in a given race
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] How to order rows into groups and filter them
I see, race info column isn't normally there, I just added that in my first sheet to try and visualize what I wanted to achieve.
Basically, all I want to do is count successive duplicates in column B (in the last sheet I uploaded), and filter out all duplicates which are under 3.
Basically, all I want to do is count successive duplicates in column B (in the last sheet I uploaded), and filter out all duplicates which are under 3.
OpenOffice 4.1.7
Windows 10
Windows 10
Re: [Solved] How to order rows into groups and filter them
Pivot tables are simple.
This is the equivalent of counting the rows of a race which is equal to the horse count per race if the horses are unique within one race.Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to order rows into groups and filter them
I'm going to learn about pivot table's as I can see they will be useful for other projects.
But for now, how can I modify your formula to just count duplicates in 1 column?
Instead of it adding up as it proceeds down the sheet like in my last sheet.
But for now, how can I modify your formula to just count duplicates in 1 column?
Instead of it adding up as it proceeds down the sheet like in my last sheet.
OpenOffice 4.1.7
Windows 10
Windows 10