BF Results : Number the race meetings in alphabetical order

[hughsiepops]

Morning

From the Betfair results pages here < http://promo.betfair.com/betfairsp/prices/ > I would like to number the race meetings in alphabetical order. To give you an example on the 3rd April there were 2 UK race meetings that day Donc and MrktR. So all those races at Donc would have a ‘1’ placed in a new column and those at MrktR would have the number ‘2’ .. I know I could do this manually when I copy across each days results into a master spreadsheet, but it would be far more efficient for me to copy a months worth of results and apply a formula to do all races for that month in one go.

I would like the numbering to start again from '1' each day. So on the 4th April there 3 UK race meetings. Wind, Folk and Kelso. Here the numbering would start again at Folk. [ Folk ‘1’, Kelso ‘2’ and Wind ‘3’ ].

Would I have to refer to Column B (MENU_HINT) as it is the only column that contains the Meeting’s name (Eg. GB / Donc 3rd Apr and GB / MrktR 3rd Apr). I have no idea on how this could be done. Is this possible to do via a formula that I can simply drag / copy down the worksheet.?

Any help or advance would be much appreciated

Thanks
Hughsiepops

[osknows]

I suspect you should really be doing this using a database rather than excel but here goes anyway. Firstly, the EVENT_DT is a mess in Betfair's data so needs tidied up.

Assuming data starts in row 2

In cell T2 put this:
=IF(ISERROR(DATEVALUE(D2)),DATE(YEAR(D2),DAY(D2),MONTH(D2)),DATEVALUE(D2))

In cell V2 put this:
=COUNTIF(INDIRECT(ADDRESS(MATCH(T2,$T$1:$T$65000,0),2,1)&":"&ADDRESS(MATCH(T2,$T$1:$T$65000,0)+COUNTIF($T$1:$T$65000,T2)-1,2,1)),"<="&B2)

In cell W2 put this array formula (CTRL+SHIFT+ENTER):
=SUM(1/(IF(INDIRECT(ADDRESS(MATCH(T2,$T$1:$T$65000,0),22,1)&":"&ADDRESS(MATCH(T2,$T$1:$T$65000,0)+COUNTIF($T$1:$T$65000,T2)-1,22,1))<V2,COUNTIF(INDIRECT(ADDRESS(MATCH(T2,$T$1:$T$65000,0),22,1)&":"&ADDRESS(MATCH(T2,$T$1:$T$65000,0)+COUNTIF($T$1:$T$65000,T2)-1,22,1)),INDIRECT(ADDRESS(MATCH(T2,$T$1:$T$65000,0),22,1)&":"&ADDRESS(MATCH(T2,$T$1:$T$65000,0)+COUNTIF($T$1:$T$65000,T2)-1,22,1))),9.999999999E+307)))+1

Then drag down