Hi everyone. I am the match secretary for a local snooker league. I have devised a spreadsheet which automatically sorts a league table when I enter in results into a table.
To achieve this, I had to combine the RANK and SUMPRODUCT functions (I found a solution online). The league table is sorted by PTS, then by WINS. However, errors occur in the auto sorted table when these two criteria don't separate two or more teams.
The only other way to separate them after this is to separate them alphabetically.
Is there anyway of doing this?
I'm a little confused... do you have a third criteria to separate tied players? Excel can sort on up to three columns, switching ascending/descending in each.
I had a similar problem sorting ice hockey tables.... but my problem was that I had more than three splitting criteria...
fortunately, the fields involved were numeric, so I was able to combine multiple fields into one using multiplication by 1000 each time.
For example;
to combine wins and goal difference.... where wins is the first tie breaker...
multiply wins by 1000 and add goal difference....
therefore... if each team has 2 wins, they get 2000 plus the goal difference.... so the sort effectively ignores the wins... if one team has more wins, that will dwarf any goal difference... so that gets ignored.
Hope that helps... if you can clarify your exact problem maybe I can help better.
Malcolm
Like I said, I'm using the RANK feature, which only works with numbers. The only thing separating teams are POINT and WINS.
I have decided to do it differently now using macros. I used the worksheet_activate() function to run a sorting macro I recorded.
I was hoping there was a way of doing it without using macros.
The function VLOOKUP can help it looks in the first column of an array and moves across the row to return the value of a cell
Kerschi
Have you considered redesigning it as an access database? Excel can (probably) do these things, but complex sorting like this is child's play for access.
