# Formula required

#### mac_see

##### Active Member
Cell A1 contains Tom, A2 **** and A3 Harry. Cell B1 contains 3 B2 5 and B3 1. Values in Cell B1,B2,B3 are dynamic and changes according to values present in another worksheets. What I want is in D1 D2 D3 and E1 E2 E3 the same values should be copied but will be sorted in decending order as the value in B1 B2 B3 changes.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Dave Patton

##### Well-known Member
On 2002-10-20 13:41, mac_see wrote:
Cell A1 contains Tom, A2 **** and A3 Harry. Cell B1 contains 3 B2 5 and B3 1. Values in Cell B1,B2,B3 are dynamic and changes according to values present in another worksheets. What I want is in D1 D2 D3 and E1 E2 E3 the same values should be copied but will be sorted in decending order as the value in B1 B2 B3 changes.

in E1 =LARGE(\$B\$1:\$B\$3,1)
in E2 =LARGE(\$B\$1:\$B\$3,2)
in E3 =LARGE(\$B\$1:\$B\$3,3)

in D1

=INDEX(\$A\$1:\$A\$3,MATCH(E1,\$B\$1:\$B\$3,0))

Copy the formula down

#### mac_see

##### Active Member
Thanx buddy. It works !!!

#### mac_see

##### Active Member
No, it doesn't work if I put the same value for two person. For example, if I put 4000 for Tom and 3000 for **** and Harry, the reference D2 and D3 both shows ****'s name and Harry is disappeared???

##### MrExcel MVP

On 2002-10-30 08:26, mac_see wrote:
No, it doesn't work if I put the same value for two person. For example, if I put 4000 for Tom and 3000 for **** and Harry, the reference D2 and D3 both shows ****'s name and Harry is disappeared???

With the sample

{"Tom",3;"****",5;"Harry",1}

you provided in A1:B3...

In C1 enter and copy down:

=RANK(B1,\$B\$1:\$B\$3)+COUNTIF(\$B\$1:B1,B1)-1

In D1 enter & copy down:

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

EDIT. Changed the list separator ; to , (comma).
This message was edited by aladin akyurek on 2002-10-30 09:42

#### mac_see

##### Active Member
It gives an error:
The formula you typed contains an error.

I am having the following data
A1=Tom, A2=****, A3=Harry,
B1=1, B2=2, B3=3,

I need the sorted names in range D1:D3 and sorted values in range E1:E3

##### MrExcel MVP

On 2002-10-30 09:38, mac_see wrote:
It gives an error:
The formula you typed contains an error.

I am having the following data
A1=Tom, A2=****, A3=Harry,
B1=1, B2=2, B3=3,

I need the sorted names in range D1:D3 and sorted values in range E1:E3

Change the separator semi-colon to comma. For sorted values in E1:E3 use the same formula as in D1:D3, ranges adjusted.

#### mac_see

##### Active Member
I think therez a kind of misunderstanding here....... Let me restate my question.

I am having the following data
A1=Tom, A2=****, A3=Harry,
B1=100, B2=200, B3=300,

What I want is, I need to sort the names in range D1:D3 and sort their respective values in range E1:E3. As given in the above example, you should put a formula in range E1:E3 in such a way that, 300 will come on top (E1), 200 will come in (E2) and 300 in (E3). I need a second formula in range D1:D3 so that the corresponding name for that value will appear in the respective cell.

In your earlier example, the names were sorting properly but I am getting 1,2,3 instead of 100,200,300 or any value that I put in range B1:B3.

#### mac_see

##### Active Member
I think therez a kind of misunderstanding here....... Let me restate my question.

I am having the following data
A1=Tom, A2=****, A3=Harry,
B1=100, B2=200, B3=300,

What I want is, I need to sort the names in range D1:D3 and sort their respective values in range E1:E3. As given in the above example, you should put a formula in range E1:E3 in such a way that, 300 will come on top (E1), 200 will come in (E2) and 300 in (E3). I need a second formula in range D1:D3 so that the corresponding name for that value will appear in the respective cell.

In your earlier example, the names were sorting properly but I am getting 1,2,3 instead of 100,200,300 or any value that I put in range B1:B3.

##### MrExcel MVP
On 2002-10-30 10:08, mac_see wrote:
I think therez a kind of misunderstanding here....... Let me restate my question.

I am having the following data
A1=Tom, A2=****, A3=Harry,
B1=100, B2=200, B3=300,

What I want is, I need to sort the names in range D1:D3 and sort their respective values in range E1:E3. As given in the above example, you should put a formula in range E1:E3 in such a way that, 300 will come on top (E1), 200 will come in (E2) and 300 in (E3). I need a second formula in range D1:D3 so that the corresponding name for that value will appear in the respective cell.

In your earlier example, the names were sorting properly but I am getting 1,2,3 instead of 100,200,300 or any value that I put in range B1:B3.
Book1
ABCDEF
1Tom1003Harry300
2****2002****200
3Harry3001Tom100
4
Sheet1

Replies
5
Views
174
Replies
9
Views
198
Replies
3
Views
241
Replies
12
Views
110
Replies
1
Views
50

1,148,181
Messages
5,745,204
Members
423,933
Latest member
ankushmukherjee

### 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.

### Which adblocker are you using?

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

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