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

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

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

"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

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

"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

Hi Bruno

Because the data in column one comes from somewhere else it doesnt
work.

John

"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

Hmm

Thanks Bruno

I will have another close look at it and come back if I need to.

Best wishes,

Joh