Rank List of Names and Score, Breaking Ties

[Coachcasa]

Hi everyone. I have a list of names with a point total next to each. I'd like to take this list and then rank my top ten from the list by the largest points beneath this list. In my ranking, I want to list the names in column A and then their points in column B. I can get it to do what I want, ranking the name and points, however, what I can't figure out is how to list each name for ties instead of listing the same name over and over. I was using Index and Rank, but this won't break ties. Can anyone help me with this? Thank you.

 

[Cubist]

Hi,
Can you upload some sample data? Preferably using XL2BB.

 

[Fluff]

Is this for Excel or Google Sheets?

 

[Coachcasa]

Here is a copy of the spreadsheet I'm using. I'm using Excel and then copying it into Sheets so I can share it.

Is this for Excel or Google Sheets?

I'm working in Sheets to share it, but prefer working in Excel.

 

[Coachcasa]

Hi,
Can you upload some sample data? Preferably using XL2BB.

I'll be happy to share by spreadsheet, but I'm not sure how to upload the actual spreadsheet.

 

[Cubist]

Did you look at the linked provided in #2 for XL2BB? The other option is to share it via DropBox.

 

[Fluff]

In future if you need a formula for Google Sheets you should post in the General Discussion & Other Applications section of the board & make it clear what you are using.
I have done that for you this time.

 

[Eric W]

This works in Excel, I can't say about Google Sheets:

Book1
	A	B	C
1	First Name	Last Name	Varsity Pts
2	Al	Anderson	64
3	Betty	Boop	79
4	Cal	Cooledge	28
5	Deb	Dunne	46
6	Ed	Evans	66
7	Fae	Franks	35
8	Gil	Goodson	72
9	Hil	Harris	89
10	Igor	Ives	64
11	Jan	Jackson	53
12	Ken	Kent	21
13	Liv	Loeb	93
14	Mac	McMahon	8
15	Naomi	Nelson	89
16	Oliver	Onion	50
17	Pam	Peters	89
18	Quincy	Quark	99
19	Rebecca	Rains	38
20	Sam	Snead	33
21	Tom	Thomson	83
22	Ursula	Union	44
23	Vic	Vinge	20
24	Wendy	Wells	68
25	Xavier	Xman	8
26	Yolanda	Yen	96
27	Zac	Zieber	99
28
29
30
31
32
33
34
35
36
37
38	Top Ten Point Scorers:
39
40	Varsity
41	(name)		(pts)
42	Quincy	Quark	99
43	Zac	Zieber	99
44	Yolanda	Yen	96
45	Liv	Loeb	93
46	Hil	Harris	89
47	Naomi	Nelson	89
48	Pam	Peters	89
49	Tom	Thomson	83
50	Betty	Boop	79
51	Gil	Goodson	72
Sheet4

Cell Formulas
Range	Formula
A42:B51	A42	=INDEX(A$2:A$27,AGGREGATE(15,6,(ROW($A$2:$A$27)-ROW($A$2)+1)/($C$2:$C$27=$C42),COUNTIF($C$42:$C42,$C42)))
C42:C51	C42	=LARGE($C$2:$C$27,ROWS(C$42:C42))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:C34	Expression	=MOD(ROW(),2)=0	text	NO

 

[Fluff]

Another option that works in Sheets

=SORTN(A2:C31,10,,3,0)

 

[Coachcasa]

Another option that works in Sheets

=SORTN(A2:C31,10,,3,0)

Thank you. This worked great. What if I want to sort for columns that aren't next to each other? I also have a D column with JV points and would like to do my top ten for both varsity points and JV points.