Sorting

[MAX]

Team PLAY WIN DRAW LOSE GF GA GD POINTS
------------------------------------------------------------------------
Inter 26 18 6 2 47 21 26 60
lazio 26 16 5 5 42 21 21 53
Milan 26 14 6 6 43 27 16 48
lecce 26 14 4 8 38 25 13 46
Roma 26 12 9 5 36 25 11 45
----------------------------------------------------------------------
This is part of a table where the data is coming from a source showing below.

[G-1] - 14/09/08 - 28/01/09 - [G-20]
-----------------------------------------
0 - 1 Bologna Atalanta 1 - 0
2 - 0 Genoa Milan 1 - 1
2 - 1 Inter Catania 2 - 0
1 - 0 Lecce Udinese 1 - 2
-------------------------------------------
[G-1] means --- game No:1, where for example Bologna is at Home.
[G-20] means--- game No:20, means 2nd round, where Bologna is Away.
The figures beside the teams' name are the results.

I am not using any macros, just simple functions. The table is updated
whenever I put the results. Everything works perfect but I have to sort the
table in this manner.
The team with the more points ,then with the best GD, then with the best GF.

Will you please help me so when I put the results, the table will update
without the need to sort. I am working with Excel 2007.

Thanks.

 

[Shane Devenshire]

Hi,

I assume you want to sort the data at the top, first table? Assume the data
is in A1:I50. Then select the range and choose Data, Sort and in the Sort by
box pick Points, click Add Level and on the new row pick GD as the Sort by,
Add another level and pick GF for the Sort by. In each case choose the Order
you want.

If you really have ------ on row 2 then delete the row and add an underline
to row 1 or a bottom border.

 

[MAX]

Sorry I think that I didn't explain myself well.
When I post the results to the fixtures everything works perfect, such as
points, GD,GF, etc.... At this moment I am doing as you told me to sort the
table, but I want that as I enter the results, the table will sort itself
automatically.
Will you please tell me where can I post the whole file?
Thanks.

Hi,

I assume you want to sort the data at the top, first table? Assume the data
is in A1:I50. Then select the range and choose Data, Sort and in the Sort by
box pick Points, click Add Level and on the new row pick GD as the Sort by,
Add another level and pick GF for the Sort by. In each case choose the Order
you want.

If you really have ------ on row 2 then delete the row and add an underline
to row 1 or a bottom border.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Team PLAY WIN DRAW LOSE GF GA GD POINTS
------------------------------------------------------------------------
Inter 26 18 6 2 47 21 26 60
lazio 26 16 5 5 42 21 21 53
Milan 26 14 6 6 43 27 16 48
lecce 26 14 4 8 38 25 13 46
Roma 26 12 9 5 36 25 11 45
----------------------------------------------------------------------
This is part of a table where the data is coming from a source showing below.

[G-1] - 14/09/08 - 28/01/09 - [G-20]
-----------------------------------------
0 - 1 Bologna Atalanta 1 - 0
2 - 0 Genoa Milan 1 - 1
2 - 1 Inter Catania 2 - 0
1 - 0 Lecce Udinese 1 - 2
-------------------------------------------
[G-1] means --- game No:1, where for example Bologna is at Home.
[G-20] means--- game No:20, means 2nd round, where Bologna is Away.
The figures beside the teams' name are the results.

I am not using any macros, just simple functions. The table is updated
whenever I put the results. Everything works perfect but I have to sort the
table in this manner.
The team with the more points ,then with the best GD, then with the best GF.

Will you please help me so when I put the results, the table will update
without the need to sort. I am working with Excel 2007.

Thanks.

 

[Shane Devenshire]

Hi,

Regaring posting the whole file - I don't know what works best for that,
others here at the site could tell you.

There is no built-in feature to sort your data automatically. However, if
you want the sort to occur whenever you change or enter data in the important
columns -

1. First record the steps necessary to do the sort on you data. Then attach
the code to a Sheet_Change event.

Here is an example:

the range F2:I6 is my sample range where if an entry is changed the sort
reoccurs automatically. F2 because row 1 is titles and F is the first column
what something relevant could change. I6 you will need to adjust to include
as many rows as you want to trigger the auto sort. I2, F2 and H2 are the
Points, GF and GD columns. "A1:I6" is the entire sort range including the
titles. Again you will need to adjust that to fit your situation.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("F2:I6"))
If Not isect Is Nothing Then
With ActiveSheet
With .Sort.SortFields
.Clear
.Add Key:=Range("I2"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
.Add Key:=Range("H2"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
.Add Key:=Range("F2"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
End With
With .Sort
.SetRange Range("A1:I6")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End If
End Sub


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Sorry I think that I didn't explain myself well.
When I post the results to the fixtures everything works perfect, such as
points, GD,GF, etc.... At this moment I am doing as you told me to sort the
table, but I want that as I enter the results, the table will sort itself
automatically.
Will you please tell me where can I post the whole file?
Thanks.

Hi,

I assume you want to sort the data at the top, first table? Assume the data
is in A1:I50. Then select the range and choose Data, Sort and in the Sort by
box pick Points, click Add Level and on the new row pick GD as the Sort by,
Add another level and pick GF for the Sort by. In each case choose the Order
you want.

If you really have ------ on row 2 then delete the row and add an underline
to row 1 or a bottom border.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Team PLAY WIN DRAW LOSE GF GA GD POINTS
------------------------------------------------------------------------
Inter 26 18 6 2 47 21 26 60
lazio 26 16 5 5 42 21 21 53
Milan 26 14 6 6 43 27 16 48
lecce 26 14 4 8 38 25 13 46
Roma 26 12 9 5 36 25 11 45
----------------------------------------------------------------------
This is part of a table where the data is coming from a source showing below.

[G-1] - 14/09/08 - 28/01/09 - [G-20]
-----------------------------------------
0 - 1 Bologna Atalanta 1 - 0
2 - 0 Genoa Milan 1 - 1
2 - 1 Inter Catania 2 - 0
1 - 0 Lecce Udinese 1 - 2
-------------------------------------------
[G-1] means --- game No:1, where for example Bologna is at Home.
[G-20] means--- game No:20, means 2nd round, where Bologna is Away.
The figures beside the teams' name are the results.

I am not using any macros, just simple functions. The table is updated
whenever I put the results. Everything works perfect but I have to sort the
table in this manner.
The team with the more points ,then with the best GD, then with the best GF.

Will you please help me so when I put the results, the table will update
without the need to sort. I am working with Excel 2007.

Thanks.