Excel - Automatic Sorting

Asked By stephen1980 on 17-Dec-07 04:37 PM
I have a League Table which I use for keeping football results. After
each match I find it annoying to have to always sort the table to see
who is now top of the league.

Is it possible to make it automaticly change whenever a match is
played.

If it is possible I would prefer it to be done without me pressing any
buttons, but if that is the only option I can cope.


ww.rapidshare.de/files/38073810/FIFA_League_Table.xls.html

I can't post urls so can you add an extra w to that link^


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920




RagDyeR replied on 18-Dec-07 10:46 AM
This old post should help:

http://tinyurl.com/32rsyg

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I have a League Table which I use for keeping football results. After
each match I find it annoying to have to always sort the table to see
who is now top of the league.

Is it possible to make it automaticly change whenever a match is
played.

If it is possible I would prefer it to be done without me pressing any
buttons, but if that is the only option I can cope.


ww.rapidshare.de/files/38073810/FIFA_League_Table.xls.html

I can't post urls so can you add an extra w to that link^


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
Pete_UK replied on 21-Dec-07 03:43 AM
You can download two example workbooks from Bob Phillips' site here:

http://www.xldynamic.com/source/xld.LeagueTable.html

which allows you to maintain a football league - the examples are
based on the English Premier League, but can be adapted to suit.

Hope this helps.

Pete
stephen1980 replied on 20-Dec-07 03:45 PM
Thanks guys for the replies, I do appreciate it but I still don't have a
clue how to change the formula for my certain needs.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
RagDyeR replied on 21-Dec-07 10:34 AM
Why don't you describe your data configuration?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


Thanks guys for the replies, I do appreciate it but I still don't have a
clue how to change the formula for my certain needs.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
Pete_UK replied on 23-Dec-07 12:27 AM
Hi RD,

I can see Stephen's messages in your responses to him, but the
originals are not showing up in Google Groups - any idea whay that
should be?

Pete

-=AD--------------------
-=AD--------------------
in
RagDyer replied on 21-Dec-07 04:47 PM
Sorry ... don't have the slightest idea why!

I accidentally tripped over these groups (msnews.microsoft.com) about 6 or 7
years ago when I was learning XL, and have stuck with them and OE ever
since.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Hi RD,

I can see Stephen's messages in your responses to him, but the
originals are not showing up in Google Groups - any idea whay that
should be?

Pete
Pete_UK replied on 23-Dec-07 12:27 AM
Okay. Thanks.

Pete

7
-
stephen1980 replied on 02-Jan-08 11:31 AM
Is this possible to do or am I chasing a loswt cause?


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
RagDyeR replied on 02-Jan-08 12:18 PM
You were given links to possible solutions.

When you posted back that you were confused about being able to modify those
suggestions to match your data, I asked you to give us an idea of your data
configuration, so that perhaps "specific to you" help might be suggested.

I'm still here, awaiting your descriptions.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


Is this possible to do or am I chasing a loswt cause?


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
stephen1980 replied on 02-Jan-08 03:36 PM
Sorry if I was sounding impatient, I know you are only trying to help.
What do you mean by data configuration?


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
Ragdyer replied on 02-Jan-08 08:52 PM
Where are your teams names entered?
Do they go down a column, or across columns, in a single row?

Where do you enter the wins and losses?
When you say you want to auto sort, are you intending to award placement by
calculating win/loss percentage?
Where is this calculation stored and displayed in relation to the team
names?

