OT: Another Excel question

[Tim]

I have a spreadsheet of race entries & results. You can see it here.

individual races (see tabs along the bottom), runners are awarded points according to place. One point for first, 500 points for last.

It's easy enough to calculate the points and if you look at the GEDM tab (Glasgow-Edinburgh double marathon) you can see all the points that each runner has scored.

My problem is in transfering the runners' points to the appropriate column in the "Participants" page which is a list of runners who have entered one or more of nine races in the Scottish Ultra Marathon Series. The GEDM was a relatively small race and it was easy enough to copy and paste individual points over but some of the races will have many more runners and I'm not looking forward to pasting them all individually.

Is there any way to automate this? I can't work out how to get around the fact that the lists have different numbers of runners. (The #s indicate races entered but not run yet).

Tim

[Andrew May]

Take a look at the VLOOKUP function. You will need to manipulate the names in the individual races to ensure that they are in the same format as in the participants table and you will also need to ensure that they are in alphabetical order but you can use sort for that.

Andrew

[Andrew May]

Sorry, alphabetical order is not required if you a doing an exact match.

Specifically look at something like:

VLOOKUP(A12,GEDM!$A$2:$H$73,8,FALSE)

where A12 is the cell with the name in on the Participants sheet GEDM!$A$2:$H$73 is the block of data on the GEDM sheet with the full name inserted into the first row and created by using =CONCATENATE(C2," ",D2) etc.

8 is the column number that now contains the race score and FALSE indicates that you want an exact match.

Wrap this in an IF and ISNA function to remove the #N/A error where the competitor was not in the race and this gives you something like:

=IF(ISNA(VLOOKUP(A12,GEDM!$A$2:$H$73,8,FALSE)),"",VLOOKUP(A12,GEDM!$A$2:$H$73,8,FALSE))

HTH etc.

Andrew

[Dave Osborne]

Probably, the best way of doing it is to make the participant sheet the "master record" and give each individual participant a unique number on this sheet.

Then on the race sheet, you enter the participant number and VLOOKUP the participant number and return the participant name name to avoid having to type the name out again. (and again and again for each race)

It is then trivial to VLOOKUP the result for an individual race back to the participant sheet.

Solution ========

1. If you wish, change the names on the Participant sheet so that they are in the form "surname, firstname secondname" this will facilitate alpha-sorting the names.

1. On participants sheet, add a new column to the left of column A and call this column "Participant Number"

2. Give each participant a unique number by (say) putting 101 in the first position and dragging down to increment the number.

1. insert two new columns in the GEDM sheet at the left-hand side. Call the first column "participant number" and the second "participant name"

2. Print off the participant sheet.

1. Using the participant sheet as a reference, manually add the participant numbers to column A of the GEDM sheet by matching names.

2. in column B2 of the GEDM sheet, type the following formula =IF(A2="","",VLOOKUP(A2,Participants!A:B,2,FALSE)) Drag this forumula down to copy it to every row in column B.

This will automagically add the names from the participants sheet to the gedm sheet.

1. Check that the names match in each row of the GEDM sheet, then delete columns D and E (names in the old format).

1. Switch to participants sheet and in cell C5 write the following formula: =IF(A5="","",VLOOKUP(A5,GEDM!A:G,7,FALSE)) Drag this formula down to copy it to every row in column C.

This will copy the results from the race back to the participants sheet.

[Andrew May]

I think this depends a little on where the race results come from. It looks a little as if they come in that format from the race organizers. Am I correct? If not and they are entered by Tim then the whole lot could be one on a single sheet.

[stuart noble]

I think maybe you're trying to get a spreadsheet to do the job of a relational database. If you have Access on your system there are plenty of templates you could adapt and it would be easy to import the Excel data into it. Things could get very complicated the way it is

[Jules Richardson]

Hmm, if it were me I'd bung the data in a database - or at least some sort of non-proprietary format that was easy to parse, and use some form of script (shell, AWK etc.) to do the grunt work. I'm not sure what the equivalent is if you're stuck with Windows, though :-(

cheers,

Jules

[Clive George]

Perl on both. I look back at my fumblings with sh with a bit of pride that I actually got things done, but no regrets at having moved on.

[stuart noble]

In Access you can see the data in an Excel form if that's what you're familiar with, but with the advantage that you can link any number of "worksheets" together in whatever way suits you now, or in the future. Once the competitors details are in there, you never have to re-enter, or copy and paste, again.

[Tim]

=IF(ISNA(VLOOKUP(A12,GEDM!$A$2:$H$73,8,FALSE)),"",VLOOKUP(A12,GEDM!$A$2:$H$73,8,FALSE))

Cheers Andrew, I'll give it a try.

Tim

[Tim]

The results (without points) will in all probability come in in lots of different formats. The GEDM results were in pdf format so I have to copy names, sex, age group and times into my sheet and then calculate the points.

I've only put the different results on different sheets for the sake of clarity.

Tim