Formula to sort returned values in new column - with name

JGuden

New Member
Joined
Sep 28, 2006
Messages
6
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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

New Member
Joined
Sep 28, 2006
Messages
6
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

New Member
Joined
Sep 28, 2006
Messages
6
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Convert the data area (A2:B6) into a list by means of Data|List|Create List.
Book3
ABCDEF
14
2XYrankXY
3Moe0.653Shemp1.87
4Curly0.124Larry0.89
5Larry0.892Moe0.65
6Shemp1.871Curly0.12
7Total4  
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)),"")
 

Forum statistics

Threads
1,141,097
Messages
5,704,313
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top