top 5 and top 10

[chungtinhlak]

Hi, I have a work sheets with a list of items, i want to know the top 5 and top 10 item on this list, is there a way to do that with vba? I know how to do this in excel using countif but then i have to go to all kinds of sorting. I was wondering if there's an easier way.

thanks

[Bob Phillips]

Assuming that your nubers are in A2:A20.

Enter this in B1

=RANK(A2,$A$2:$A$20)+COUNTIF($A$2:$A2,A2)-1

and copy down.

Then in E1 say, enter this array formula

=IF(ISERROR(SMALL(IF($B$2:$B$20<6,ROW($A$2:$A$20),""),ROW($A1))),"",
INDEX($A$2:$A$20,SMALL(IF($B$2:$B$20<6,ROW($A$2:$A$20),""),ROW($A1))-ROW($A$2)+1))

and copy down.

For the top 10 change the <6 to <11

[mdmackillop]

If you just want to highlight them
[VBA]Sub TopVals()
Dim rng As Range, cel As Range, r as Long
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cel In rng
r = Application.Rank(cel, rng)
Select Case r
Case 1 To 5
cel.Interior.ColorIndex = 6
Case 6 To 10
cel.Interior.ColorIndex = 8
End Select
Next
End Sub
[/VBA]

[chungtinhlak]

what did I do wrong here?

[Bob Phillips]

Your rankings are in column C not B, so change all the B cell references to C in the list formula.