Sorting rows of data of varying lengths

[NDBC]

I think I've left the hardest question to last. See data below
H I J K L M
N O P.....
Name No lap1 lap2 lap3 lap4 lap5
lap6........

5 Name / Name 100 0:00:08
6 Name / Name 101 0:00:10 0:00:18
7 Name / Name 102 0:00:11 0:00:19 0:00:19 0:00:25 0:00:27
8 Name / Name 103 0:00:13 0:00:21 0:00:21 0:00:27 0:00:31
9 Name / Name 104 0:00:16 0:00:24 0:00:24 0:00:29 0:00:29 0:00:33
10 105
11 106
..
..
..

I need to sort the riders to determine places. The H,I.... and 5,6... are
cell references (and do not change) if that helps. The number of laps any one
rider may have completed in a race varies from 0 to unlimited. Riders are
ranked by number of laps completed and then finish time on their last lap if
number of laps is equal.

There are 2 ways to determine the number of rows for sorting that I can see.
1) determine the number of rider names till the first blank
2) just sort every rider number anyway even if unused. The maximum rider
number is 199 (row 104).

My hassle is determining the max number of columns for the range and then
sorting the rows based on what could realistically be 30 columns of times.

Thanks

 

[Joel]

Put in an auxilary column the number of columns with data. If lap 1 starts
in column J then in row 5

=countA(J55)

Then copy the formula down the column. Now sort this new column in
descending order. The new column will tell you the number of laps completed
since countA is the count of non blank cells.

 

[NDBC]

Thanks Joel

Now I know the max number of columns. So it's 100 rows by (in this case) 7
columns (including name and rider number). How to I sort that with code.

 

[Joel]

Copy the new column then paste back using Paste Special and select value to
replace the formula with a number. Then sort.

 

[NDBC]

I see two problems . One, I am trying to write code for a command button to
do this so that inexperienced users don't have to do anything but click the
button. The second is the method below doesn't sort the rows with the same
number of laps. There could be 5 competitors with 6 laps, 10 with 5 laps 4
with 4 laps etc.

 

[NDBC]

Sorry I wasn't clearer about wanting to do this with code. By sorting rows
with the same number of laps I mean sort rows with same number of laps in
ascending order based on the time it was when they finished their last lap.
(ie. fastest to slowest in each lap)

Thanks again for your help.

 

[Lars-Åke Aspelin]

I think I've left the hardest question to last. See data below
H I J K L M
N O P.....
Name No lap1 lap2 lap3 lap4 lap5
lap6........

5 Name / Name 100 0:00:08
6 Name / Name 101 0:00:10 0:00:18
7 Name / Name 102 0:00:11 0:00:19 0:00:19 0:00:25 0:00:27
8 Name / Name 103 0:00:13 0:00:21 0:00:21 0:00:27 0:00:31
9 Name / Name 104 0:00:16 0:00:24 0:00:24 0:00:29 0:00:29 0:00:33
10 105
11 106
.
.
.

I need to sort the riders to determine places. The H,I.... and 5,6... are
cell references (and do not change) if that helps. The number of laps any one
rider may have completed in a race varies from 0 to unlimited. Riders are
ranked by number of laps completed and then finish time on their last lap if
number of laps is equal.

There are 2 ways to determine the number of rows for sorting that I can see.
1) determine the number of rider names till the first blank
2) just sort every rider number anyway even if unused. The maximum rider
number is 199 (row 104).

My hassle is determining the max number of columns for the range and then
sorting the rows based on what could realistically be 30 columns of times.

Thanks

Sub sort_laps()
With ActiveWorkbook.Worksheets("Sheet2")
maxlap = .Range("K4").End(xlToRight).Column - .Range("J4").Column
For i = 1 To maxlap
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("J5").Offset(100, i) _
, SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:="0,1,2,3,4,5,6,7,8,9,"" """, _
DataOption:=xlSortNormal
.Sort.SetRange Range("H5:H104").Resize(, maxlap + 3)
.Sort.Header = xlNo
.Sort.MatchCase = False
.Sort.Orientation = xlTopToBottom
.Sort.SortMethod = xlPinYin
.Sort.Apply
Next i
End With
End Sub

The macro first finds the total number of laps, note that there must
be a blank cell to the right of e.g. "lap6" if there are 6 laps.
Then the data is sorted on each lap, starting from lap1.
The "CustomOrder" part makes the sorting put the blanks last.

Macro is only tested in Excel 2007.

Hope this helps / Lars-Åke

 

[NDBC]

I've got 2003 so didn't try it but it gave me an idea how it is done. Thanks

 

[Lars-Åke Aspelin]

It seems that CustomFormat was not actually needed.
I have rewritten the macro. It now works in Excel2002 and Excel2007.

Sub sort_laps()
laps = Range("K4").End(xlToRight).Column - Range("K4").Column + 1
rovs = 100
Range("H5:H5").Resize(rovs, laps + 3).Select
For i = 1 To laps
Selection.Sort Key1:=Range("J5").Offset(0, i), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next i
End Sub

Hope this works also for your version of Excel / Lars-Åke

 

[Joel]

You just need to sort on two columns. first the number of laps completed
descending order and then the last lap time in ascending order.

 

[NDBC]

For those tht may come across this problem. This is what I did. Process.
1) sort whole range by number of laps done (in column I)
2) count how many riders had done each amount of laps and sort them in sub
groups based on last lap time. (ie. if first 5 riders had done 6 laps then
sort the first 5 rows, if next 3 riders had done 5 laps sort the next 3 rows
etc).

My code for what it's worth (based on my level of experience it might be
rough but it works)

'A Grade
Worksheets("A Grade").Range("G5:IV104").Sort Key1:=Worksheets("A
Grade").Range("I5"), Order1:=xlDescending, _
MatchCase:=False, Orientation:=xlSortColumns, DataOption1:=xlSortNormal

MaxLap = Worksheets("A Grade").Range("I5")
Rdone = 0
If MaxLap > 0 Then
For l = MaxLap To 1
lapnums = Worksheets("A Grade").Range("I5:I104")
Onlap = Application.CountIf(lapnums, l)
If Onlap > 0 Then
tl = 5 + Rdone
br = 4 + Onlap + Rdone
Worksheets("A Grade").Range("G" & tl & ":IV" & br).Sort Key1:=Range("i" +
Onlap & tl), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlSortColumns,
DataOption1:=xlSortNormal
Rdone = Rdone + Onlap
End If
Next l
End If


Thanks for all the thoughts. It gave me a process.

 

[Lars-Åke Aspelin]

If you do just those two sorts, how will you then make sure that the
display order is correct for players that are not among those who have
completed the same number of laps, but not the maximum number of laps?

Lars-Åke

 

[Joel]

I cheanged you code a little to make it easier to follow. I used a
different method to determine the rows where the number of laps were the
same. I also sorted the entire row which is more efficient then sorting on
specific columns.


Sub test()

'A Grade
With Worksheets("A Grade")
.Range("A" & Rows.Count).End(xlUp).Row
.Rows("5:" & LastRow).Sort _
Key1:=.Range("I5"), _
Order1:=xlDescending, _
header:=xlNo

RowCount = 5
Start = RowCount
Do While .Range("I" & RowCount) <> 0
'check if number row completed is different
'between two row
If .Range("I" & RowCount) <> .Range("I" & (RowCount + 1)) Then
LastCol = .Cells(RowCount, Columns.Count).End(xlToLeft).Column
.Rows(Start & ":" & RowCount).Sort _
Key1:=.Cells(Start, LastCol), _
Order1:=xlDescending, _
header:=xlNo

Start = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End With

End Sub