Scoring XCountry

[woodywest]

Hi everyone...

I am trying to create a simple Cross Country Results template (I have a limited Excel background) to save time on race day... I have schools register athletes ahead of time, and I send each athlete a unique Competitor number.

Below is a crude look at the Sheet.... The first four columns (Place, Name, School and Comp #) are for overall placement... When I type in the Competitor #, the Student Name and Student School populates based on VLookup of Competitor data on another sheet. Where I have difficulty is the TEAM Scoring (School, Runner 1-5 and Total columns)... is there a simple formula that will look at the School column in the Team section, find itself in the School column of the Overall placement column and then put the Placement # from smallest to largest in the Runner columns? For Example, School 3 would look over and see itself in 2nd place, 6th Place, 7th Place, 16th place and 44th Place and put those numbers under Runner 1, Runner 2, etc.

There are 5 Runner columns... In the Total column, I will only sum Runners 1 - 4 for the Team total and can do this... Runner 5 is the tiebreaker for two teams tied in the standings... I can eyeball this. I just want an easy way to have the placement data auto-populate (I realize I can just sort by school and total, but would prefer something faster)

Thanks for any assistance!

[TABLE="width: 1074"]
<tbody>[TR]
[TD]Place[/TD]
[TD]Name[/TD]
[TD]School[/TD]
[TD]Comp #[/TD]
[TD]Grade 1 Girls - TEAM[/TD]
[TD][/TD]
[TD]School[/TD]
[TD]Runner 1[/TD]
[TD]Runner 2[/TD]
[TD]Runner 3[/TD]
[TD]Runner 4[/TD]
[TD]Runner 5[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Student172[/TD]
[TD]School4[/TD]
[TD="align: right"]172[/TD]
[TD="align: right"]1[/TD]
[TD]School1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Student152[/TD]
[TD]School3[/TD]
[TD="align: right"]152[/TD]
[TD="align: right"]2[/TD]
[TD]School2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Student825[/TD]
[TD]School12[/TD]
[TD="align: right"]825[/TD]
[TD="align: right"]3[/TD]
[TD]School3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Student1394[/TD]
[TD]School21[/TD]
[TD="align: right"]1394[/TD]
[TD="align: right"]4[/TD]
[TD]School4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Student567[/TD]
[TD]School9[/TD]
[TD="align: right"]567[/TD]
[TD="align: right"]5[/TD]
[TD]School5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Student161[/TD]
[TD]School3[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]6[/TD]
[TD]School6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Student79[/TD]
[TD]School3[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]7[/TD]
[TD]School7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Student1164[/TD]
[TD]School18[/TD]
[TD="align: right"]1164[/TD]
[TD="align: right"]8[/TD]
[TD]School8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Student484[/TD]
[TD]School9[/TD]
[TD="align: right"]484[/TD]
[TD="align: right"]9[/TD]
[TD]School9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Student258[/TD]
[TD]School5[/TD]
[TD="align: right"]258[/TD]
[TD="align: right"]10[/TD]
[TD]School10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Student166[/TD]
[TD]School4[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]11[/TD]
[TD]School11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]Student1438[/TD]
[TD]School22[/TD]
[TD="align: right"]1438[/TD]
[TD="align: right"]12[/TD]
[TD]School12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]Student599[/TD]
[TD]School10[/TD]
[TD="align: right"]599[/TD]
[TD="align: right"]13[/TD]
[TD]School13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Student508[/TD]
[TD]School9[/TD]
[TD="align: right"]508[/TD]
[TD="align: right"]14[/TD]
[TD]School14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]Student1146[/TD]
[TD]School17[/TD]
[TD="align: right"]1146[/TD]
[TD="align: right"]15[/TD]
[TD]School15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]Student75[/TD]
[TD]School3[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]16[/TD]
[TD]School16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]Student196[/TD]
[TD]School5[/TD]
[TD="align: right"]196[/TD]
[TD="align: right"]17[/TD]
[TD]School17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]Student746[/TD]
[TD]School12[/TD]
[TD="align: right"]746[/TD]
[TD="align: right"]18[/TD]
[TD]School18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]Student231[/TD]
[TD]School5[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]19[/TD]
[TD]School19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]Student1117[/TD]
[TD]School17[/TD]
[TD="align: right"]1117[/TD]
[TD="align: right"]20[/TD]
[TD]School20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]Student1290[/TD]
[TD]School20[/TD]
[TD="align: right"]1290[/TD]
[TD="align: right"]21[/TD]
[TD]School21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]Student1220[/TD]
[TD]School19[/TD]
[TD="align: right"]1220[/TD]
[TD="align: right"]22[/TD]
[TD]School22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]Student1150[/TD]
[TD]School17[/TD]
[TD="align: right"]1150[/TD]
[TD="align: right"]23[/TD]
[TD]School23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]Student1071[/TD]
[TD]School16[/TD]
[TD="align: right"]1071[/TD]
[TD="align: right"]24[/TD]
[TD]School24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD]Student1361[/TD]
[TD]School20[/TD]
[TD="align: right"]1361[/TD]
[TD="align: right"]25[/TD]
[TD]School25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD]Student149[/TD]
[TD]School3[/TD]
[TD="align: right"]149[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD]Student1476[/TD]
[TD]School22[/TD]
[TD="align: right"]1476[/TD]
[TD="align: right"]27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD]Student371[/TD]
[TD]School9[/TD]
[TD="align: right"]371[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD]Student240[/TD]
[TD]School5[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD]Student1137[/TD]
[TD]School17[/TD]
[TD="align: right"]1137[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD]Student226[/TD]
[TD]School5[/TD]
[TD="align: right"]226[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD]Student263[/TD]
[TD]School5[/TD]
[TD="align: right"]263[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD]Student732[/TD]
[TD]School12[/TD]
[TD="align: right"]732[/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD]Student1301[/TD]
[TD]School20[/TD]
[TD="align: right"]1301[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD]Student548[/TD]
[TD]School9[/TD]
[TD="align: right"]548[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD]Student1417[/TD]
[TD]School21[/TD]
[TD="align: right"]1417[/TD]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD]Student19[/TD]
[TD]School1[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD]Student1060[/TD]
[TD]School16[/TD]
[TD="align: right"]1060[/TD]
[TD="align: right"]38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD]Student822[/TD]
[TD]School12[/TD]
[TD="align: right"]822[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD]Student180[/TD]
[TD]School4[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD]Student249[/TD]
[TD]School5[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]41[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]42[/TD]
[TD]Student268[/TD]
[TD]School5[/TD]
[TD="align: right"]268[/TD]
[TD="align: right"]42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]43[/TD]
[TD]Student1379[/TD]
[TD]School20[/TD]
[TD="align: right"]1379[/TD]
[TD="align: right"]43[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD]Student61[/TD]
[TD]School3[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD]Student906[/TD]
[TD]School14[/TD]
[TD="align: right"]906[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DeusXv]

You could use something like this:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
51	Place	Name	School	Comp #	Grade 1 Girls - TEAM		School	Runner 1	Runner 2	Runner 3	Runner 4	Runner 5	Total
52	1	Student172	School4	172	1	School1		1	2	3	4	5	10
53	2	Student152	School3	152	2	School2		2	4	6	8	10	20
54	3	Student825	School12	825	3	School3		3	5	7	9	11	24
Sheet1

Cell Formulas
Range	Formula
M52	{=SUM(IF(F52=F52:F54,H52:K54,0))}
M53	{=SUM(IF(F53=F52:F54,H52:K54,0))}
M54	{=SUM(IF(F54=F52:F54,H52:K54,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

This just add's the first 4 team members and you can just drag this all the way down the sheet, just check the formula to make sure that the right school is selected and the range should stay the same.