Formula required

mac_see

Active Member
Joined
Oct 15, 2002
Messages
419
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Oct 15, 2002
Messages
419
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
Oct 15, 2002
Messages
419
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
Oct 15, 2002
Messages
419
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
Joined
Oct 15, 2002
Messages
419
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

Forum statistics

Threads
1,144,117
Messages
5,722,581
Members
422,447
Latest member
knopp

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