Auto sort a league table

Scunnered

New Member
Joined
Jul 29, 2007
Messages
2
I am in need of assistance to automatically sort a league table for a wappenschaw competition.

I record the individuals scores on the basis of :

Points Won
Shots For
Shots Against

on rows 1, 2 & 3 repeating the sequence for all the participants over a 22 week series. To bring this into a concentrated listing I have then entered the data in an alpha sort with the players name in Col A. Played in B, Points in C Shots in D and Rank in E on a scores tab.

Finally I wish to automatically sort the final rankings of the players descending from the player with the highest number of points. It is here that I struggle and need your assistance. I wish to display the information as Player in Col A, Played in Col C, Shots in Col G and Points in col H. Cols D E & F will show the numbers of wins, draws and losses and dont figure over all in the calculations.

I am looking for a formula to auto sort on the basis of col H followed by col G and all other related data across cols A to F. Currently the range of rows are No 3 to 57.

It would be appreciated if I could be provided by an early solution as things are getting a bit messy and I would much prefer to provide a properly sorted table for the club.

Thanking you in anticipation of your kind assistance
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi scunnered:

Welcome to MrExcel Board!

Please post a few rows of your sample data; your expected result; logic or explanation as necessary ... so we can see how your data is laid out, and what you are trying to accomplish.
 

Scunnered

New Member
Joined
Jul 29, 2007
Messages
2
auto sort a league table

Yogi Anand

Many thanks for your response.

I currently have an alpha sort of members with scores registered against each member which I wish to auto sort

My table looks like this at the moment

PLAYER Played W D L Shots Points

ANDERSON, A 9 6 1 2 54 13
ANDERSON, J 8 2 0 6 -35 4
BARCLAY, A 9 5 1 3 23 11
BERRY, J 10 5 0 5 -8 10
BOGLE, R 10 4 0 6 -13 8
BROWN, K 10 8 0 2 71 16
BROWN, R 5 5 0 0 39 10
BRYSON, D 1 0 0 1 -1 0


What I would like to achieve is as formula to display as below:

BROWN, K 10 8 0 2 71 16
ANDERSON, A 9 6 1 2 54 13
BARCLAY, A 9 5 1 3 23 11
BROWN, R 5 5 0 0 39 10
BERRY, J 10 5 0 5 -8 10
BOGLE, R 10 4 0 6 -13 8
ANDERSON, J 8 2 0 6 -35 4
BRYSON, D 1 0 0 1 -1 0


Pasting this over the cols have got a bit out of line but I hope you can follow what I am looking for.

The data that I want comes from a sheet called SC and I was using an INDEX forumla to get things to work. Somehow the thing has been corrupted and no mater what I try I can't get back to basics.

It is this last step that is my problem and I would appreciate assistance in achieving my aim.

I trust this is clear enough for you to follow
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi scunnred:

As far as the basics go ... what I did was first I arranged the Points in descending order, then I extracted info in all the other related fields by matching it with the Points fied entry as illustrated in the following ...
Book1
ABCDEFGHIJKLMNO
1step2step1
2PLAYERPlayedWDLShotsPointsPLAYERPlayedWDLShotsPoints
3ANDERSON, A96125413BROWN, K108027116
4ANDERSON, J8206-354ANDERSON, A96125413
5BARCLAY, A95132311BARCLAY, A95132311
6BERRY, J10505-810BERRY, J10505-810
7BOGLE, R10406-138BOGLE, R10406-138
8BROWN, K108027116ANDERSON, J8206-354
9BROWN, R55003910BRYSON, D1001-10
10BRYSON, D1001-10BRYSON, D1001-10
Sheet2


Step1

array formula in cell O3 is ...

=MAX(IF($G$3:$G$10<O2,$G$3:$G$10))
this is then copied down

array formula in cell I3 is ...

=INDEX(A:A,MATCH($O3,$G:$G,0))
this is then copied to cell I3:N10

I hope this helps. This should get you started. You still have to deal with other issues ... such as what to do in case there is more than one player with the same number of points (such as 10 points in this case), and so on.

Good Luck and keep us posted with the progress on the project.
 

Forum statistics

Threads
1,181,657
Messages
5,931,265
Members
436,785
Latest member
KingGideon

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