software

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: www.excelforum.com/member.php...oamp;userid=20916
View this thread: www.excelforum.com/showthread...hreadid=499656In cell C2 (assuming your table headers are in row 1), use the formula

=SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000gt;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 MVPquot;owl527quot; gt; wrote in message
news
gt;
gt; Hi,
gt; I have two columns, A and B:
gt; Column A: contains 3 types - Apple, Banana and Carrot
gt; Column B: contains numbers
gt;
gt; I want to summarize the data and find out the top 2 number of each
gt; type. e.g.
gt; Column A Column B
gt; A 12
gt; B 34
gt; C 23
gt; A 51
gt; B 92
gt; C 73
gt; C 7
gt; A 287
gt; B 1
gt; A 62
gt; C 9
gt;
gt; Part of the result would be:
gt; A 287
gt; 62
gt; B 92
gt; 34
gt;
gt; how can I do that? please let me know. Thanks.
gt;
gt;
gt; --
gt; owl527
gt; ------------------------------------------------------------------------
gt; owl527's Profile: www.excelforum.com/member.php...oamp;userid=20916
gt; View this thread: www.excelforum.com/showthread...hreadid=499656
gt;