Sorting Tables

[Ozzy73]

What formula can I use to sort a numeric column in descending order & moving all its values with it in the rest of the table.


The E.G is for a football tournament, the team with the most points should be on top.


Group A P W D L GF GA GD Pts

South Africa 3 2 1 0 3 1 2 7

Uruguay 3 0 2 1 3 5 -2 2

Mexico 3 0 1 2 4 6 -2 1

France 3 2 0 1 9 7 2 6


Thanks

 

[Hui]

Add a new Column with the Rank function in it

=Rank(Pts, Range)


Then use a Lookup to lookup the Ranks in order 1 to 7 and retrieve the other columns

The rank Column will have to be before the Group Column to enable the use of Lookup

Also when using Rank, add a very small value to each of the Points

something like

=Rank(Points+(Row()/100000), Range)

That stops duplicate values


so top table will become

Rank Group A P W D L GF GA GD Pts

4 South Africa 3 2 1 0 3 1 2 7

2 Uruguay 3 0 2 1 3 5 -2 2

1 Mexico 3 0 1 2 4 6 -2 1

3 France 3 2 0 1 9 7 2 6


Then a table below that will be

Rank Group A P W D L GF GA GD Pts

1 Mexico 3 0 1 2 4 6 -2 1

2 Uruguay 3 0 2 1 3 5 -2 2

3 France 3 2 0 1 9 7 2 6

4 South Africa 3 2 1 0 3 1 2 7


Using Vlookup to retrieve the values from the top area

 

[Ozzy73]

GREAT! THANKS! MUCH APPRECIATED!!!