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.


--
owl527
------------------------------------------------------------------------
owl527's Profile: http://www.excelforum.com/member.php...o&userid=20916
View this thread: http://www.excelforum.com/showthread...hreadid=499656

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

=SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000B2))+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


"owl527" wrote in message
...

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.


--
owl527
------------------------------------------------------------------------
owl527's Profile: http://www.excelforum.com/member.php...o&userid=20916
View this thread: http://www.excelforum.com/showthread...hreadid=499656