Formula to sort returned values in new column - with name

[JGuden]

Hi everyone,

I'm using XL03 with XP. I have a formula that calculates a response rate in a one column based on looking up an objects name in a corresponding column. (example below of current results)

A B
Moe .65
Curly .12
Larry .89
Shemp 1.87

I am looking to create a formula/section in my sheet that returns the values in column B in descending order with the corresponding value of column A in a separate column...as in:

D E
Shemp 1.87
Larry .89
Moe .65
Curly .12

In reality column A & B potentially occupy hunderds of rows. Any help is greatly appreciated. Thanks.

 

[onlyadrafter]

Hello,

In C1 enter

=RANK(B1,$B$1:$B$4)

in D1 enter

=INDEX($A$1:$A$4,MATCH(ROW(),$C$1:$C$4,0))

and in E1 enter

=VLOOKUP(D1,$A$1:$B$4,2,0)

change refs as required.

 

[JGuden]

When using the example I provided your solution works. When I apply it to my actual spreadsheet I am having some problems. Column D returns the name value is descending order but only does so beginning with rank 21. the data being used for this in my spreadsheet begins in row 21 so my initial reaction is that when it matches ROW and the starting list of data is in row 21 it begins by looking for the rank of 21. Where would I modify the equation you provided to have it return the list name based on the rank number 1? My data is in A21:B85.

 

[JGuden]

RO() - 20

While 'm sure this is not the most technically sound answer, entering:

=INDEX($A$21:$A$85,MATCH(ROW()-20,$E$21:$E$85,0))

Returns the desired result.


thanks for the help,

Cheers.

-J

 

[Aladin Akyurek]

Convert the data area (A2:B6) into a list by means of Data|List|Create List.

Book3
	A	B	C	D	E	F
1					4
2	X	Y	rank		X	Y
3	Moe	0.65	3		Shemp	1.87
4	Curly	0.12	4		Larry	0.89
5	Larry	0.89	2		Moe	0.65
6	Shemp	1.87	1		Curly	0.12
7	Total		4
8
Sheet1

Then 2 options:

1] Sort the list on Y in descending order.

2]

C2: Rank

C3, copied down:

=RANK(B3,$B$3:$B$6)+COUNTIF($B$3:B3,B3)-1

E1:

=MAX(C3:C6)

E3, copied across and down:

=IF(ROWS(E$3:E3)<=$E$1,INDEX(A$3:A$6,MATCH(ROWS(E$3:E3),$C$3:$C$6,0)),"")