Rank On Multiple Criteria

[TellM1955]

I'm looking for help on a formula to identify the top 3 positions in 2 Divisions on multiple dates. I've attached a sample file and highlighted the criteria in yellow. I've manually entered the top three positions where I am looking for a formula. Many thanks in anticipation.

 

[TellM1955]

Rank-Sample.xlsx
	A	B	C	D	E	F	G
1	Position	Name	Handicap	Points	Date	TwoDivs	Top 3
2	1st	AAA	-1	41	21/07/2022	Div 1	1
3	2nd	CCC	15	39	21/07/2022	Div 2	1
4	3rd	DDD	21	38	21/07/2022	Div 1	2
5	4th	BBB	13	38	21/07/2022	Div 2	2
6	5th	NNN	16	38	21/07/2022	Div 1	3
7	6th	MMM	13	37	21/07/2022	Div 2	3
8	7th	WWW	16	37	21/07/2022	Div 2	0
9	1st	FFF	17	38	03/08/2022	Div 2	1
10	2nd	GGG	18	37	03/08/2022	Div 2	2
11	3rd	AAA	13	36	03/08/2022	Div 1	1
12	4th	VVV	12	35	03/08/2022	Div 1	2
13	5th	NNN	6	35	03/08/2022	Div 1	3
14	6th	DDD	8	34	03/08/2022	Div 1	0
15	7th	GGG	22	34	03/08/2022	Div 2	3
16	8th	JJJ	6	34	03/08/2022	Div 1	0
17	9th	BBB	19	34	03/08/2022	Div 2	0
18	10th	CCC	13	33	03/08/2022	Div 2	0
19	1st	PPP	9	42	24/08/2022	Div 1	1
20	2nd	OOO	6	41	24/08/2022	Div 1	2
21	3rd	LLL	1	39	24/08/2022	Div 1	3
22	4th	GGG	15	39	24/08/2022	Div 2	1
23	5th	BBB	21	39	24/08/2022	Div 2	2
24	6th	WWW	11	39	24/08/2022	Div 1	0
25	7th	AAA	10	38	24/08/2022	Div 1	0
26	8th	FFF	15	37	24/08/2022	Div 2	3
27	9th	NNN	9	37	24/08/2022	Div 1	0
28	10th	YYY	4	37	24/08/2022	Div 1	0
Sheet1

 

[Snakehips]

@TellM1955 Hard part of this, for me, has been splitting tied scores an the basis of data supplied.
Think I have now sussed it but have settled for using a 'Helper' column, which can be relocated or hidden if required.

MayMrXL.xlsm
	A	B	C	D	E	F	G	H
1	Position	Name	Handicap	Points	Date	TwoDivs	Top 3	Helper
2	1st	AAA	-1	41	7/21/22	Div 1	1	40.999
3	2nd	CCC	15	39	7/21/22	Div 2	1	38.998
4	3rd	DDD	21	38	7/21/22	Div 1	2	37.997
5	4th	BBB	13	38	7/21/22	Div 2	2	37.996
6	5th	NNN	16	38	7/21/22	Div 1	3	37.995
7	6th	MMM	13	37	7/21/22	Div 2	3	36.994
8	7th	WWW	16	37	7/21/22	Div 2		36.993
9	1st	FFF	17	38	8/3/22	Div 2	1	37.999
10	2nd	GGG	18	37	8/3/22	Div 2	2	36.998
11	3rd	AAA	13	36	8/3/22	Div 1	1	35.997
12	4th	VVV	12	35	8/3/22	Div 1	2	34.996
13	5th	NNN	6	35	8/3/22	Div 1	3	34.995
14	6th	DDD	8	34	8/3/22	Div 1		33.994
15	7th	GGG	22	34	8/3/22	Div 2	3	33.993
16	8th	JJJ	6	34	8/3/22	Div 1		33.992
17	9th	BBB	19	34	8/3/22	Div 2		33.991
18	10th	CCC	13	33	8/3/22	Div 2		32.99
19	1st	PPP	9	42	8/24/22	Div 1	1	41.999
20	2nd	OOO	6	41	8/24/22	Div 1	2	40.998
21	3rd	LLL	1	39	8/24/22	Div 1	3	38.997
22	4th	GGG	15	39	8/24/22	Div 2	1	38.996
23	5th	BBB	21	39	8/24/22	Div 2	2	38.995
24	6th	WWW	11	39	8/24/22	Div 1		38.994
25	7th	AAA	10	38	8/24/22	Div 1		37.993
26	8th	FFF	15	37	8/24/22	Div 2	3	36.992
27	9th	NNN	9	37	8/24/22	Div 1		36.991
28	10th	YYY	4	37	8/24/22	Div 1		36.99
29
30
Sheet12

