Sorting

S

Soz

Who can help please?
I am running a football league for a group of friends. I have a table which
shows how many points each one has accumulated. I use the "range" function to
show which position each person has in the league. What I would like to do is
to have on a separate table the same list but this time I would like it to
show their names just like a normal league table, ie. most points at top,
least at bottom.
I have 3 columns, Column A has a list of names, Column B shows their
position and Column C has their points. At the moment we are 30 people.
Can someone help to make a proper table.
Hope so.
Best regards
Soz
 
S

Soz

Hello and many thanks for your help.
I see how it works but would it be possible to tell me how to set up the
macro so I can incorporate it into the spreadsheet which I am currently using.
Again many thanks
Soz
 
M

moi

The macro has 3 parts:
- a class module, there an event handler is declared
- a module, this one has an auto_opener, to activate/install the event
handler
- a piece of code in Sheet1, which actally is the event handler that
triggers any change in the workbook
this last one calls a sort macro which needs to be put in a 'normal module',
not in a class

Just check out help for 'events' and you'll see that you can do a lot more,
BeforeSave, BeforeClose, ActivateSheet - those are all events that you can
make Excel respond to in your own way.
 
S

Soz

Hello and thanks for the quick reply.
Sounds complicated for me but I will definitely try it. I have to sort it
out by next week as have to send everything out then. Thanks again.
Soz
 
R

Roger Govier

Hi

Whilst the solution presented by "moi" is elegant and works automatically,
if you are concerned about being able to achieve the necessary steps you
could carry out your task in a simplified way by running a macro to do the
sorting as and when you wished.

In your second sheet, your sorted list, you say that column B is your
ranking.
In cell B2 enter
=ROW()-1
copy down through B3:B31

Now, switch on the macro recorder Tools>Macro>Record before you select the
range A1:C31, then Data>Sort select Column C as the sort column and choose
Descending. Then switch off the macro recorder.

Whenever you want to produce your sorted list, just run the macro.
 
M

Martin P

This is if you do not want to use a macro.
With your information for the 30 players in cells A1 to A30, enter the
following.
In cell D1:
=C1+ROW(C1)/1000
In cell E1:
=RANK($D1,$D$1:$D$30)
In cell F1:
=ROW(A1)
In cell G1:
=A1
In cell H1:
=VLOOKUP($K1,$F$1:$G$30,2)
In cell I1:
=SUMPRODUCT($B$1:$B$30,--($F$1:$F$30=$K1))
In cell J1:
=SUMPRODUCT($C$1:$C$30,--($F$1:$F$30=K1))
In cell K1:
=SUMPRODUCT($F$1:$F$30,--($E$1:$E$30=F1))
Copy down.
Range H1:J30 gives the information in the order you need.
 
Top