How to rank or sort this?

[Luthius]

I have one table with the following values

Name	Points
Mary	12
John	16
Joseph	15
Hilary	16
Richard	13

I would like something like this:

Name	Points	Rank
Mary	12	1º
John	16	5º
Joseph	15	3º
Hilary	16	4º
Richard	13	2º

What I mean is that I will rank the values ​​using the data in column B, but if the column B has equal values​​, the formula will rank using as parameter the name in alphabetical order.

Luthius

 

[jasonb75]

No It isn't waste of time.
I need it with formula. That's the reason I asked.
My first question was solved, Now I want as I wrote using formula.

Your first question wasn't solved, you asked for equal values to be ranked alphabetically on Name, which none of the suggestions so far will do.

That would need an array solution such as

{=RANK(B2,$B$2:$B$6,1)+IF(COUNTIF($B$2:$B$6,B2)>1,COUNTIF($A$2:$A$6, "<"&A2)+SUM(IF(A2=$A$2:A2, 1, 0))-1)}

There was nothing in your follow up question to say that you wanted the results to a new location "with a formula", only that you wanted the results to be sorted into rank order.

Using any of the existing ranking formula,

=INDEX(A$2:A$6,MATCH($G2,$C$2:$C$6,0))

Where Rank # of the results table is in column G, drag right and down to fill the table.

 

[fromspenny]

One way is to name your table NPR and create a new worksheet and do a vlookup,

Excel Workbook
	A	B
1	Rank	Name
2	1	John
3	2	Hilary
4	3	Joseph
5	4	Richard
6	5	Mary
Sheet2

 

[Luthius]

Yes thank you. I was blinded by my own question, thanks for remember me.
Well, but in case to get the second result with just one formula?
I mean, I Have Rank # in G and in H a formula that give result without use other column with formula to get it?

 

[jasonb75]

This is how I did it, (all values set the same on this sample to verify alphabetical sorting)

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 37px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Name</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Points</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Rank</TD><TD></TD><TD>Rank</TD><TD>Name</TD><TD>Score</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Mary</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">16</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">4</TD><TD></TD><TD style="TEXT-ALIGN: right">1</TD><TD>Hilary</TD><TD style="TEXT-ALIGN: right">16</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">John</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">16</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">2</TD><TD></TD><TD style="TEXT-ALIGN: right">2</TD><TD>John</TD><TD style="TEXT-ALIGN: right">16</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Joseph</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">16</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3</TD><TD></TD><TD style="TEXT-ALIGN: right">3</TD><TD>Joseph</TD><TD style="TEXT-ALIGN: right">16</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Hilary</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">16</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1</TD><TD></TD><TD style="TEXT-ALIGN: right">4</TD><TD>Mary</TD><TD style="TEXT-ALIGN: right">16</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Richard</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">16</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">5</TD><TD></TD><TD style="TEXT-ALIGN: right">5</TD><TD>Richard</TD><TD style="TEXT-ALIGN: right">16</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2:C6</TD><TD>{=RANK(B2,$B$2:$B$6,1)+IF(COUNTIF($B$2:$B$6,B2)>1,COUNTIF($A$2:$A$6, "<"&A2)+SUM(IF(A2=$A$2:A2, 1, 0))-1)}</TD></TR><TR><TD>G2:G6</TD><TD>=ROW(A1)</TD></TR><TR><TD>H2:I6</TD><TD>=INDEX(A$2:A$6,MATCH($G2,$C$2:$C$6,0))</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!


</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

using original scores/points

<TABLE style="WIDTH: 316pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=421><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Points</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Rank</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=37>Rank</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Score</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Mary</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Mary</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>John</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Richard</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>13</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Joseph</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Joseph</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Hilary</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Hilary</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>16</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Richard</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>John</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>16</TD></TR></TBODY></TABLE>

 

[Luthius]

In this case (see table below) the values are uniques and I can make my report using the formula:

=INDEX($A$2:$A$6,MATCH(LARGE($B$2:$B$6,ROW()-1),$B$2:$B$6,0),1)

Name	Ponts
Mary	12
John	16
Joseph	15
Hilary[B]	9[/B]
Richard	13

But I can't use this formula (my formula) because In case of duplicated values in column B my formula doesn't work

I Know that your example solved my question, but now I just want in one formula and not using auxliar column.

 

[jasonb75]

Do you mean you want to do it as in my example, but without column C?

 

[Luthius]

Yes. Thats it...

 

[jasonb75]

I can't see that it's possible using formula to get the results, sorry.