Complicated Data Organising OR Possibly Formula LOOKUPS HELP!...

[Ourkid123uk]

Hi guys,

This is it, my final part of my project and im completely stuck!

I have a Sheet Called "Data Table (From Web)" which is arranged in a table format. Using Formulas (I have just pasted values in example) the tables fill with the following Data.

'RACE COURSE NAME' / 'RACE TIME'

These tables are sepereated into Days 1-7 and also by Location UK and IRE.

The data i bring into these tables comes in Alphabetically and in coresponding Time Order. (this is Key as the data im then referecing is in same order).

To the right of these Header i have spaces for the following Data.

'DISTANCE' / 'CLASS' / 'PRIZE MONEY' / 'RUNNERS' / 'NON-RUNNERS'

These need to be filled by retrieving the relevant corresponding Data from the or Sheets organised by days. The daily Sheets it needs to reference are called "Web Page Racing Post UK (Day 1)", these go to "Day 7".


The problem im having is that the "Web Page Data" is retrived from a Web Querry and the data has no Clear order.

From trying LOTS of different approachs i have found some common Patterns which may help you guys figure out a Formula flexible enough to Refernce this Data for the different days and be accurate.

The DISTANCE / CLASS / PRIZE MONEY (Although some DONT have a Class) are all in the same Cell seperated by a Comma and in the Correct order from Left to Right.

The RUNNERS have a number (1 or 2 max) and is then followed by the word 'Ran' or sometimes 'ran'.

The NON-RUNNERS can be counted by two criteria;

If the Cells contents below the 'Ran' Row contains the text "Distances" then there are 0 Non-Runners. If the Cell below 'Ran' Doesnt contain "Distances" then 2 Cells further down contain the names of the Non-Runners. Again these could be returned as a value by then counting the commas.


Extra Information.

For the First Race Of the Day on the Sheet "Web Page Racing Post UK (Day 1)" The data i require is above the TIME of the Race. For all subsequent Races (in time order) then the TIME comes forst then the Data im after eg, DISTANCE / CLASS / PRIZE MONEY / RUNNERS / NON-RUNNERS.


HOW THE HECK CAN I GET THIS INFORMATION INTO MY TABLES!???

Forumals would work using logic but this i far beyond my caperbilities!

The Data is always formatted like shown in the attached example and i dont know enough about cutom WebScraping to make the data easier.

I cant customise the Web Querry as Excel doesnt recognise indervidual data, so the whole page has to be imported.


Is there a way to custom Filter or Sort or use Dynamic formulas to retrive the correct information intop my tables for UK and IRE?

I have attached a sample and highlighted the corresponding colours that show what data im trying to retrieve into the tables and where they are (randomly) found in the reference Sheets.

Im not getting my hopes up for this one, but ANY help from you guys would be GREATLY appreciated... I just know someone out there is genius anough to figure this out!


Thank you

[protonLeah]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)