How to automatically re-order a list

S

sandmania

Hi,
I have a worksheet with data in several rows. Column wise they consist of
names, points etc..

I am trying to setup a formula whereby the formula looks at the data in the
points columns and then re-orders the rows accordingly taking all the
information in the row with it, with the highest points total at the top and
lowest points total at the bottom.

Each points cell, has a formula in there already which pulls the points
total from other cells and combines them.
Hope you can help!
Thanks
 
B

Bernie Deitrick

sandmania,

Have your tried Data / Sort? Select the whole table, and the rows will stay together when you sort
based on one column.

If you want to create a second table that contains the same data as the first, but just sorted, then
it's a bit more complicated, but easily do-able.

Let us know....

HTH,
Bernie
MS Excel MVP
 
K

Krissie C

Forgive me if I'm oversimplyfing or not understanding what your asking for,
but couldn't you just use Data>Sort by Points column?
(Back up your data first, as you cannot undo a sort)
Good Luck...
 
S

sandmania

Hi,

Yeah I could use the sort by icon, but is there a way of doing it
automatically, so as soon as data is entered, it automatically calculates
which should go top etc....?
 
G

Gord Dibben

S

sandmania

Hi,

Thanks for you reply.
Tried the below, and to some effect it does work, but again it doesn't shift
the entire row up or down.

An example of what i am trying
name played won points
bob 10 10 30
john 9 9 27
billy 10 8 24

say if billy gets 31 points, i want all the row data to move to the top.
similiarly the other rows will move either up/down when something changes.

thanks for those links though. They may come in useful

Kind Regards
 
G

Gary Brown

If you have your original league table with 2 columns before, the first
used to rank the points + the row number/100 to allow for individuals
on joint points, then a second column ranking the first. Then in a new
table, where you have numbers down the left (for the number of players
in the league) and then put a vlookup for all of the columns in your
table. When you then alter the original tables points values. The new
table automatically recalculates.

See attached zipped spreadsheet


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4137 |
+-------------------------------------------------------------------+
 
S

sandmania

Hi,

Thanks for that info...I will give that a go. The link you specified below
doesnt seem to work.

kind regards
 
S

sandmania

Thats absolutely brilliant....just what I was trying to do. Much appreciated.

thankyou
Kind Regards
 
Top