Formula required

[mac_see]

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.

 

[Dave Patton]

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]

Thanx buddy. It works !!!

 

[mac_see]

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???

 

[Aladin Akyurek]

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]

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

 

[Aladin Akyurek]

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]

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]

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.

 

[Aladin Akyurek]

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
	A	B	C	D	E	F
1	Tom	100	3	Harry	300
2	****	200	2	****	200
3	Harry	300	1	Tom	100
4
Sheet1