PDA

View Full Version : top 5 and top 10



chungtinhlak
03-09-2009, 09:27 AM
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
03-09-2009, 09:51 AM
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
03-09-2009, 10:56 AM
If you just want to highlight them
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

chungtinhlak
03-10-2009, 11:39 AM
what did I do wrong here?

Bob Phillips
03-10-2009, 12:56 PM
Your rankings are in column C not B, so change all the B cell references to C in the list formula.