How to clear the lowest 10 values from a list of 30 values

[fap87]

Suppose I have a database of 50 students. Each row starts with the nam
of the student and then marks of 30 quizes for each student. e.g.-

Jack- 10 7 8 5 10 9 8 7 3 6 4 7 3 ........
Millie- 2 3 10 9 8 2 7 1 2 9 2 0 7 9.......
Ryan- 4 5 7 8 9 10 2 4 5 7 8 9............
...............................................................
..............................................................
Now I want to count only best 20 quiz marks out of 30 quizes and sho
the cells containing 10 lowest values as blank.
e.g. if i want to clear lowest 5 values for jack-
Jack- 10 7 8 _ 10 9 8 7 _ _ _ 7 _ ....

Now how do I do this and how do I repeat it for all 50 students? Tha
is, how do I show only best 20 quiz marks out of 30 for each student?


please help
fap8

 

[Don Guillett]

try this
Sub ClearBottom5()
'Rows("12:14").Copy Rows(2)

Ln = 5
For r = 2 To 4 'lastrow
rln = Application.Small(Rows(r), Ln)
For i = 2 To Cells(r, Columns.Count).End(xlToLeft).Column
If Cells(r, i) <= rln Then Cells(r, i).ClearContents
Next i
Next r
End Sub

 

[DrPhil]

Scripts are definately more powerful but a simple way that is more work is to
sort each student. The quizes would get out of order but if you only cared
about the total it would put all the quizes in desending or ascending order
and you could sum the best 20 for each. There may be shortcuts to repeating
sorting I don't know about. You dont want to sort them all together or it
may apply the order for one to all the others.

try this
Sub ClearBottom5()
'Rows("12:14").Copy Rows(2)

Ln = 5
For r = 2 To 4 'lastrow
rln = Application.Small(Rows(r), Ln)
For i = 2 To Cells(r, Columns.Count).End(xlToLeft).Column
If Cells(r, i) <= rln Then Cells(r, i).ClearContents
Next i
Next r
End Sub

 

[Jerry W. Lewis]

A third approach would be to use the LARGE function to to report the hightest
20 grades. Like the sorting suggestion, this would just give you the highest
scores without reference to which exams they came from.

Jerry