In other words, the data that you want to automatically sort, how is it
set-up?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
stephen1980 replied on 03-Jan-08 07:18 AM
'RapidShare Webhosting + Webspace'
(http://www.rapidshare.de/files/38073810/FIFA_League_Table.xls.html)

That's a link to download it if you want.

I want to automatically sort the I column. The I column is the one
which contains points earned.

The one worry I have is that when the I column is sorted I want the
rows to change as well. For example if Stephen (A5) won a game then it
auto sorts, I would want all his data to be in the same row as "Stephen"


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
RagDyer replied on 03-Jan-08 03:35 PM
The range you're looking to "auto" sort is filled with formulas, so you
might move it from A1:J6 to Q1:Z6, and use it as the datalist for the
*presentation* range, which you'll place in the original location of A1:J6.
This presentation range will contain formulas which will "auto" sort your
calculated datalist range.

Important question ... will the PTS column ever contain TIES?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
stephen1980 replied on 03-Jan-08 08:34 PM
When I try to copy A1:J6 to Q1:Z6 it changes the formula which I don't
want it to do. Also what is the auto sort formula you are talking
about?

If PTS are tied it doesn't matter the auto sort is the important thing.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
RagDyeR replied on 04-Jan-08 11:46 AM
I *Didn't* say copy, I said *MOVE*.

Moving selected cells retains all references, without changing anything!

Select A1 to J6.
Hover the cursor around the border of the selection until it changes from a
white cross to a white arrow with 4 tiny black arrows at the tip.

Click and drag the selection to Q1:Z6.
You'll see *nothing* changed (except the location).

At this point, if you wish, you can place this "calculating" datalist at Q1
to Z6 in numerical or alphabetical order of teams or players, since the
to the PTS column.

Now, I asked about ties because it makes a *big* difference to the auto sort
formulas I'm going to give you.
By your answer, I'll assume there *is* the possibility of ties.


So now, copy the header row from Q1:Z1 over to A1:J1.

In I2 (PTS column), enter this formula:
=LARGE($Y$2:$Y$6,ROWS($1:1))
And copy down to I6.

In A2, enter this *array* formula:
=INDEX(Q$2:Q$6,LARGE(IF($Y$2:$Y$6=$I2,ROW($1:$5)),COUNTIF($I2:$I$6,$I2)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.  Also, CSE *must* be used when
revising the formula.

*AFTER* the CSE entry, copy the formula down to A6,
Then copy it across to Column H, so that you have the range of A2 to H6
filled with this formula.

Next, enter this *array* formula in J2:

=INDEX(Z$2:Z$6,SMALL(IF($Y$2:$Y$6=$I2,ROW($1:$5)),COUNTIF($I2:$I$6,$I2)))

*AFTER* the CSE entry, copy down to J6.

You should now have your "presentation" datalist set up, where any changes
in the PTS column at Y2 to Y6 will *automatically* change the *entire*
display at A2 to J6.

Ties in Y2:Y6 will display the team listed first in Q2:Q6 higher.
If you should want the later entered team in Q2:Q6 to be listed first for
ties, change the Large() function in all the array formulas to the Small()
function:

=INDEX(Q$2:Q$6,SMALL(IF($Y$2:$Y$6=$I2,ROW($1:$5)),COUNTIF($I2:$I$6,$I2)))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================




When I try to copy A1:J6 to Q1:Z6 it changes the formula which I don't
want it to do. Also what is the auto sort formula you are talking
about?

If PTS are tied it doesn't matter the auto sort is the important thing.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
stephen1980 replied on 05-Jan-08 07:23 AM
Thank you very much RagDyeR, it has worked and now automatticly sorts.
The only problem is that it the teams have changed.

For example where John was Munich he is now Barca, I think this was
probably my mistake though.

'RapidShare Webhosting + Webspace'
(http://rapidshare.de/files/38214216/FIFA_League_Table.xls.html)

^New version with Auto Sorting


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
RagDyeR replied on 05-Jan-08 11:57 AM
For some reason, you used the Large() function in the array formulas in A2
to H6,
*BUT* ... you used the Small() function in the array formulas in J2 to J6.

*ALL* of the array formulas must use the *same* function.

Change whichever you wish, but make the *all the same*!

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thank you very much RagDyeR, it has worked and now automatticly sorts.
The only problem is that it the teams have changed.

For example where John was Munich he is now Barca, I think this was
probably my mistake though.

'RapidShare Webhosting + Webspace'
(http://rapidshare.de/files/38214216/FIFA_League_Table.xls.html)

^New version with Auto Sorting


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
RagDyeR replied on 05-Jan-08 12:48 PM
I just re-read *my* post with the suggested formulas, and I see that  * I *
made the mistake! ! !

Sorry!

Just make sure all the array formulas use the same function, either Large()
or Small().
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

For some reason, you used the Large() function in the array formulas in A2
to H6,
*BUT* ... you used the Small() function in the array formulas in J2 to J6.

*ALL* of the array formulas must use the *same* function.

Change whichever you wish, but make the *all the same*!

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thank you very much RagDyeR, it has worked and now automatticly sorts.
The only problem is that it the teams have changed.

For example where John was Munich he is now Barca, I think this was
probably my mistake though.

'RapidShare Webhosting + Webspace'
(http://rapidshare.de/files/38214216/FIFA_League_Table.xls.html)

^New version with Auto Sorting


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
stephen1980 replied on 05-Jan-08 01:11 PM
That is it working perferctly, thanks again RagDyeR for all your help.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
RagDyeR replied on 05-Jan-08 03:38 PM
You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


That is it working perferctly, thanks again RagDyeR for all your help.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
stephen1980 replied on 05-Jan-08 06:18 PM
'RapidShare Webhosting + Webspace'
(http://rapidshare.de/files/38220450/FIFA_League_Table.xls.html)

RagDyeR I add in a few touches, like Displaying the winner.

There it is, the completed version. I have worked on this for September
and my freinds couldn't even understand the most basics IF commands.

Please if anyone wants to use that table feel free to ;D


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
RagDyeR replied on 05-Jan-08 07:51 PM
I forgot to mention before, BUT, you should *not* offer WBs that have macros
that are unnecessary for their display.

You should remove them before uploading!
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------




'RapidShare Webhosting + Webspace'
(http://rapidshare.de/files/38220450/FIFA_League_Table.xls.html)

RagDyeR I add in a few touches, like Displaying the winner.

There it is, the completed version. I have worked on this for September
and my freinds couldn't even understand the most basics IF commands.

Please if anyone wants to use that table feel free to ;D


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
stephen1980 replied on 05-Jan-08 08:32 PM
I have tried to remove them yet it still asks me everytime I open Excel.
Tools/Macro/Marco and there is none there?


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
Ragdyer replied on 05-Jan-08 11:03 PM
Check out this link to Debra Dalgleish's web page on the subject:

http://www.contextures.com/xlfaqMac.html#NoMacros

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
stephen1980 replied on 06-Jan-08 08:11 AM
I could find out how to delete it so I copied and pasted it to another
Excel Document and then I had to redo one or two of the formulas.

'RapidShare Webhosting + Webspace'
(http://rapidshare.de/files/38224545/FIFA_Leauge_Table.xls.html)

P.S. In case you are all wondering why this thread has 200+ views when
other only have 10 its because this thread was my homepage for a while.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
stephen1980 replied on 12-Jan-08 05:07 PM
Well, today was the first day the table went into use. The sorting
worked perfectly except, the GD, which I said wouldn't matter, has
changed the outcome of the table.

'RapidShare Webhosting + Webspace'
(http://rapidshare.de/files/38283674/FIFA_Leauge_Table.xls.html)

As you can see, John went throught by being 4th in the table, except it
should of been Paddy instead. Is it possible to soryt by PTS then by GD
when using auto sort.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
stephen1980 replied on 13-Jan-08 12:50 PM
Well, today was the first day the table went into use. The sorting
worked perfectly except, the GD, which I said wouldn't matter, has
changed the outcome of the table.

'RapidShare Webhosting + Webspace'
(http://rapidshare.de/files/38283674/FIFA_Leauge_Table.xls.html)

As you can see, John went throught by being 4th in the table, except it
should of been Paddy instead. Is it possible to soryt by PTS then by GD
when using auto sort.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
Ragdyer replied on 13-Jan-08 02:11 PM
AND ... what happens if the GD's are equal?

Any other "rules" you care to share?

It's nice to know the whole story before one starts attempting a solution!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
stephen1980 replied on 13-Jan-08 05:11 PM
Well if GD is equal it I would have to accepted that it isn't possible
to get what I was 100% of the time. Is is possible to sort by PTS, then
GD when auto sorting?


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
stephen1980 replied on 14-Jan-08 12:12 PM
Well if GD is equal then sort by GF, if GF is equal then that can't be
helped. I will just have to accept it.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
stephen1980 replied on 15-Jan-08 03:12 PM
Well if GD is equal then sort by GF, if GF is equal then that can't be
helped. I will just have to accept it.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
RagDyeR replied on 16-Jan-08 10:10 AM
Let me play with this for a while.


--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


Well if GD is equal then sort by GF, if GF is equal then that can't be
helped. I will just have to accept it.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
stephen1980 replied on 17-Jan-08 02:30 PM
Thanks mate, I had a look at it myself, but I just could not understand
what to do.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
stephen1980 replied on 18-Jan-08 04:11 PM
Thanks mate, I had a look at it myself, but I just could not understand
what to do.


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
stephen1980 replied on 26-Jan-08 10:02 AM
Any luck yet mate?


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920
RagDyeR replied on 26-Jan-08 02:05 PM
I would suggest that you post this to the programming group.

They would be able to give you some code to do everything you wish as far as
auto sorting 3 or 4 keys deep.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


Any luck yet mate?


--
stephen1980
------------------------------------------------------------------------
stephen1980's Profile: http://www.citrixguide.com1922
View this thread: http://www.citrixguide.com/showthread.php?t=20920