Excel Autosort Table

[jwoolford]

I'm new here so I'm not sure if this is exactly where it's supposed t
go because I didn't want to post it anywhere else in case it was t
simple of a problem.

I have standings for a league (range is B4:J11). I would like the row
to change automatically to rank the teams, keeping each cell's value i
that team's row based on their points. This is what I'm looking to d
'
(http://www.euro2004.com/MultimediaFiles/Predictor/finals_forecast.zip
(Type the scores into the results boxes and the standings will chang
automatically).

If someone could shead some light on this for me that would be great

 

[Andy Brown]

I have standings for a league (range is B4:J11).

? ; the results range is actually J3:K26.

?? ; if I enter a result to the effect that England beat France (I may as
well to save waiting for it to happen), the Group B table in O9:W14
autosorts England to the top & France to the bottom.

It won't however sort alphabetically 2 teams who draw against each other.

???,
Andy

OT, do you know what happens this go-round where two teams finish the group
stage on equal points & GD & they drew against each other? Please don't tell
me they'll toss a coin.

 

[jwoolford]

I was using that file as an example.
If I sort the range by J in desending order I have the teams with th
highest point total at the top.

I want to know how to do this using a macro or a formula so it change
when I enter a game score, automatically sorting the table so I don'
have to do it manually

 

[CLR]

You could use a helper column with the names of the teams therein, and then
use this column as a second (or third) key on your sort..........

Vaya con Dios,
Chuck, CABGx3

 

[Andy Brown]

I was using that file as an example.

Apologies, my misunderstanding. Examination of the file (ignoring the fact
that some areas are password-protected) reveals that there are actually 3
sheets, one of which contains somewhere between dozens and 100s of formulas.
Such as

=IF(OR(OR($O$39 = $G3,$O$40 = $G3,$O$41 = $G3,$O$42 = $G3,$O$43 = $G3,$O$44
= $G3,$O$45 = $G3,$O$46 = $G3,$O$47 = $G3,O$48 = $G3,$O$49 = $G3,$O$50 =
$G3,$O$51 = $G3,$O$52 = $G3,$O$53 = $G3,$O$54 = $G3 ),OR($O$39 = $H3,$O$40 =
$H3,$O$41 = $H3,O$42 = $H3,$O$43 = $H3,$O$44 = $H3,$O$45 = $H3,$O$46 =
$H3,$O$47 = $H3,$O$48 = $H3,$O$49 = $H3,$O$50 = $H3,$O$51 = $H3,$O$52 =
$H3,$O$53 = $H3,$O$54 = $H3 )),"",General!J3)

On that basis I feel it's most unlikely that anyone would volunteer to
revamp this to meet your requirements gratis.

Regrets,
Andy

 

[Max]

Let's say you have in Sheet1,
cols A and B, data from row2 down

Team...Score
ABC....9
XYZ...10
DEF...12
LMN...10
etc

In an empty col, say col C:

Put in C2: =B2+ROW()/10000
Copy C2 down

Col C will function as an arbitrary tie-breaker column

In a new Sheet2
-------------------
With the same col headers in row1: Team...Score

Put in A2:

=OFFSET(Sheet1!$A$1,MATCH(LARGE(Sheet1!$C:$C,ROW()-1),Sheet1!$C:$C,0)-1,COLU
MN()-1)

Copy A2 across to B2,
then copy down as many rows as there is data in Sheet1
(or until #NUM! appears)

The above will produce the auto-sort of all the data rows in Sheet1
with teams ranked in descending* order by their points
in the "Score" col in Sheet1.

*To auto-sort in ascending order, just replace "LARGE" with "SMALL" in the
formula

 

[jwoolford]

Thank you, that works out

 

[Max]

You're welcome !
Glad to hear that

 

[Andy Brown]

Thank you, that works out.

Glad to hear it, although I can't see how. It's nothing like your original
"when I enter a game score, automatically sort the table", suggesting
something akin to -

Today A played B, C played D, E played F. A beat B, D beat C, E & F drew. On
plugging in the actual scores, formulas would assign points for a win to A &
D, points for a draw to E & F, points for a loss to B & C (presumably zero,
tho' in e.g. hockey I think you can get something for "only just
losing"(?)). Also, the formulas would work out ranking where points were
tied based on goals/tries/runs/baskets/whatever scored, and beyond that (if
necessary) alphabetically.

If what you have is all you need, & given "I want to know how to do this
using a macro or a formula", then you don't need formulas on another sheet,
you don't even need another sheet, you don't even need formulas on the
original sheet ; you just need a tiny piece of code in the sheet module, viz

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A:B").Sort Key1:=Range("B2"), Order1:=xlDescending, _
Key2:=Range("A2"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Enjoy.

Rgds,
Andy

 

[Max]

Nice piece of code, Andy !

Here's some ease-in steps for the OP, jwoolford,
to effect Andy's code (just in case ..)

.. you just need a tiny piece of code in the sheet module, viz ..

Right-click on the sheet tab [ e.g. : Sheet1's tab]
Select "View code"
Paste Andy's code into the whitespace on the right
(Clear the defaults in the whitespace first)

Press Alt+Q to get back to Excel
Now when any team's score is updated in Sheet1
(e.g.: new score keyed-in and Enter key pressed),
cols A and B will auto-sort ..

 

[jwoolford]

Thank you. That script works out perfectly.

What I did to automatically calculate the standings using only th
scores was like this.

I would enter the scores, and on a seperate sheet with a formula lik
this
=IF(ISBLANK(Results!F3),
",IF(Results!F3>Results!I3,1,IF(Results!F3=Results!I3,0,IF(Results!F3<Results!I3,0)))


This would deterime if they won, I had two other formula's similar t
calculate losses and ties. On another standings sheet it would add th
data up to find out the total number of wins, losses, ties. It woul
calculate games played from this, and from the wins and ties it woul
calculate the team's points. It would add the goals and goals agains
from the score sheet.
This would create a standing sheet with team's data in alphabetica
order, and that formula Max posted would sort it the same paper as th
scores

 

[Max]

Pleased to know everything
helped to work it out for you.

Thanks for the feedback!