Help With x3 Formulas to Return Relevant Values Into x2 Tables...

[Ourkid123uk]

Hi all,

I've nearly completed an epic learning project but stuck at the final Furlong.

Im working with Horse Racing Data Imported from the Web. I have rearranged and Custom Filtered the best i could and finally have the Filtered minimal amount of Data that i can possibly achieve to help with my final stage.

I need help with x3 Formulas to be Filled down in my tables.


The Problem;


I have Two Tables, One for UK Races and the other for IRE races. For each of these i already have the 'Course Name' & 'Time' of race. (imported from a Custom Power Query).

I then have 3 more Fields which i need Formulas to return the relevant data from the Filtered Web Page Data. These are, 'Distance, Class, Prize Money' , 'Runners' & 'Non-Runners'.

The problem i have is that although the Data is in Time order, I have to separate the Data into the Tables from UK & IRE Races. The good news is that the data i have Filtered is very consistent in its format.

I have attached a stripped back Worksheets which includes a UK & IRE (underneath UK) Table with the Fields 'Distance, Class, Prize Money' , 'Runners' & 'Non-Runners' left blank for the problem Formulas. Next to this i have included an example Table demonstrating for this example the correct Information that should be returned by the Formulas.

I have Color coded the relevant Cells that relate to each other if that helps.

As you can see from my attachment, the 'Distance, Class, Prize Money' Field is self explanatory but just needs the relevant information for the correct race to be placed into the Tables. The 'Runners' Field needs to again import the relevant Data which is eg, "16 Ran" but just return the number, so "16" into the relevant table position. Finally for the 'Non-Runners' Field i need the formula to not only import the correct data into the Table in the Correct position but return a value based on the number of commas in the Cell below "NR:" from the Web Data.

For example,

NR:
Aisteoir (passport irregularities), Ardglen Flyer (temperature)

i would need to return the Value of 2, or,

NR:
Lilly Be (unsuitable ground)

i would need to return the value of 1.

The pattern is as follows,

If there are 'Non-Runners' and 0 Commas then return the value of 1,

If there are 'Non-Runners' and 1 Commas then return the value of 2,

If there are 'Non-Runners' and 2 Commas then return the value of 3,

etc....

I know this sounds REALLY complicated but if you do kindly take a look at the attachment you can see its just x3 Formulas possibly INDEX/MATCH that can then be Filled down the Tables.

Finally, the Table Data and Web Data change so the x3 Formulas are required rather than just referencing the corresponding Cells.


I really appreciate any help or advice and please just let me know if you need any more information if you fancy having a crack at helping.


Many Thanks guys!

(ps, Scroll down for the IRE Tables)

[dflak]

I don't see how you are matching race results in column O with the summary in Columns H:L, but this is the formula you will need. In its general form it's =Len(CellRef) - Len(Substitute(CellRef,"Expression", "")). This formula tells you how many times the Expression appears in the Cell Reference.

In your case it translates into =LEN(O12)-LEN(SUBSTITUTE(O12,",",""))+1

The plus 1 is because you want one more than the number of commas. The nice thing about this formula is it does not matter how many commas there are. It does not stop at 3.

[JohnTopley]

There are many inconsistencies in the data e.g NR not present so I think a formula-based solution is going to be very difficult: especially the need to "count" the non-runners based on the presence or not of a comma.

And I am finding that Matching the times does not work without me reformatting the times or similar.

These are the formulae I tried

For column C

=INDEX($O$4:$O$1000,MATCH($A2,$O$4:$O$1000,0)+MATCH($B2,OFFSET($O$4,MATCH($A2,$O$4:$O$1000,0),0,1000,1),0)-4)

fails if NR not present: we have to count back from the time - hence the "-4"

Similar for column D

=SUBSTITUTE(INDEX($O$4:$O$1000,MATCH($A2,$O$4:$O$1000,0)+MATCH($B2,OFFSET($O$4,MATCH($A2,$O$4:$O$1000,0),0,1000,1),0)-3),"ran","")

Column E

