Rank a list

[saitken]

Hi

I have created a spreadsheet for a charity golf competition. I enter all the teams scores and have a tab 'Rank calc' that ranks all the scores and has a formula to work out the equal ranks e.g. 4th equal. But it is not working properly and I can't work out what the error is. The competition is on Friday, so if someone can help fix the ranking or knows another way to do the same this please let me know.
Thank you

Copy of Rotary AM-AM v1 orig.xlsx
	A	B	C	D	E	F	G
1	Team Name	Score	Rank		Place	Team Name	Score
2	Cairndale Hotel	87	8		1	Ian McIntyre	110
3	Octoputts Golfing Society	54	16		2	Andrew Hunter	100
4	Metrik Solutions	67	11		3	Raumond Marshall	99
5	Golden Girls	34	25		4	Heathhall Garden Centre	97
6	Peter Allen	89	5		5=	Peter Allen	89
7	BB Bodyrepairers	45	22		5=	Maureen McKerrow	89
8	Mathers Ltd	67	12		7	Alba Printers Ltd	88
9	Mike McCormick	76	10		8	Cairndale Hotel	87
10	Troqueer Garage	45	23		9	Retired Firefighters	77
11	Maureen McKerrow	89	6		10	Mike McCormick	76
12	Cameron McInnes	43	24		11=	Metrik Solutions	67
13	Tom Bell's Team	65	14		11=	Mathers Ltd	67
14	Ian McIntyre	110	1		13	Gordon Lawson	66
15	Heathhall Garden Centre	97	4		14	Tom Bell's Team	65
16	Emma Clark	23	26		15	Donald MacDonald	55
17	Donald MacDonald	55	15		16=	Octoputts Golfing Society	54
18	Gordon Lawson	66	13		16=	Jock Simpson	54
19	Retired Firefighters	77	9		18=	Les Martin	49
20	Alba Printers Ltd	88	7		18=	Les Martin	49
21	Raumond Marshall	99	3		18=	John Maxwell	49
22	Andrew Hunter	100	2		18=	John Maxwell	49
23	Dfs Tool & Machy Repairs	47	21		22=	JD Engineering Ltd	48
24	JD Engineering Ltd	48	20		22=	JD Engineering Ltd	48
25	Les Martin	49	18		24=	Dfs Tool & Machy Repairs	47
26	John Maxwell	49	19		24=	Dfs Tool & Machy Repairs	47
27	Jock Simpson	54	17		26=	BB Bodyrepairers	45
28	Margaret Hamilton	54	18		26=	Troqueer Garage	45
29	Queen of the South	54	19		28	Cameron McInnes	43
30	Queen of the South seconds	54	20		29	Golden Girls	34
31	Angus Robertson	54	21		30	Emma Clark	23
Rank calc

Cell Formulas
Range	Formula
A2:A31	A2	='Score Card'!C3
B2:B31	B2	='Score Card'!AC3
C2:C31	C2	=RANK(B2,$B$2:$B$27,0)+COUNTIF($B$2:B2,B2)-1
E2	E2	=IF(G2=G3,1 & "=",1)
F2:F31	F2	=OFFSET(A$2,MATCH(SMALL(C$2:C$31,ROW()-ROW(F$2)+1),C$2:C$31,0)-1,0)
G2:G31	G2	=INDEX($A$1:$B$31, MATCH(F$2:F$31,$A$1:$A$31,), MATCH("Score",$A$1:$B$1,))
E3:E31	E3	=IF(G3=G2,LEFT(E2,LEN(E2)-1) & "=",IF(G3=G4,ROW()-2+1 & "=",ROW()-2+1))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what exactly isn't working?

 

[saitken]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what exactly isn't working?

Excel 365 on Mac. Account details updated.

