Sort columns by Rank
 

Hi,
I compile a lot of tables for chess tournaments and am wondering if anyone
can help with the following -

The tables are updated after each result is received, the tables are then
posted to the web. At present the table rows are left 'as is' with the 1st
ranked player being highlighted.

I would like to order the table so that the rows are ranked by order of
total points. The row would then have to be assembled so that the match
pairings remained intact. Can anyone hep point me in the right direction to
write a macro to achieve this sorting please? A sample file can be seen at
http://www.bfcc-online.org.uk/Britis...3/cane2003.htm

Best Wishes,
Mickey

Sort columns by Rank
 

Apologies, the link I gave is the one table with no results as yet! Please
try this one -
http://www.bfcc-online.org.uk/Britis...3/cane2003.htm

I need to keep the black squares all in a neat diagonal for the table once
sorting is complete.

Mickey

Sort columns by Rank
 

Damm, I am asleep today! The link is -
http://www.bfcc-online.org.uk/Britis...3/cand2003.htm

Sort columns by Rank
 

Hi Mickey,

Here's a macro

Sub SortResults()

Range("N7").EntireColumn.Insert
Range("O8:O14").Copy
Range("N8").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
Range("A7:O14").Sort Key1:=Range("N8"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("N7").EntireColumn.Delete
End Sub

--

HTH

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

"Mickey" wrote in message
...
Hi,
I compile a lot of tables for chess tournaments and am wondering if anyone
can help with the following -

The tables are updated after each result is received, the tables are then
posted to the web. At present the table rows are left 'as is' with the
1st
ranked player being highlighted.

I would like to order the table so that the rows are ranked by order of
total points. The row would then have to be assembled so that the match
pairings remained intact. Can anyone hep point me in the right direction
to
write a macro to achieve this sorting please? A sample file can be seen
at
http://www.bfcc-online.org.uk/Britis...3/cane2003.htm

Best Wishes,
Mickey

Sort columns by Rank
 

Hi Bob,
Poole Harbour is beautiful, I always enjoyed my trips that way.

Thanks for the macro but unfortunately it doesn't appear to work. It does
sort the table but messes up the order of columns, hence player 2 who had
drawn with say player 5, is then shown as no result against the same player.
The points do not get ranked in order although most appear to be correct,
however the highest points end up at the bottom?

Perhaps sorting the table as desiredis not feasible?

Mickey

Sort columns by Rank
 

Mickey,

Actually the problem is with your formulae in the lower half of the table.
You need to convert them all to absolute references to make it okay. So for
instance the formula in F9
=IF(G24=1,0,IF(G24=0.5,0.5,IF(G24=0,1,"")))
should read
=IF($G$24=1,0,IF($G$24=0.5,0.5,IF($G$24=0,1,"")))

If you want to select all the cells and then run this code, it will convert
them all for you

Dim cell
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, xlA1,
xlA1, xlAbsolute)
End If
Next cell

--

HTH

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

"Mickey" wrote in message
...
Hi Bob,
Poole Harbour is beautiful, I always enjoyed my trips that way.

Thanks for the macro but unfortunately it doesn't appear to work. It does
sort the table but messes up the order of columns, hence player 2 who had
drawn with say player 5, is then shown as no result against the same
player.
The points do not get ranked in order although most appear to be correct,
however the highest points end up at the bottom?

Perhaps sorting the table as desiredis not feasible?

Mickey

Sort columns by Rank
 

Hi Bob,
Thanks again, I tried to run the absolute ref code but got a syntax error
from the line cell.Formula = Application.ConvertFormula(cell.Formula,
xlA1,
xlA1, xlAbsolute)

I will convert the formulae by hand and try the code again, I am sure it
will work. A massive thanks for your help, this could save me many hours of
work.

Mickey

Sort columns by Rank
 

Hi Bob,
I got the '$' entered manually, unfortunately the table still did not sort
correctly. The higher figures ended at the bottom and the columns were out
of sync.

Thanks for your help on this cold Sunday.
Mickey

Sort columns by Rank
 

Mickey,

I think I know what you want that is not happening, and that is for the
values in the top half of the grid to move to the correct place when the
names change. This is tricky as they are horizontally placed, whereas the
names and the sort is vertical.

