locating the top 5 number (in a col)

[owl527]

Hi,
I have two columns, A and B:
Column A: contains 3 types - Apple, Banana and Carrot
Column B: contains numbers

I want to summarize the data and find out the top 2 number of each
type. e.g.
Column A Column B
A 12
B 34
C 23
A 51
B 92
C 73
C 7
A 287
B 1
A 62
C 9

Part of the result would be:
A 287
62
B 92
34

how can I do that? please let me know. Thanks.

 

[Bernie Deitrick]

In cell C2 (assuming your table headers are in row 1), use the formula

=SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000>B2))+1

Change the 1000's to reflect your row count. Then copy down to match your data table.

Apply a filter to the entire table, and use the filter to show those values in column C that are
less than or equal to 2 (or 5, depending on which you actually meant). That will hide the smaller
values.

HTH,
Bernie
MS Excel MVP