It doesn't rank correctly from the final 18th entry. "John Maxwell" appears twice and all the teams below are not correctly ranked.
Thanks

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Team Name	Score	Rank		Place	Team Name	Score
2	Cairndale Hotel	87	8		1	Ian McIntyre	110
3	Octoputts Golfing Society	54	16		2	Andrew Hunter	100
4	Metrik Solutions	67	11		3	Raumond Marshall	99
5	Golden Girls	34	25		4	Heathhall Garden Centre	97
6	Peter Allen	89	5		5=	Peter Allen	89
7	BB Bodyrepairers	45	22		5=	Maureen McKerrow	89
8	Mathers Ltd	67	12		7	Alba Printers Ltd	88
9	Mike McCormick	76	10		8	Cairndale Hotel	87
10	Troqueer Garage	45	23		9	Retired Firefighters	77
11	Maureen McKerrow	89	6		10	Mike McCormick	76
12	Cameron McInnes	43	24		11=	Metrik Solutions	67
13	Tom Bell's Team	65	14		11=	Mathers Ltd	67
14	Ian McIntyre	110	1		13	Gordon Lawson	66
15	Heathhall Garden Centre	97	4		14	Tom Bell's Team	65
16	Emma Clark	23	26		15	Donald MacDonald	55
17	Donald MacDonald	55	15		16=	Octoputts Golfing Society	54
18	Gordon Lawson	66	13		16=	Jock Simpson	54
19	Retired Firefighters	77	9		16=	Margaret Hamilton	54
20	Alba Printers Ltd	88	7		16=	Queen of the South	54
21	Raumond Marshall	99	3		16=	Queen of the South seconds	54
22	Andrew Hunter	100	2		16=	Angus Robertson	54
23	Dfs Tool & Machy Repairs	47	21		22=	Les Martin	49
24	JD Engineering Ltd	48	20		22=	John Maxwell	49
25	Les Martin	49	18		24	JD Engineering Ltd	48
26	John Maxwell	49	19		25	Dfs Tool & Machy Repairs	47
27	Jock Simpson	54	17		26=	BB Bodyrepairers	45
28	Margaret Hamilton	54	18		26=	Troqueer Garage	45
29	Queen of the South	54	19		28	Cameron McInnes	43
30	Queen of the South seconds	54	20		29	Golden Girls	34
31	Angus Robertson	54	21		30	Emma Clark	23
Data

Cell Formulas
Range	Formula
F2:G31	F2	=SORT(A2:B31,2,-1)
C2:C31	C2	=RANK(B2,$B$2:$B$27,0)+COUNTIF($B$2:B2,B2)-1
E2	E2	=IF(G2=G3,1 & "=",1)
E3:E31	E3	=IF(G3=G2,LEFT(E2,LEN(E2)-1) & "=",IF(G3=G4,ROW()-2+1 & "=",ROW()-2+1))
Dynamic array formulas.

 

[Fluff]

Another option is

Fluff.xlsm
	A	B	C	D	E	F	G
1	Team Name	Score	Rank		Place	Team Name	Score
2	Cairndale Hotel	87	8		1	Ian McIntyre	110
3	Octoputts Golfing Society	54	16		2	Andrew Hunter	100
4	Metrik Solutions	67	11		3	Raumond Marshall	99
5	Golden Girls	34	29		4	Heathhall Garden Centre	97
6	Peter Allen	89	5		5 =	Peter Allen	89
7	BB Bodyrepairers	45	26		5 =	Maureen McKerrow	89
8	Mathers Ltd	67	12		7	Alba Printers Ltd	88
9	Mike McCormick	76	10		8	Cairndale Hotel	87
10	Troqueer Garage	45	27		9	Retired Firefighters	77
11	Maureen McKerrow	89	6		10	Mike McCormick	76
12	Cameron McInnes	43	28		11 =	Metrik Solutions	67
13	Tom Bell's Team	65	14		11 =	Mathers Ltd	67
14	Ian McIntyre	110	1		13	Gordon Lawson	66
15	Heathhall Garden Centre	97	4		14	Tom Bell's Team	65
16	Emma Clark	23	30		15	Donald MacDonald	55
17	Donald MacDonald	55	15		16 =	Octoputts Golfing Society	54
18	Gordon Lawson	66	13		16 =	Jock Simpson	54
19	Retired Firefighters	77	9		16 =	Margaret Hamilton	54
20	Alba Printers Ltd	88	7		16 =	Queen of the South	54
21	Raumond Marshall	99	3		16 =	Queen of the South seconds	54
22	Andrew Hunter	100	2		16 =	Angus Robertson	54
23	Dfs Tool & Machy Repairs	47	25		22 =	Les Martin	49
24	JD Engineering Ltd	48	24		22 =	John Maxwell	49
25	Les Martin	49	22		24	JD Engineering Ltd	48
26	John Maxwell	49	23		25	Dfs Tool & Machy Repairs	47
27	Jock Simpson	54	17		26 =	BB Bodyrepairers	45
28	Margaret Hamilton	54	18		26 =	Troqueer Garage	45
29	Queen of the South	54	19		28	Cameron McInnes	43
30	Queen of the South seconds	54	20		29	Golden Girls	34
31	Angus Robertson	54	21		30	Emma Clark	23
Data

Cell Formulas
Range	Formula
E2:G31	E2	=LET(r,RANK(B2:B31,B2:B31),SORT(HSTACK(r,A2:B31),3,-1))
C2:C31	C2	=RANK(B2,$B$2:$B$31,0)+COUNTIF($B$2:B2,B2)-1
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E:E	Expression	=COUNTIFS(E:E,E1)>1	text	NO

For the conditional formatting use a custom cell format of 0"="

 

[saitken]

Hi thanks for this. But I don't understand how to use it! Any chance you can attach a sheet with this that I can download?

 

[Fluff]

Just click the copy icon & paste it into a blank sheet

 

[saitken]

Brilliant. All sorted now. Thank you

 

[Fluff]

You're welcome & thanks for the feedback.