Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting
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 "MAX" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting
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. "Shane Devenshire" wrote: 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 "MAX" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting
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 "MAX" wrote: 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. "Shane Devenshire" wrote: 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 "MAX" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic sorting (giving max and min) based on custom sorting lis | Excel Worksheet Functions | |||
Sorting VLookup vs Sorting SumProduct | Excel Discussion (Misc queries) | |||
Sorting: Sorting by the First Character | Excel Discussion (Misc queries) | |||
Sorting | New Users to Excel | |||
sorting | Excel Discussion (Misc queries) |