Data Consolidation and Scoring
Budget: £20 – £250 GBP
I need to merge 6-10 sheets into one master sheet, pulling both text and numbers. The relevant data will be identified by specific column headings. After merging, I need rows scored based on the data in each row. In summary I get horse racing data each day from several sheets, I want a master spreadsheet to only pull the data I need then score the horses using a formula. I would like the master spreadsheet to lock in the headers and pivot table, then sort the horses by track, race time then horse name. I have spent quite some time trying to teach myself to do this but I'm not sure whether its using chat GPT to sort the initial spreadsheets but I keep getting errors so want to eliminate the need for AI.
You'll see from the downloaded files the format that I download the original data in and then what I am trying to do with it in the master sheet. Once I downloaded all of the horse racing data I would then load it into chat gpt with the following instruction:
'Can you analyse the Horse or Horse-name column in each of the spreadsheets and if the horse name isn't duplicated in all spreadsheets then remove it from the spreadsheets that have the extra name. Make sure that each sheet has the same number of rows at the end with the same horse names. On each sheet can you change the column heading for the name of each horse to 'Horse' so that all sheets have the same column heading. Once analysed can you sort them by track a-z, then time earliest to latest, then horse name a-z. When finished can you provide individual download links and the confirm the number of rows please.'
The reason I want it to clean the data is depending when I download the data, some horses might be withdrawn and therefore I would not need to include them, but some of the data produces a single daily sheet whereas others update throughout the day. From here I would then load the Master sheet and refresh the data queries. I have also added a My Betting Rules document. There is a row in the table that explains what formula I want in the OR vs Weight column, if you could include this and ensure that it is included in the raw score.
If you could cross reference the scoring on the master spreadsheet against my formulas as I believe the won at OR and won at Weight might be wrong.
Ideally I would like to just download the files, then have a button on the master spreadsheet that updates when pressed.
Ideal skills and experience:
- Proficiency in spreadsheet software (Excel, Google Sheets)
- Experience in data merging and manipulation
- Ability to create scoring systems based on row data
- Attention to detail and accuracy
You'll see from the downloaded files the format that I download the original data in and then what I am trying to do with it in the master sheet. Once I downloaded all of the horse racing data I would then load it into chat gpt with the following instruction:
'Can you analyse the Horse or Horse-name column in each of the spreadsheets and if the horse name isn't duplicated in all spreadsheets then remove it from the spreadsheets that have the extra name. Make sure that each sheet has the same number of rows at the end with the same horse names. On each sheet can you change the column heading for the name of each horse to 'Horse' so that all sheets have the same column heading. Once analysed can you sort them by track a-z, then time earliest to latest, then horse name a-z. When finished can you provide individual download links and the confirm the number of rows please.'
The reason I want it to clean the data is depending when I download the data, some horses might be withdrawn and therefore I would not need to include them, but some of the data produces a single daily sheet whereas others update throughout the day. From here I would then load the Master sheet and refresh the data queries. I have also added a My Betting Rules document. There is a row in the table that explains what formula I want in the OR vs Weight column, if you could include this and ensure that it is included in the raw score.
If you could cross reference the scoring on the master spreadsheet against my formulas as I believe the won at OR and won at Weight might be wrong.
Ideally I would like to just download the files, then have a button on the master spreadsheet that updates when pressed.
Ideal skills and experience:
- Proficiency in spreadsheet software (Excel, Google Sheets)
- Experience in data merging and manipulation
- Ability to create scoring systems based on row data
- Attention to detail and accuracy