Rank (but not returning same value)
 

I have a range of data that I input into Sheet 1, range A1:B11:

Name Score
Allan 10
Jane 9
Gary 7
Steven 5
Robert 10
June 7
Sheila 8
Diane 4
Laura 9
Carolyn 5

And I want to be able to return, in an order, the following data (displayed
in Sheet 2, Range A1:C11):

Rank Name Score
1 Allan 10
1 Robert 10
3 Jane 9
3 Laura 9
5 Sheila 8
6 Gary 7
6 June 7
8 Steven 5
8 Carolyn 5
10 Diane 4

What formulae would I put into cells A2, B2 and C2 in Sheet 2 that I could
then copy and paste down to cells A11, B11 and C11 respectively that would
allow me to return the data as I intend?

Regards

Rank (but not returning same value)
 

http://www.cpearson.com/excel/rank.aspx

Chip Pearson, has in on the above link, try iot.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Gary T" escreveu:

I have a range of data that I input into Sheet 1, range A1:B11:

Name Score
Allan 10
Jane 9
Gary 7
Steven 5
Robert 10
June 7
Sheila 8
Diane 4
Laura 9
Carolyn 5

And I want to be able to return, in an order, the following data (displayed
in Sheet 2, Range A1:C11):

Rank Name Score
1 Allan 10
1 Robert 10
3 Jane 9
3 Laura 9
5 Sheila 8
6 Gary 7
6 June 7
8 Steven 5
8 Carolyn 5
10 Diane 4

What formulae would I put into cells A2, B2 and C2 in Sheet 2 that I could
then copy and paste down to cells A11, B11 and C11 respectively that would
allow me to return the data as I intend?

Regards

Rank (but not returning same value)
 

Try this:

Defined named ranges:

Name
Refers to: =Sheet1!$A$2:$A$11

Score
Refers to: =Sheet1!$B$2:$B$11

On Sheet2 enter these formulas:

A2:
=RANK(C2,C$2:C$11)

B2: (array formula**)
=INDEX(Name,MATCH(LARGE(Score-ROW(Score)/10^10,ROWS(B$2:B2)),Score-ROW(Score)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

C2:
=LARGE(Score,ROWS(C$2:C2))

Select A2, B2 and C2 and copy down as needed.


--
Biff
Microsoft Excel MVP


"Gary T" wrote in message
...
I have a range of data that I input into Sheet 1, range A1:B11:

Name Score
Allan 10
Jane 9
Gary 7
Steven 5
Robert 10
June 7
Sheila 8
Diane 4
Laura 9
Carolyn 5

And I want to be able to return, in an order, the following data
(displayed
in Sheet 2, Range A1:C11):

Rank Name Score
1 Allan 10
1 Robert 10
3 Jane 9
3 Laura 9
5 Sheila 8
6 Gary 7
6 June 7
8 Steven 5
8 Carolyn 5
10 Diane 4

What formulae would I put into cells A2, B2 and C2 in Sheet 2 that I could
then copy and paste down to cells A11, B11 and C11 respectively that would
allow me to return the data as I intend?

Regards

Rank (but not returning same value)
 

Another play using non-array formulas which also gets you there ..

Source table as posted is assumed within Sheet1's A1:B11

In Sheet2,

Put in A2:
=IF(ROWS($1:1)COUNT($D:$D),"",RANK(C2,INDIRECT("C 2:C"&COUNT(D:D)+1)))

Put in B2:
=IF(ROWS($1:1)COUNT($D:$D),"",INDEX(Sheet1!A:A,MA TCH(LARGE($D:$D,ROWS($1:1)),$D:$D,0)))
Copy B2 to C2

Put in D2:
=IF(Sheet1!B2="","",Sheet1!B2-ROW()/10^10)
Leave D1 blank

Select A2:D2, copy down to cover the max expected extent of source data in
Sheet1. Hide away col D. Cols A to C will return the results that you seek.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gary T" wrote:
I have a range of data that I input into Sheet 1, range A1:B11:

Name Score
Allan 10
Jane 9
Gary 7
Steven 5
Robert 10
June 7
Sheila 8
Diane 4
Laura 9
Carolyn 5

And I want to be able to return, in an order, the following data (displayed
in Sheet 2, Range A1:C11):

Rank Name Score
1 Allan 10
1 Robert 10
3 Jane 9
3 Laura 9
5 Sheila 8
6 Gary 7
6 June 7
8 Steven 5
8 Carolyn 5
10 Diane 4

What formulae would I put into cells A2, B2 and C2 in Sheet 2 that I could
then copy and paste down to cells A11, B11 and C11 respectively that would
allow me to return the data as I intend?

Regards