=LEN(INDEX($O$4:$O$1000,MATCH($A2,$O$4:$O$1000,0)+MATCH($B2,OFFSET($O$4,MATCH($A2,$O$4:$O$1000,0),0,1000,1),0)-1))-LEN(SUBSTITUTE(INDEX($O$4:$O$1000,MATCH($A2,$O$4:$O$1000,0)+MATCH($B2,OFFSET($O$4,MATCH($A2,$O$4:$O$1000,0),0,1000,1),0)-1),",",""))+1

And be consistent with names "EPSOM DOWNS" vs "EPSOM"

[mcmahobt]

Believe I may have interpreted your request correctly. One change I had to make was to how you wrote EPSOM in your initial workbook since it did not match your filter results (or at least could not easily match based on a wildcard).

Proposed formulas, however bulky, do seem to return the correct results.

[JohnTopley]

@mcmahobt: Nice work. I see you used TIMEVALUE (missed that when I had my problems with the time) and looking at the formulae it is dependent on all time being unique?

[mcmahobt]

@mcmahobt: Nice work. I see you used TIMEVALUE (missed that when I had my problems with the time) and looking at the formulae it is dependent on all time being unique?

I considered that as a potential problem, thus the used of the Named Range. Take DONCASTER for example: the Named Range I use begins with matching the first instance of DONCASTER, then starting the range there. Then in the INDEX formula, the MATCH array is based off of the Named Range, so will return the first instance of TIME FOR WEB, which (hopefully non-coincidentally) is filtered in ascending order by FULL NAME.

Clear as mud? Does my logic make sense to you?

[AlKey]

Hi mcmahobt,
You can make your formula much shorter if you replace TIMEVALUE(I2, with just --I2

[Ourkid123uk]

Guys!!!....

THANK YOU!

Wow, i wouldn't have been able to do that with my present knowledge. Ill go through and try and learn exactly how each of your Formulas works and their advantages and disadvantages.

Ill be using mcmahobt's formula purely as it all works without any tweaking, thank you!

I have just a couple of questions on your mcmahobt's Formulas. (Forgive me if these sound stupid)

1) What is "Rng" and how do you relate that to the Cells correctly? I initially thought it was a Range Defined Name but its not!? Basically if my Web Data is located in a different WorkBook or Column how do i Reference this correctly?

2) "EPSOM DOWNS" did not work correctly but "EPSOM" did. Is this due to the fact it is two words within one cell? (ill need to know for other course names)

3) After copying the formula down the Cells that 'Surplus' returns the "#VALUE!" error. How can i get this to not show?

Guys i REALLY appreciate what you have done here! Ill leave REP for all of your and hopefully i can return your knowledge i have learnt from yourselves to other in the future.

THANK YOU!

[Ourkid123uk]

Then in the INDEX formula, the MATCH array is based off of the Named Range, so will return the first instance of TIME FOR WEB, which (hopefully non-coincidentally) is filtered in ascending order by FULL NAME.

Clear as mud? Does my logic make sense to you?

Yes thankfully this is correct! I did the best i could to Filter the Data in a way that i thought someone would be able to help me with... thank you!

[mcmahobt]

1) Rng is a Named Range, it should show up if you navigate within the Ribbon to Formulas > Name Manager. You'll need to change the references according to where your actual data is stored. PM me if you need more help with this.
2) Yes and No. I used a wildcard in the MATCH formula. You can google the term to see exactly how it works, but basically because you had EPSOM written in column O, and EPSOM DOWNS written in column H, which I was using as the reference to search through column O for a match, no result would have been found.
3) Wrap each formula in IFFERROR(,"").

[JohnTopley]

@mcMahobt: all makes sense now after I looked carefully at Rng definition: my own (less sophisticated!) version was taking the same approach.

[Ourkid123uk]

Brilliant!

1) Found it! I've had a look through and i think ill be able to tweak it for use in my main spreadsheet! I was trying to Use VBA and the COUNTA function to create Dynamic Ranges but i just don't have the knowledge yet to have done so.
2) Seen as i had to make weird and wonderful *wildcard's for my Advanced Filter im sure ill be able to sort that now i know i'm looking under the 'Name Manager'.
3) Of course! Yes i will indeed add that to the Formulas, thanks!

Ill try and fully implement this into my "Working" Spreadsheet and tweak as needed. Hopefully it will all still remain in tact and working how you have it!

I really cant thank you enough