Cell Formulas
Range	Formula
G2:G30	G2	=IF(OR((COUNTIFS(H$2:H$30,">"&H2,F$2:F$30,F2,E$2:E$30,E2)+1)>3, B2="" ),"",COUNTIFS(H$2:H$30,">"&H2,F$2:F$30,F2,E$2:E$30,E2)+1)
H2:H30	H2	=IFERROR(D2-IF(ISNUMBER(1*MID(A2,2,1)),LEFT(A2,2),LEFT(A2,1))/1000,"")

Hope that helps.

 

[TellM1955]

@TellM1955 Hard part of this, for me, has been splitting tied scores an the basis of data supplied.
Think I have now sussed it but have settled for using a 'Helper' column, which can be relocated or hidden if required.

MayMrXL.xlsm
	A	B	C	D	E	F	G	H
1	Position	Name	Handicap	Points	Date	TwoDivs	Top 3	Helper
2	1st	AAA	-1	41	7/21/22	Div 1	1	40.999
3	2nd	CCC	15	39	7/21/22	Div 2	1	38.998
4	3rd	DDD	21	38	7/21/22	Div 1	2	37.997
5	4th	BBB	13	38	7/21/22	Div 2	2	37.996
6	5th	NNN	16	38	7/21/22	Div 1	3	37.995
7	6th	MMM	13	37	7/21/22	Div 2	3	36.994
8	7th	WWW	16	37	7/21/22	Div 2		36.993
9	1st	FFF	17	38	8/3/22	Div 2	1	37.999
10	2nd	GGG	18	37	8/3/22	Div 2	2	36.998
11	3rd	AAA	13	36	8/3/22	Div 1	1	35.997
12	4th	VVV	12	35	8/3/22	Div 1	2	34.996
13	5th	NNN	6	35	8/3/22	Div 1	3	34.995
14	6th	DDD	8	34	8/3/22	Div 1		33.994
15	7th	GGG	22	34	8/3/22	Div 2	3	33.993
16	8th	JJJ	6	34	8/3/22	Div 1		33.992
17	9th	BBB	19	34	8/3/22	Div 2		33.991
18	10th	CCC	13	33	8/3/22	Div 2		32.99
19	1st	PPP	9	42	8/24/22	Div 1	1	41.999
20	2nd	OOO	6	41	8/24/22	Div 1	2	40.998
21	3rd	LLL	1	39	8/24/22	Div 1	3	38.997
22	4th	GGG	15	39	8/24/22	Div 2	1	38.996
23	5th	BBB	21	39	8/24/22	Div 2	2	38.995
24	6th	WWW	11	39	8/24/22	Div 1		38.994
25	7th	AAA	10	38	8/24/22	Div 1		37.993
26	8th	FFF	15	37	8/24/22	Div 2	3	36.992
27	9th	NNN	9	37	8/24/22	Div 1		36.991
28	10th	YYY	4	37	8/24/22	Div 1		36.99
29
30
Sheet12

Cell Formulas
Range	Formula
G2:G30	G2	=IF(OR((COUNTIFS(H$2:H$30,">"&H2,F$2:F$30,F2,E$2:E$30,E2)+1)>3, B2="" ),"",COUNTIFS(H$2:H$30,">"&H2,F$2:F$30,F2,E$2:E$30,E2)+1)
H2:H30	H2	=IFERROR(D2-IF(ISNUMBER(1*MID(A2,2,1)),LEFT(A2,2),LEFT(A2,1))/1000,"")

Hope that helps.

Snakehips, thanks for your help. I think I've mislead you with the requirement to which I apologise. I think I better explain further.
The position in column A is the position the individual has finished. Depending on the individuals Handicap, column C, determines whether they are in Division 1 or 2, column F. So the requirement is; from the positions in column A determine the top three positions that fill Division 1 and 2 from each date. Hope this makes sense?

 

[TellM1955]

Snakehips, thanks for your help. I think I've mislead you with the requirement to which I apologise. I think I better explain further.
The position in column A is the position the individual has finished. Depending on the individuals Handicap, column C, determines whether they are in Division 1 or 2, column F. So the requirement is; from the positions in column A determine the top three positions that fill Division 1 and 2 from each date. Hope this makes sense?

Snakehips, disregard my last. I wasn't looking at it correctly. It works perfect thanks

 

[smozgur]

It works perfect thanks

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.