Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort columns by Rank
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort rank | Excel Worksheet Functions | |||
Rank and Sort | Excel Worksheet Functions | |||
Rank and Sort | Excel Worksheet Functions | |||
Rank then sort then pastelink | Excel Worksheet Functions | |||
Microsoft Excel Rank and Sort | Excel Discussion (Misc queries) |