I will try and work it out, there will be a way, so keep checking back for
a solution over the next couple of days.

--

HTH

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

"Mickey" wrote in message
...
Hi Bob,
I got the '$' entered manually, unfortunately the table still did not sort
correctly. The higher figures ended at the bottom and the columns were
out
of sync.

Thanks for your help on this cold Sunday.
Mickey

Sort columns by Rank
 

Hi Bob,
Many thanks for your continued patience. I know of one guy that has this
macro but he wasn't for sharing, hence my search for a soloution. If it
would help I am happy to send a file I use and to make a contribution to
cover your costs in time. The soloution would be very useful for years to
come so would be something worth paying for. Hope that doesn't offend but I
am keen to solve this one.

Best WIshes,
Mickey

Sort columns by Rank
 

HI Mickey,

I think I have basically cracked it (when I started this, I thought this
will be easy, 7 and a half hours later ...<g).

Here is the basic macro. Can you give it a try and see that it is working as
you expect. It needs to be made more generic, to cater for a variable
number of entries. I will start on that but you check it out for accuracy in
the meantime.

If you mail me direct with your workbook (bob . phillips @ tiscali . co.uk -
remove the spaces), I will add it to there. How does the macro get
triggered, a worksheet button, toolbar commandbutton, or what?

Anyway, here is the code - watch the wrap-around

Sub SortResults()
Dim i As Long
Dim j As Long
Dim iArray As Long
Dim aryData(55, 2)
Dim sRange As String
Dim nEntries As Long

'saved the results data
For i = 8 To 15
For j = 6 To 13
If i < j + 2 Then
aryData(iArray, 0) = Cells(i, "B").Value
aryData(iArray, 1) = Cells(7 + Cells(7, j).Value, "B").Value
aryData(iArray, 2) = Cells(i, j).Value
iArray = iArray + 1
End If
Next j
Next i

'now sort the data
Range("O7").EntireColumn.Insert
Range("P8:P15").Copy
Range("O8").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
Range("A7:P15").Sort Key1:=Range("O8"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("O7").EntireColumn.Delete

With Range("F8:M15")
.ClearContents
.Interior.ColorIndex = xlColorIndexNone
End With

'restore the data
For iArray = 0 To 55
i = Range("B8:B15").Find(aryData(iArray, 0)).Row
j = Range("B8:B15").Find(aryData(iArray, 1)).Row - 2
Cells(i, j).Value = aryData(iArray, 2)
Next iArray

For i = 8 To 15
For j = 6 To i - 2
If i = j + 2 Then
Cells(i, j).Interior.ColorIndex = 1
Else
sRange = "R" & j + 2 & "C" & i - 2
sRange = "=IF(" & sRange & "=1,0,IF(" & sRange &
"=0.5,0.5,IF(AND(LEN(" & sRange & ")0," & sRange & "=0),1,"""")))"
Cells(i, j).FormulaR1C1 = sRange
End If
Next j
Next i


End Sub


--

HTH

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

"Mickey" wrote in message
...
Hi Bob,
Many thanks for your continued patience. I know of one guy that has this
macro but he wasn't for sharing, hence my search for a soloution. If it
would help I am happy to send a file I use and to make a contribution to
cover your costs in time. The soloution would be very useful for years
to
come so would be something worth paying for. Hope that doesn't offend but
I
am keen to solve this one.

Best WIshes,
Mickey

Sort columns by Rank
 

oh and by the way, the guy that wouldn't share? It's not as though we are
talking about a ground-breaking new product here, it's just a useful macro.
Where are some people at?

Bob

"Bob Phillips" wrote in message
...
HI Mickey,

I think I have basically cracked it (when I started this, I thought this
will be easy, 7 and a half hours later ...<g).

Here is the basic macro. Can you give it a try and see that it is working
as
you expect. It needs to be made more generic, to cater for a variable
number of entries. I will start on that but you check it out for accuracy
in
the meantime.

If you mail me direct with your workbook (bob . phillips @ tiscali .
co.uk -
remove the spaces), I will add it to there. How does the macro get
triggered, a worksheet button, toolbar commandbutton, or what?