Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting in Excel 2003
I have a golf society workbook to which I have made some changes, sadly
it has messed it up. I am trying to sort the following table which is A155 to E167. Round 1 - Results 18 Holes Back 9 Back 6 Back 3 Position H'cap Adj Graham Arundell 69 35 22 10 12 +2 John Clarke 70 35 22 10 11 +1 Steve Darby 71 36 23 10 10 Duncan Davison 72 36 23 10 9 Michael Exelby 73 37 24 10 8 Kelvin Goodacre 74 37 24 10 7 John Hart 75 38 24 10 6 Dave Hatfield 76 38 24 10 5 Dave McRorie 77 39 25 11 4 Iain Moffatt 78 39 25 11 3 John Proud 79 40 26 12 2 -1 Dave Salt 80 40 26 12 1 -2 Using the following macro Range("A156:E167").Select Selection.Sort Key1:=Range("E156"), Order1:=xlDescending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort Key1:=Range("B167"), Order1:=xlDescending, Key2:=Range( _ "C156"), Order2:=xlDescending, Key3:=Range("D156"), Order3:=xlDescending _ , Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom My problem appears to be related to the names in the table which are brought from another part of the workbook i.e. The name "Graham Arundell" come from A10 which in turn comes from Setup!d5. Please can you shed some light on why the table wont sort in these circumstances?? Many thanks John North Yorkshire UK |
#2
|
|||
|
|||
It works fine IF I copy the whole table and then paste special -
values! How could I automate this in the workbook please?? John aka Tonto |
#3
|
|||
|
|||
It works fine IF I copy the whole table and then paste special -
values! How could I automate this in the workbook please?? John aka Tonto |
#4
|
|||
|
|||
"Tonto" wrote in message oups.com...
I have a golf society workbook to which I have made some changes, sadly it has messed it up. I am trying to sort the following table which is A155 to E167. I am not shure you want this: Graham Arundell 69 35 22 10 12 2 John Clarke 70 35 22 10 11 1 Steve Darby 71 36 23 10 10 Duncan Davison 72 36 23 10 9 Michael Exelby 73 37 24 10 8 Kelvin Goodacre 74 37 24 10 7 John Hart 75 38 24 10 6 Dave Hatfield 76 38 24 10 5 Dave McRorie 77 39 25 11 4 Iain Moffatt 78 39 25 11 3 John Proud 79 40 26 12 2 -1 Dave Salt 80 40 26 12 1 -2 ordered this way: Dave Hatfield 76 38 24 10 5 Dave McRorie 77 39 25 11 4 Dave Salt 80 40 26 12 1 -2 Duncan Davison 72 36 23 10 9 Graham Arundell 69 35 22 10 12 2 Iain Moffatt 78 39 25 11 3 John Clarke 70 35 22 10 11 1 John Hart 75 38 24 10 6 John Proud 79 40 26 12 2 -1 Kelvin Goodacre 74 37 24 10 7 Michael Exelby 73 37 24 10 8 Steve Darby 71 36 23 10 10 Should it be the case, you need only-one-line macro: Sub SomeSort() ActiveSheet.[I45:O56].Sort _ Key1:=[I45], _ Order1:=xlAscending, _ Orientation:=xlSortColumns, _ MatchCase:=True End Sub Bruno |
#5
|
|||
|
|||
Bruno Campanini wrote: "Tonto" wrote in message oups.com... I have a golf society workbook to which I have made some changes, sadly it has messed it up. I am trying to sort the following table which is A155 to E167. I am not shure you want this: Graham Arundell 69 35 22 10 12 2 John Clarke 70 35 22 10 11 1 Steve Darby 71 36 23 10 10 Duncan Davison 72 36 23 10 9 Michael Exelby 73 37 24 10 8 Kelvin Goodacre 74 37 24 10 7 John Hart 75 38 24 10 6 Dave Hatfield 76 38 24 10 5 Dave McRorie 77 39 25 11 4 Iain Moffatt 78 39 25 11 3 John Proud 79 40 26 12 2 -1 Dave Salt 80 40 26 12 1 -2 ordered this way: Dave Hatfield 76 38 24 10 5 Dave McRorie 77 39 25 11 4 Dave Salt 80 40 26 12 1 -2 Duncan Davison 72 36 23 10 9 Graham Arundell 69 35 22 10 12 2 Iain Moffatt 78 39 25 11 3 John Clarke 70 35 22 10 11 1 John Hart 75 38 24 10 6 John Proud 79 40 26 12 2 -1 Kelvin Goodacre 74 37 24 10 7 Michael Exelby 73 37 24 10 8 Steve Darby 71 36 23 10 10 Should it be the case, you need only-one-line macro: Sub SomeSort() ActiveSheet.[I45:O56].Sort _ Key1:=[I45], _ Order1:=xlAscending, _ Orientation:=xlSortColumns, _ MatchCase:=True End Sub Bruno Hi I am trying to sort the names based on the first three columns of numbers. first column, second column then third column. Cheers John |
#6
|
|||
|
|||
"Tonto" wrote in message
oups.com... Hi I am trying to sort the names based on the first three columns of numbers. first column, second column then third column. Cheers John I don't see what sense there is in this cricumnstance, b.t.w. this does the job: Sub SomeSort() ActiveSheet.[I45:O56].Sort _ Key1:=[I45], _ Key2:=[J45], _ Key3:=[K45], _ Order1:=xlAscending, _ Orientation:=xlSortColumns, _ MatchCase:=True End Sub Bruno |
#7
|
|||
|
|||
Hi Bruno
Because the data in column one comes from somewhere else it doesnt work. John |
#8
|
|||
|
|||
"Tonto" wrote in message
oups.com... Hi Bruno Because the data in column one comes from somewhere else it doesnt work. John This is not a good reason. My data in column one come half from another computer on my lan, half from another (closed) file on my computer and the formula works! Something else must be wrong over there. Bruno |
#9
|
|||
|
|||
Hmm
Thanks Bruno I will have another close look at it and come back if I need to. Best wishes, Joh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel 2003 locks up after startup | Excel Discussion (Misc queries) | |||
Excel Re-calculation 2000 compared to 2003 | Excel Discussion (Misc queries) | |||
Problems using Excel 2000 to open/save file saved in Excel 2003 | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) |