Rearranging based upon value

[sophos707]

Hi!

I am wondering how I can enter a formula to display my cells in order, from lowest number to highest. But I also want the names to be pulled over...
For example:

A B
Car 5
Dog 20
Fun 2


How can I make, in column "D" a list of the names, and then values in order, so it would end up like this:

D E
Fun 2
Car 5
Dog 20

 

[Norie]

Couldn't you just goto Data>Sort...?

 

[sophos707]

Well, the problem is that I need to maintain the columns A and B without any changes... I just need new columns with that data, sorted differently.

DATA>Sort will only rearrange the ones I have, right?

 

[sophos707]

And I need the text titles as well, not just the numbers.

 

[sophos707]

/bump

Any ideas?

 

[BJungheim]

Using a helper column it's quite easy.

In C1:

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

In D1:

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

In E1:

=INDEX(B:B,MATCH(ROW(),$C:$C,0))

Copied down as needed.