Sort using formulas?

[mschatz2]

Hi all,

I've been working on this problem for several days now and have decided to give up and see if I can get some help. My situation is this: I have the standings from a soccer league, and I want to display them in the correct order (first place through twentieth place). I have attached a file here with all the information needed. The way to determine which team is in which place is as follows:
1. Most points (column C)
2. Greatest goal differential (column J)
3. Most goals scored (column H)
4. Alphabetical by team name (column B)

So in this case, TeamA and TeamB should really be in first and second place as shown here, but TeamD should really be in 3rd place because it has the same amount of points as TeamC but a greater goal differential.

It would be quite easy to accomplish this task using a sort on all these cells. I would simply include four levels in the sort as I outlined here above. The problem is that for my purposes I need to accomplish this using formulas. I have made various attempts at this using the RANK formula, but haven't been able to get my whole task done.

Thanks in advance for any help you can give me and please let me know if any of the above is unclear.

Thanks,
Mike

[JBeaucaire]

Kind of hard to explain, take a look.

[alansidman]

Have you considered a macro to do the manual sort?

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A2:J21").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("c2:c21") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("J2:J21") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H2:H21") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B21") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:J21")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Alan

[mschatz2]

Kind of hard to explain, take a look.

Thank you very much, Jerry, for taking the time to respond. Looking at your file, it appears that my problem is very close to being solved. The one problem that I see is that a couple rankings are still off. TeamA, for example, should be in first place because it is tied for having the most points (6) and it has the best goal differential (+6). The fact that it has less goals scored than TeamB is irrelevant as their tiebreaker was already solved by the goal differential. Likewise, TeamT should be in last place (20th place) because it has 0 points a a worse goal differential (-8) than any of the other 0 point teams.

Also, for the 4th and final tiebreaker, this should be team name in alphabetical order. I made this point perhaps more confusing than it needed to be by changing the real team names to these generic names (which were already in alphabetical order). I have put the real team names in the updated attached files to make more clear the issue of putting the teams in alphabetical order if the first 3 tiebreakers are equal (as is the case with Belgrano and Chicago in this example).

Thanks again for your help with this.

Mike

P.S. Thanks, Alansidman, for the suggestion, but I really need the solution to be in the form of formulas instead of a macro. Thanks again.

[JBeaucaire]

To be simple and accurate, since you have 3 sets of columns to rank on then you also need 3 progressive rank columns to solve it properly.

The "alphabetical" final step requires you actually put the table in alphabetical order, so I sorted the table properly so the final rank column works.

You can actually hide columns E:G so the only thing showing for "results" is the table in J:K.

[mschatz2]

To be simple and accurate, since you have 3 sets of columns to rank on then you also need 3 progressive rank columns to solve it properly.

The "alphabetical" final step requires you actually put the table in alphabetical order, so I sorted the table properly so the final rank column works.

You can actually hide columns E:G so the only think showing for "results" is the table in J:K.

Perfect! This solves my problem completely. Thank you very much for your help.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.