Auto sort a league table

[Scunnered]

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

 

[Yogi Anand]

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]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1									step2						step1
2	PLAYER	Played	W	D	L	Shots	Points		PLAYER	Played	W	D	L	Shots	Points
3	ANDERSON, A	9	6	1	2	54	13		BROWN, K	10	8	0	2	71	16
4	ANDERSON, J	8	2	0	6	-35	4		ANDERSON, A	9	6	1	2	54	13
5	BARCLAY, A	9	5	1	3	23	11		BARCLAY, A	9	5	1	3	23	11
6	BERRY, J	10	5	0	5	-8	10		BERRY, J	10	5	0	5	-8	10
7	BOGLE, R	10	4	0	6	-13	8		BOGLE, R	10	4	0	6	-13	8
8	BROWN, K	10	8	0	2	71	16		ANDERSON, J	8	2	0	6	-35	4
9	BROWN, R	5	5	0	0	39	10		BRYSON, D	1	0	0	1	-1	0
10	BRYSON, D	1	0	0	1	-1	0		BRYSON, D	1	0	0	1	-1	0
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.