Calculating running race position.

[Herakles]

The final bit of this project and then it's finished.

The position of a runner in a trail race is based upon three criteria, distance, gender and age category..

Runners matching these three criteria and the same time have the same position.

I could have a number of people in each position.

I only want to show positions 1 to 3.

Runners will be always be listed in finishing order.

There are 2 different distances run at the same time, 10 different age categories and 2 genders.

Any suggestions on a foolproof formula would be appreciated.

Thanks

10k Reports.xlsm
	A	B	C	D	E	F	G	H	I
1	App Ref	Number	Name	Club Name	Distance	Category	Gender	Time	Position
2	1	200	*** ***	******	6	31-40	M	00:23:00	1
3	2	201	*** ***	******	6	41-50	M	00:23:45	1
4	3	205	*** ***	******	6	41-50	M	00:23:45	1
5	4	300	*** ***	******	6	41-50	M	00:23:45	1
6	5	245	*** ***	******	6	41-50	M	00:23:45	1
7	6	212	*** ***	******	6	41-50	M	00:24:02	2
8	7	303	*** ***	******	6	41-50	M	00:24:03	3
Timekeeping

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:I183	Cell Value	=1	text	YES
I2:I183	Cell Value	=2	text	YES
I2:I183	Cell Value	=3	text	YES

 

[Skybluekid]

Try this

Book1.xlsm
	B	C	D	E	F
2	Distance	Category	Gender	Time	Position
3	6	41-50	M	00:23:02	2
4	8	41-50	F	00:23:12	1
5	6	41-50	F	00:23:45	1
6	8	41-50	M	00:24:04	1
7	8	41-50	F	00:24:15	2
8	8	41-50	F	00:24:23	3
9	6	41-50	M	00:23:32	3
10	6	41-50	F	00:23:56	2
11	8	41-50	M	00:24:15	2
12	6	41-50	F	00:23:57	3
13	8	41-50	M	00:24:15	2
14	6	41-50	M	00:22:40	1
Sheet5

Cell Formulas
Range	Formula
F3:F14	F3	=XMATCH(E3,SORT(FILTER($E$3:$E$14,B3&C3&D3=$B$3:$B$14&$C$3:$C$14&$D$3:$D$14),,1))

 

[Skybluekid]

I have altered it slightly by Sorting the table by a customer sort

Book1.xlsm
	A	B	C	D	E	F
2	Runner	Distance	Category	Gender	Time	Position
3	J	8	41-50	M	00:24:04	1
4	G	8	41-50	F	00:23:12	1
5	D	6	41-50	F	00:23:45	1
6	C	6	41-50	M	00:22:40	1
7	L	8	41-50	M	00:24:15	2
8	K	8	41-50	M	00:24:15	2
9	H	8	41-50	F	00:24:15	2
10	E	6	41-50	F	00:23:56	2
11	A	6	41-50	M	00:23:02	2
12	I	8	41-50	F	00:24:23	3
13	F	6	41-50	F	00:23:57	3
14	B	6	41-50	M	00:23:32	3
Sheet5

Cell Formulas
Range	Formula
F3:F14	F3	=XMATCH(E3,SORT(FILTER($E$3:$E$14,B3&C3&D3=$B$3:$B$14&$C$3:$C$14&$D$3:$D$14),,1))

 

[Herakles]

I have altered it slightly by Sorting the table by a customer sort

Book1.xlsm
	A	B	C	D	E	F
2	Runner	Distance	Category	Gender	Time	Position
3	J	8	41-50	M	00:24:04	1
4	G	8	41-50	F	00:23:12	1
5	D	6	41-50	F	00:23:45	1
6	C	6	41-50	M	00:22:40	1
7	L	8	41-50	M	00:24:15	2
8	K	8	41-50	M	00:24:15	2
9	H	8	41-50	F	00:24:15	2
10	E	6	41-50	F	00:23:56	2
11	A	6	41-50	M	00:23:02	2
12	I	8	41-50	F	00:24:23	3
13	F	6	41-50	F	00:23:57	3
14	B	6	41-50	M	00:23:32	3
Sheet5

Cell Formulas
Range	Formula
F3:F14	F3	=XMATCH(E3,SORT(FILTER($E$3:$E$14,B3&C3&D3=$B$3:$B$14&$C$3:$C$14&$D$3:$D$14),,1))

I have altered it slightly by Sorting the table by a customer sort

Book1.xlsm
	A	B	C	D	E	F
