Help with managing data. Complicated one!

[wizard007]

I'm new to macros and am trying something way above me here so any help appreciated!
I have a list of runners in a horse race as seen below. I need a macro to basically scan down the list and make sure there are exactly 3 horses listed in each race.
If 4 or more horses show I need to delete the bottom ones and if only 1 or 2 show I need to add rows to make sure there are 3 rows for that race.
As below you will see that the 2.10 race needs the bottom horse taken out as it has 4 horses in it and the 2.35 has only 2 so I need it to enter a blank like below it.

Any ideas anyone!?!?!?!?

If you need the spreadsheet I've attached the data as a text file.

Runners Track Time Horse Rating
13 NOTT 2.00 EXPENSIVE ART 87
13 NOTT 2.00 BERTIE SWIFT 76
13 NOTT 2.00 WELSH AUCTION 61
8 ASCO 2.10 FAT BOY 97
8 ASCO 2.10 MOUNT PLEASURE 95
8 ASCO 2.10 THUNDER BAY 81
8 ASCO 2.10 SAUZE D'OULX 81 <------ Delete this one as too many
9 PONT 2.20 CEE BARGARA 92
9 PONT 2.20 IRVING PLACE 74
9 PONT 2.20 BARRALAND 70
10 NOTT 2.35 NOBILISSIMA 76
10 NOTT 2.35 ELLCON 66
5 ASCO 2.45 SILVER PIVOTAL 88 <------ Add a blank here as needs to be 3 lines in each race
5 ASCO 2.45 ITALIAN GIRL 72
5 ASCO 2.45 FRETWORK 68
9 PONT 2.55 OFFICER 76
9 PONT 2.55 BINOCULAR 68
9 PONT 2.55 RANAVALONA 61
9 PONT 2.55 EL DECECY 61

Thanks in advance for helping out a newb!

[Jazzer]

Hi,

Assuming that your table starts from A1 and the first line contains the headers (Runners, Track, Time, Horse, Rating) and there are no empty lines in the data, this should work:

Please Login or Register  to view this content.

[wizard007]

Hi Jazzer,

You sir are a genious!!!!!!!

That worked a treat and has saved me loads of time.

Question?
Is there a way to make it only process the first 300 rows? There is only ever about 250-280 rows of data and the macro goes on for ages which I assume is the script checking the whole workbook.

Thanks again!

[Jazzer]

Hi and thank you :D

The ending of this line:

For lRow = 3 To Range("C1").End(xlDown).Row

after the "To", searches the last line of the sheet and uses it as the ending point of the loop. You can just write there the last line you want to check, like this:

For lRow = 3 To 300

That goes throug the first 300 lines.

- Asser

[wizard007]

Hi,

I tried that but it still just keeps going. I let it run for about 2 minutes and it was still churning away.

It doesn't matter really though as I can just hit escape and stop the script.

Thank for all your help.

[jetted]

maybe this macro can help, different flavor same result

Please Login or Register  to view this content.