Look up help please

[danjuma]

Need some help with the following please.

I have the following manually copied and pasted from the oddschecker site in say column BU:

http://www.oddschecker.com/horse-racing ... well/14:10
http://www.oddschecker.com/horse-racing ... well/14:40
http://www.oddschecker.com/horse-racing ... well/15:10
http://www.oddschecker.com/horse-racing ... well/15:40
http://www.oddschecker.com/horse-racing ... well/16:10
http://www.oddschecker.com/horse-racing ... well/16:40

I want a formula in say cell BA1 to compare the race details in A1 (for example Font 23rd Sep - 14:10 2m2f Mdn Hrd in this instance) and select the correct corresponding race from the list in column BU to be displayed in cell BA1.

Many thanks
Dan

[osknows]

Hi Dan,

I wonder what this is for This may work....

=VLOOKUP("*"&MID(A1,FIND(":",A1,1)-2,5)&"*",$BU1:$BU100,1,FALSE)

[Ian]

Hi Dan & Os

I know what this is for

I've done the following formula which generates the required URL from A1

="http://www.oddschecker.com/horse-racing/"&VLOOKUP(LEFT(A1,4),Courses!A:B,2,FALSE)&MID(A1,FIND("-",A1)+2,5)


You need to set up a sheet called Courses with Col A & B as below

Asc ascot/
Aint aintree/
Ayr ayr/
Bang bangor/
Bath bath/
Bev beverley/
Brig brighton/
Carl carlisle/
Cart cartmel/
Catt catterick/
Chel cheltenham/
Chep chepstow/
Ches chester/
Donc doncaster/
Epsm epsom/
Extr exeter/
Fake fakenham/
Folk folkestone/
Font fontwell/
GLgh great-leighs/
Good goodwood/
Ham hamilton/
Hayd haydock/
Here hereford/
Hex hexham/
Hunt huntingdon/
Kels kelso/
Kemp kempton/
Leic leicester/
Ling lingfield/
Ludl ludlow/
Leop leopardstown/
Mrkt market-rasen/
Muss musselburgh/
Newb newbury/
Newc newcastle/
Newm newmarket/
Newt newton-abbot/
Nott nottingham/
Nad nad-al-sheba/
Pert perth/
Plum plumpton/
Pont pontefract/
Punc punchestown/
Redc redcar/
Ripo ripon/
Sali salisbury/
Sand sandown/
Sedg sedgefield/
Sthl southwell/
Stra stratford/
Taun taunton/
Thir thirsk/
Towc towcester/
Utto uttoxeter/
Warw warwick/
Weth wetherby/
Winc wincanton/
Wind windsor/
Wolv wolverhampton/
Worc worcester/
Yarm yarmouth/
York york/
List listowel/

[danjuma]

Yes guys, we know whose spreadsheet I want to use it for, and many thanks for the replies/suggestions

@Ian, your solution is brilliant! Many thanks. Just one thing, not sure whether it's a typo error from you, or the site has changed their links again!
One needs to add "-betting" after "horse-racing", so the formula should read:

="http://www.oddschecker.com/horse-racing-betting/"&VLOOKUP(LEFT(A1,4),Courses!A:B,2,FALSE)&MID(A1,FIND("-",A1)+2,5)

Cheers
Dan

[Ian]

Dan, I did the formula some time back when experimenting with web queries so they have at some stage changed how they word it. You'll have to check the list of courses as well as the weeks go by - I probably didn't use it everywhere.

Os's spreadsheet is 100% better than using web queries like I tried in the past. I think it his work that's brilliant not mine !!

Os, I tried to adapt the way you did the oddschecker spreadsheet to get the data from bestbetting (which I prefer to oddschecker). I've been able to isolate the horse name and prices into separate fields but they are prefixed and suffixed by some "blanks" which aren't blanks because TRIM doesn't get rid of them. Any ideas ?

Off to Market Rasen for a couple of days now.

[osknows]

The TRIM deletes spaces only, it could be the 'space' is actually a carriage return, new line or tab. It will definitely be something like that.

I usually use a Function to cleanup strings similar to the following

Code: Select all

Function CleanUpValue(val) As String
    CleanUpValue = Trim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(val, "</", vbNullString), ">", vbNullString), "<", vbNullString) _
            , vbNewLine, vbNullString), "'", vbNullString), "&acute;", vbNullString), "&mdash;", vbNullString), vbLf, vbNullString), Chr(34), vbNullString))
End Function


[danjuma]

...Os's spreadsheet is 100% better than using web queries like I tried in the past. I think it his work that's brilliant not mine !!

That goes without saying, the guy is a whizz and exceptional, and really, no amount of thanks is enough for his assistance and contributions to this forum!!!:D

Os, I tried to adapt the way you did the oddschecker spreadsheet to get the data from bestbetting (which I prefer to oddschecker). I've been able to isolate the horse name and prices into separate fields but they are prefixed and suffixed by some "blanks" which aren't blanks because TRIM doesn't get rid of them. Any ideas ?

Ian, is it possible to have a copy of this sheet when you have the finished product?

Many thanks
Dan

[alrodopial]

get the data from bestbetting (which I prefer to oddschecker).

I was using at some point the betfinder site (exactly the same as bestbetting) because it was lighter site but this was some time ago, I don't know what's going on today.

[lindemann]

A bit of help for an Excel/VBA numpty please!

How do I add Ian's URL formula to the spreadsheet? I've copied and pasted it into cell BA1 and into the formula bar. It seems to work for a while on the selected race but then throws up a VBA syntax error.

Thanks in advance.

[Ian]

I'm not having any problems with it lindemann.

I have noticed that if you copy the list of courses from my post to an excel sheet it suffixes the full course name with a blank which screws up the formula - you need to get rid if the extra blank.

[lindemann]

Thanks Ian. I've checked the Courses sheet and the way the URL displays and it all looks fine - no spaces or anything untoward.

When I get the Invalid Syntax error the debugger takes me to the ".Send" line in the module code. I hope that means something to somebody (coz it means v little to me I'm afraid).

[lindemann]

Just as I thought I'd solved my problems something else happens.....

I noted a mention of Excel 2010 elsewhere in the forum and this reminded me that my problems (above) were only happening on my laptop running Vista/Excel 2007. When I downloaded the Oddschecker sheet to my XP/Excel 2003 desktop and entered Ian's formula everything worked fine. Problem solved I thought.....but.....I then got a situation where there are no odds coming through from Oddschecker. It looks like the web query has connected to the right page because the list of runners is there but there's no odds data.

If I create a new web query in a new Excel worksheet it seems to retrieve data OK so I'm completely flummoxed as to why this sheet has stopped working on both my machines. I'd welcome any suggestions as this is starting to do my head in now.