You are invited to Log in or Register a free Frihost Account!

Separating equally ranked ranges in excel

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.

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

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.
Related topics
Hide Part of Your Web Site from Yahoo!
Separating contents of MySQL row and put them in variables?
Working with Excel, PHP & MySQL. Any Ideas
Exporting to Excel and formatting textarea
Steelers Look to Defy Odds on Road Again
Excel macros
Google Ranking: How long does it take to get ranked?
from excel to php ?
[help] Pregunta sobre Microsoft Excel
Su 37 vs F 22!
What or Who Created God? Any ideas?
Excel SpreadSheet to Website
my site ranked low on google *make upto 200 frih$*
Importing email adresses to microsfot outlook, via excel lis
Reply to topic    Frihost Forum Index -> Computers -> Computer Problems and Support

© 2005-2011 Frihost, forums powered by phpBB.