How to order rows into groups and filter them

[withnail]

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?

[RusselB]

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.

[withnail]

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.

[RusselB]

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

[withnail]

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.

[withnail]

I still can't get this sorted out, so bump

[Villeroy]

I've been looking at using standard filter, but how can I for instance filter out races 7,8, and 9?

Insert a new row on top above the first row.
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.

[withnail]

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

[Villeroy]

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?

[withnail]

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.

[Villeroy]

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.

[withnail]

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

[Villeroy]

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.

[withnail]

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!

[withnail]

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.

[Villeroy]

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

[withnail]

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.

[Villeroy]

Pivot tables are simple.

Pivot_Count.ods

(23.86 KiB) Downloaded 103 times

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.

[withnail]

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.