deleting rows based on same value of one cell

[bizmark222]

Hi

race.png

I am copying and pasting contents of a web page of greyhound racing into excel and want it to automatically delete rows where the historic race distances don`t match the current race card distance (A19 -275m).
In the screen shot you will find that distance in A19 and historic race distances down column C.
The trouble is that there is more then just the distance in the cell (Race 3*D3 - 275m Flat) so I wonder if it can still be done?

[sheymyster]

There are a few options, so let us know what exactly you want to happen:
1. Rows that don't have correct distance are highlighted or somehow flagged so that you can see them and delete them yourself, manually.
2. Whatever you're trying to do with this data, such as average it or something with a formula, you want the rows that don't match the correct distance to be excluded from this calculation.
3. You want a VBA macro that will find the rows without the correct distance and delete them with a button push or something.

Depending on what you want, the solution will look differently but all of these should be possible. As for extracting the race distance from A19, you can probably just use the SEARCH formula to find "m ". This will rely on the assumption that the source of your data will always name the races similarly. The end of the distance should follow with a space before the next word so that will be unique and allow you to zero in on where the distance is positioned in the cell value. Then you can use LEFT and RIGHT to cut the other stuff out so you're just left with "275m" for example.

[bizmark222]

Hi
Thanks for the reply!
option 2 looks the best to me... the highlighted cells are showing top to bottom above/below average and top and bottom 10%. So I continue to copy and paste over the same sheet without having to keep setting it up but there are over 100 races a day and is tedious manually removing incorrect lines to come up with the correct distance to time averages. So if poss as soon as its pasted in preferably the lines are automatically deleted.

[bizmark222]

so how do I go about doing it ?

[JeteMc]

Here is my interpretation of what sheymyster is suggesting. The calculation excludes the rows that do not include 275m, however no rows are actually deleted.
1. A helper column (Q) finds the rows that should be included using: =ISNUMBER(SEARCH(C4,A$2))
2. The Average is calculated using: =AVERAGEIF(Q4:Q8,TRUE,P4:P8)
Let us know if you have any questions.