2	Runner	Distance	Category	Gender	Time	Position
3	J	8	41-50	M	00:24:04	1
4	G	8	41-50	F	00:23:12	1
5	D	6	41-50	F	00:23:45	1
6	C	6	41-50	M	00:22:40	1
7	L	8	41-50	M	00:24:15	2
8	K	8	41-50	M	00:24:15	2
9	H	8	41-50	F	00:24:15	2
10	E	6	41-50	F	00:23:56	2
11	A	6	41-50	M	00:23:02	2
12	I	8	41-50	F	00:24:23	3
13	F	6	41-50	F	00:23:57	3
14	B	6	41-50	M	00:23:32	3
Sheet5

Cell Formulas
Range	Formula
F3:F14	F3	=XMATCH(E3,SORT(FILTER($E$3:$E$14,B3&C3&D3=$B$3:$B$14&$C$3:$C$14&$D$3:$D$14),,1))

Thanks Skybluekid.

It works great apart from one thing.

The sheet I have posted identifies by the shading, missing positions because, for example, there have been two people who finished
together and are in position 2 in example 1.

Would this be a major change to have one in Position 1, two in Position 2 and one in Position 3?

It is not the end of the world if not. Our stash of prizes does not cater for this scenario.

 

[Herakles]

Thanks Skybluekid.

It works great apart from one thing.

The sheet I have posted identifies by the shading, missing positions because, for example, there have been two people who finished
together and are in position 2 in example 1.

Would this be a major change to have one in Position 1, two in Position 2 and one in Position 3?

It is not the end of the world if not. Our stash of prizes does not cater for this scenario.

10k Reports.xlsm
	A	B	C	D	E	F
1	Runner	Distance	Category	Gender	Time	Position
2	200	6	21-30	M	00:22:00	1
3	201	6	21-30	M	00:22:00	1
4	202	6	21-30	M	00:22:01	2
5	203	6	21-30	M	00:22:01	2
6	204	6	21-30	M	00:22:02	4
7	205	6	21-30	M	00:23:45	5
8	206	6	21-30	M	00:23:50	6
9	207	6	21-30	M	00:24:00	7
10	208	6	21-30	M	00:24:00	7
11	209	6	21-30	M	00:24:01	9
12	210	6	21-30	M	00:24:01	9
13	211	6	21-30	M	00:24:09	11
Sheet1

 

[Skybluekid]

This should do it.

Book1.xlsm
	A	B	C	D	E	F
2	Runner	Distance	Category	Gender	Time	Position
3	J	8	41-50	M	00:24:04	1
4	G	8	41-50	F	00:23:12	1
5	D	6	41-50	F	00:23:45	1
6	C	6	41-50	M	00:22:40	1
7	L	8	41-50	M	00:24:15	2
8	K	8	41-50	M	00:24:15	2
9	H	8	41-50	F	00:24:15	2
10	E	6	41-50	F	00:23:56	2
11	A	6	41-50	M	00:23:02	2
12	M	6	41-50	M	00:23:02	2
13	I	8	41-50	F	00:24:23	3
14	F	6	41-50	F	00:23:57	3
15	B	6	41-50	M	00:23:32	3
Sheet5

Cell Formulas
Range	Formula
F3:F15	F3	=XMATCH(E3,UNIQUE(SORT(FILTER($E$3:$E$15,B3&C3&D3=$B$3:$B$15&$C$3:$C$15&$D$3:$D$15,1))))

I would use named ranges so it would be easier to read.

 

[Herakles]

This should do it.

Book1.xlsm
	A	B	C	D	E	F
2	Runner	Distance	Category	Gender	Time	Position
3	J	8	41-50	M	00:24:04	1
4	G	8	41-50	F	00:23:12	1
5	D	6	41-50	F	00:23:45	1
6	C	6	41-50	M	00:22:40	1
7	L	8	41-50	M	00:24:15	2
8	K	8	41-50	M	00:24:15	2
9	H	8	41-50	F	00:24:15	2
10	E	6	41-50	F	00:23:56	2
11	A	6	41-50	M	00:23:02	2
12	M	6	41-50	M	00:23:02	2
13	I	8	41-50	F	00:24:23	3
14	F	6	41-50	F	00:23:57	3
15	B	6	41-50	M	00:23:32	3
Sheet5

Cell Formulas
Range	Formula
F3:F15	F3	=XMATCH(E3,UNIQUE(SORT(FILTER($E$3:$E$15,B3&C3&D3=$B$3:$B$15&$C$3:$C$15&$D$3:$D$15,1))))

I would use named ranges so it would be easier to read.

Thanks a lot.

I'll give it a try later.

The bottom row changes as runners finish and whose time is logged so I will have to make it dynamic.

 

[Skybluekid]

If you make the Distance, Gender, Category and Time all named ranges with a range of 5000 rows, you shold not run out.