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!

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

<tbody>
</tbody>

 

[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.