Possible Index Formula

[Celticshadow]

Hi All

I have a list of horses as attached with their betting forecast prices. What I would like to do is extract just the prices associated with those horses and place them in column B.

Thanks in advance.

Kind Regards

https://spreadsheets.google.com/spr...ffBUodHZ3anBXc0t6d0ZHVUZxMnVHUmQxTkE&hl=en_US

 

[Celticshadow]

Hi All

Maybe the title of the topic is incorrect (?) but I would really appreciate some help in discovering a formula that would allow me to do this in excel (I am using excel 2003 for info).

Thanks again in advance.

Kind Regards

 

[Aladin Akyurek]

Care to post the saple here along with the desired results?

 

[T. Valko]

Hi All

Maybe the title of the topic is incorrect (?) but I would really appreciate some help in discovering a formula that would allow me to do this in excel (I am using excel 2003 for info).

Thanks again in advance.

Kind Regards

I think the problem is that we can't make heads or tails out of what's posted in the Google SS. I know I can't!

Post some sample data here and tell us what result you expect.

 

[Celticshadow]

Hi All

I have updated the sheet to show what I require, apologies for any confusion.

Kind Regards



https://spreadsheets.google.com/spr...ffBUodEZ4ZXVxU2JFUXBURnExcTFpSUt5SlE&hl=en_US

 

[Sektor]

UDF. Place it to cell B2.
Horse - is a cell with horse (say, Range("A2"))
Prices - row with prices (Range("A10")

[COLOR="Blue"]Function[/COLOR] ExtractPrice(Horse [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR], Prices [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Double[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "\b(\d+)/(\d+)\s+" & Horse & "\b"
        [COLOR="Blue"]With[/COLOR] .Execute(Prices)(0)
            ExtractPrice = .SubMatches(0) / .SubMatches(1)
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Celticshadow]

Hi Sektor

Many thanks for your reply, unfortunately I require a formula and not coding as I do not understand coding. Apologies

Kind Regards

 

[Sektor]

1. Press Alt+F11
2. Insert -> Module
3. Paste my UDF.
4. Type in B2: =ExtractPrice(A2,A10)

 

[Celticshadow]

Hi Sektor

Many thamks for your reply but it does not work for me and I dont understand coding and I really require a formula. Apologies

Kind Regards

 

[Celticshadow]

Hi All

Does anyone have any ideas as to a possible solution.

Thanks in advance.

Kind Regards