• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting Tables

Ozzy73

New Member
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

Excel Ninja
Staff member
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
 
Top