i have 2 colums of cells K10 to K20 AND L10 to L20.

The K column holds team numbers (1 to 10)
The L column holds each teams accumlated score.

I am trying to sort the scores in to ascending order, yet keep the tea
numbers with the correct score.

For example

1 16
2 45
3 21
4 23
5 12, etc, etc before it is sorted in ascending dorder.

what i am trying to acheve is this:

2 45
4 23
3 21
1 16
5 12

I do hope somebody can help

--
Message posted from http://www.ExcelForum.com

Select both columns of data, and sort with the 2nd as the key.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"mikewild2000 " wrote in
message ...
i have 2 colums of cells K10 to K20 AND L10 to L20.

The K column holds team numbers (1 to 10)
The L column holds each teams accumlated score.

I am trying to sort the scores in to ascending order, yet keep the team
numbers with the correct score.

For example

1 16
2 45
3 21
4 23
5 12, etc, etc before it is sorted in ascending dorder.

what i am trying to acheve is this:

2 45
4 23
3 21
1 16
5 12

I do hope somebody can help.


---
Message posted from http://www.ExcelForum.com/

Pikus

Does this code go into ThisWorkbook or Sheet1
also under what category? Worksheet_selectionchange?

Sorry Bob your idea did not wor

--
Message posted from http://www.ExcelForum.com

You can put it in any number of places depending on when exactly yo
want it to run. It's small enough that it wouldn't disrupt things to
much if you put in ThisWorkbook in the SheetChange event so it run
after any change at all is made, but it would be more efficient if yo
did something like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A
Range)
If Target.Column = 11 And _
Target.Column <= 12 And _
Target.Row = 10 And _
Target.Row <= 20 Then
Set ws1 = Worksheets(1)
For x = 10 To 20
score = ws1.Cells(x, 12).Value
scoreRow = x
For y = x + 1 To 20
If ws1.Cells(y, 12).Value score Then
score = ws1.Cells(y, 12).Value
scoreRow = y
End If
Next y
If scoreRow < x Then
ws1.Cells(scoreRow, 11).Cut
ws1.Cells(x, 11).Insert
ws1.Cells(scoreRow, 12).Cut
ws1.Cells(x, 12).Insert
End If
End If
End Sub

Let me know how that works. - Piku

--
Message posted from http://www.ExcelForum.com

complie error

end if without block if


---
Message posted from http://www.ExcelForum.com/

Sorry! How sloppy of me!

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A
Range)
If Target.Column = 11 And _
Target.Column <= 12 And _
Target.Row = 10 And _
Target.Row <= 20 Then
Set ws1 = Worksheets(1)
For x = 10 To 20
score = ws1.Cells(x, 12).Value
scoreRow = x
For y = x + 1 To 20
If ws1.Cells(y, 12).Value score Then
score = ws1.Cells(y, 12).Value
scoreRow = y
End If
Next y
Next x
If scoreRow < x Then
ws1.Cells(scoreRow, 11).Cut
ws1.Cells(x, 11).Insert
ws1.Cells(scoreRow, 12).Cut
ws1.Cells(x, 12).Insert
End If
End If
End Sub

:

--
Message posted from http://www.ExcelForum.com

Set ws1 = Worksheets(1)
For x = 10 To 20
Score = ws1.Cells(x, 12).Value
scoreRow = x
For y = x + 1 To 20
If ws1.Cells(y, 12).Value Score Then
Score = ws1.Cells(y, 12).Value
scoreRow = y
End If
Next y
If scoreRow < x Then
ws1.Cells(scoreRow, 11).Cut
ws1.Cells(x, 11).Insert
ws1.Cells(scoreRow, 12).Cut
ws1.Cells(x, 12).Insert
End If
Next x

Hope you like. - Piku

--
Message posted from http://www.ExcelForum.com