Sort columns by Rank

  • Thread starter Thread starter Mickey
  • Start date Start date
M

Mickey

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/British Championship/2003/cane2003.htm

Best Wishes,
Mickey
 
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)
 
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
 
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)
 
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
 
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
 
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)
 
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
 
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)
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel sort ranks text above numbers 2
Rank Formula 1
ranking values 3
Access or Excel 8
Ranking problem. 6
Ranking within subgroups 4
Rank after Sort 1
Problem sorting a range of columns using VBA 1

Back
Top