Rank by 4 columns of numbers and text

[ScoobyCon]

I need help with my local football team database to display the ever changing football league table. An example of the table is as follows:

TEAM PLAYED WON DRAW LOST SCORED CONCEDED DIFFERENCE POINTS
D 6 4 2 0 12 3 9 14
C 6 3 2 1 10 5 5 11
B 6 3 2 1 10 5 5 11
A 6 3 1 2 11 13 -2 10


I need a formula that sorts the table by "POINTS", "DIFFERENCE", "SCORED", "TEAM"

The cells used are CB3:CJ23 and I have this formula so far which creates a RANK in cells CA4:CA23.

=RANK(CJ4,$CJ$4:$CJ$23,0)+IF(COUNTIF($CJ$4:$CJ$23,CJ4)>1,MATCH(CI4,LARGE(IF($CJ$4:$CJ$23=CJ4,$CI$4:$CI$23),ROW(INDIRECT("1:"&COUNTIF($CJ$4:$CJ$23,CJ4)))),0)-1)+IF(COUNTIF($CI$4:$CI$23,CI4)>1,MATCH(CG4,LARGE(IF($CI$4:$CI$23=CI4,$CG$4:$CG$23),ROW(INDIRECT("1:"&COUNTIF($CI$4:$CI$23,CI4)))),0)-1)

I just need it finishing to do a final sort on the "TEAM" column so that it is in alphabetical order. I have spent several days on this now looking on google and trying different things. Just to confirm, I will be using VLOOKUP function in cells AS4:BA23 to display the football league table based on the RANK value.

Any help would be much appreciated.

[ScoobyCon]

I've found a solution but not the way I really wanted...

I have number each team from 1 to 20 in alphabetical order in a new "ID" column.
And because the 3rd part of the formula above sorts the "Goals Scored" column I have added to the formula in these cells. So for example goals scored is now

=SUM(E4,G4,I4,K4,M4,O4,Q4,S4,U4,W4,Y4,AA4,AC4,AE4,AG4,AI4,AK4,AM4,AO4,D6,D8,D10,D12,D14,D16,D18,D20,D22,D24,D26,D28,D30,D32,D34,D36,D38,D40,D42)+(CA4/10000)

With the +(CA4/10000) giving each team a unique number to help sort the RANK into alphabetical order. So in the above example Team B and Team C have the same points, goal difference and goals scored. But the new part of the formula means Team B has scored 10.0002 goals and Team C has scored 10.0003 goals. Therefore sorting the teams alphabetically.

A formula that would be simpler would be more ideal if anyone could work it out.

Thanks in advance.

[Melvinrobb]

A sample workbook is probably needed in order to assist.
Why would you not at least replace the formula you have with

Please Login or Register  to view this content.

[micope21]

Hi

Welcome to the forum!

Formula =J2+I2/10^3+G2/10^6+(21-ROW(B1))/10^9
See the ROW? If say 10 teams? Change 11-ROW, 8 teams change 9-ROW and 12 teams 13-ROW.
You say got 20 teams = this 20 teams 21-ROW.
This will sort alphabetically order.

Please see the file.

[ScoobyCon]

Thanks micope21!!

Sorry for the late reply. Been busy organising the football league.
I've added rep for you!

Cheers. ScoobyCon.