Support road.cc

Like this site? Help us to make it better.

HELP

In an effort to speed me up a bit, I'm having a hard time fitting in time to get all the riders birthdays.

So I'm calling on all you guys. If I give you 200 names and countries. Can you fill in the excel file with their birthdays and mail it back to me?

If you fancy it, leave a reply here and I will send you a message through the site.

Need about 8 people (unless you want to do more) to get this task finished and speed up getting the 2015 open...

So who's up for it?

I'll give full instructions of what to do and where to find the information.

If you're new please join in and if you have questions pop them below and the forum regulars will answer as best we can.

Add new comment

24 comments

Avatar
Tinternet_tim | 9 years ago
0 likes

Just checking I've understood you correctly
You already have their d.o.b's but they are not in the correct format?

If I have understood your problem correctly the formulas I have shown in the image I attached works.

If you want to send me the XL document via road.cc message I'll have a look tonight when I get home from work.

Avatar
Gkam84 | 9 years ago
0 likes

No idea, can't get it to work. I'll keep filling them in and work it out later

Avatar
Gkam84 | 9 years ago
0 likes

Cheers. I'll go have a play about

Avatar
Gkam84 | 9 years ago
0 likes

Four cells like

First name/ Surname/ Nationality/ DOB

Avatar
Tinternet_tim replied to Gkam84 | 9 years ago
0 likes

In that case, just use the formulas from column C to G to convert the date format into the format you required.
You can then drag the formula to the bottom of your dataset to transform all the dates into the correct format and save you a day or more work.
Don't forget to copy >paste special > value into a new cell to the right of your data. You can then delete the columns with the formulas in to tidy the spreadsheet up.

I'm assuming the above makes sense and does what you wanted?

Avatar
Tinternet_tim | 9 years ago
0 likes

Is all of that in 1 cell or in 3 different cells?

for example
Cell A1 = Keith McRae GBR 11th May 1984
Or
Cell A1 = Keith McRae
Cell B1 = GBR
Cell C1 = 11th May 1984

Also, how is a month displayed if it is more than 3 letters? Is September spelt in full or Sep?

Avatar
Gkam84 | 9 years ago
0 likes

At the moment I have it like this on most

Keith McRae GBR 11th May 1984

I want it to be

Keith McRae GBR 11/05/84

I have tried removing the (th) and using excel to format it, with no joy

Avatar
Tinternet_tim replied to Gkam84 | 9 years ago
0 likes

Keith,
If it's all in 1 cell try using the attached formulas to get it into the format you want. I have used your data in row 1 to make sure it works.
Row 2 is dummy data to test and row 3 are the formulas I have used to transform the data into the required format.
Copy column G and paste special > values into column H so you have just values and not formulas.

If the dates are in their own cell (e.g. column B) and not contained in a cell with the name and country (column A) you can still use the attached logic, but just formulas from column C to G

If the Months are displayed in a format greater than 3 letters then it'll be a little more complex but is still possible.

Avatar
Gkam84 | 9 years ago
0 likes

The birthdays are all in the wrong format. I am getting there, only about 1000 to day now

Avatar
Tinternet_tim replied to Gkam84 | 9 years ago
0 likes
Gkam84 wrote:

The birthdays are all in the wrong format. I am getting there, only about 1000 to day now

If it is purely a case of the date being in the wrong format then you should be able to use Excel to convert it by either:

Format cell, date

or if the date isn't in a date format you should be able to use an IF() statement or LEFT(), RIGHT() function. Once the equation is worked out you should be able to do it in seconds.

It is also possible to use a VLOOKUP() to match 1 set of data with another.

If you want to give an example of the problem then I'll have a look to see if I can work something out.

Avatar
cherrypicked | 9 years ago
0 likes

I am pretty sure this is it but can't download it on safari: http://www.cqranking.com/men/asp/gen/downloads.asp
Hope it helps  1

Avatar
cherrypicked | 9 years ago
0 likes

http://www.uci.ch/road/ranking/

I can't seem to find place to download entire file on that website. That may be due to mobile device limitations. Maybe I got it from a third party...

Avatar
cherrypicked | 9 years ago
0 likes

Sample data
Rank Prev. Rider Nat. + Birthdate Team (Code) Points
1 1 VALVERDE BELMONTE Alejandro ESP19800425 MOVISTAR TEAM (MOV) 686
2 2 CONTADOR VELASCO Alberto ESP19821206 TINKOFF-SAXO (TCS) 620
3 3 GERRANS Simon AUS19800516 ORICA GREENEDGE (OGE) 478

Avatar
cherrypicked | 9 years ago
0 likes

I'm pretty sure you can use the UCI id for the birthday. Download the file, manipulate the column and you have them all.

The bigger problem is when you try to match these ids to road.cc and pcs. I tried. Becomes a bit of a manual exercise, after matching some by surname etc Some of the ids (not the uci) change too!

Away on holidays at the moment. See if I can find and post the link

Avatar
Gkam84 | 9 years ago
0 likes

Not sticking it into google docs, as that would require giving a link out where anyone couple do anything to it.

I've sent a couple of you messages. TheDoctor and Stevemarks I can't because of your settings. So if anyone else wants to get involved. Fire me an email.

Guitaristkam [at] gmail.com

Avatar
Joelsim | 9 years ago
0 likes

Stick it up as a googlesheet as advised

Avatar
Joelsim | 9 years ago
0 likes

I can do a bit Gkam too

Avatar
stevemarks | 9 years ago
0 likes

 24  24  24

Yes I could spare a few hours Sunday night if that helps.

Avatar
nigsbrown | 9 years ago
0 likes

I can help out this weekend.

Avatar
Gkam84 | 9 years ago
0 likes

Why would we want to involve another site in our fantasy game?

Avatar
Twybaydos | 9 years ago
0 likes

Could you ask PCS or cycling fever if they can help? Would they not already have this data?

If we can get the info in date order then could that be populated on a month by month basis by 12 people?

Avatar
Al__S | 9 years ago
0 likes

If you put the spreadsheets on google docs multiple people can fill in information on the same document at once- you can still control access.

Avatar
Gkam84 | 9 years ago
0 likes

Cheers TER, Struggling a little, got 2700 birthdays to fill. I've split of 8 files for other to do if they can, which leaves me about 1400 still to find  105

Avatar
TERatcliffe26 | 9 years ago
0 likes

I will email you tomorrow if I can do it, need to get some software on my new laptop first

Latest Comments