How to choose maximum value

[Omics]

Hi, I am wondering if anybody can help me with this. I need to take some
information from Table 1 to a new table which I am working on.
For example, for "apple" in column A, I would like to find the highest
number (30) in Column B and return the correspnonding
text in Column C (a2) to the new table.

Table 1
columnA ColumnB ColumnC
apple 2 a1
apple 30 a2
apple 15 a3
apple 6 a4
apple 12 a5
apple 9 a6
banana 3 b1
banana 10 b2
orange 2 o1
orange 1 o2
cherry 10 c1
peanut 20 p1
peanut 50 p2
peanut 25 p3
tomato 2 t1
tomato l t1
tomato 8 t3


Also, the new table has a lot of additionalinformation and therefore I can
not simple convert Table 1 to the new table.
The expected information needed to added to the new table is:

New Table
columnA ColumnB ColumnC
apple 30 a2
banana 10 b2
orange 2 o1
cherry 10 c1
peanut 50 p2
tomato 8 t3

Can I use the MAX function? Thanks lot !!

Omics

 

[T. Valko]

see your other post

 

[Jacob Skaria]

In sheet2 cell B1 apply the formula and copy down as required
=MAX(IF(Sheet1!A1:A100=A1,Sheet1!B1:B100))

In sheet2 cell C1 apply the formula and copy down as required
=INDEX(Sheet1!$C$1:$C$100,MATCH(1,(Sheet1!$A$1:$A$100=A1)*
(Sheet1!$B$1:$B$100=B1),0))

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

 

[Omics]

Hi Biff,

Thanks so much. When I was trying to post my question today I tried to
logged in the excel discussion group via firefox. I waited for couple of
hours and did not see any response to my question. That's why I logged in
again via IE. After 30 minutes, I got two response. However, right now I can
not see my other post. Could you please direct me to find the other post or
paste please the answer here. Thanks.

Omics

 

[T. Valko]

Here's your other post:

http://www.microsoft.com/communitie...&p=1&tid=c1d33d7e-bcd0-42b7-bd89-7c4efe45a39c

 

[Omics]

Hi Biff,

It worked. Thanks so much!

Omics

Here's your other post:

http://www.microsoft.com/communitie...&p=1&tid=c1d33d7e-bcd0-42b7-bd89-7c4efe45a39c

--
Biff
Microsoft Excel MVP





.

 

[Omics]

Hi Jacob,

Thanks so much for your help! I followed your instruction and it works well.

Omics

 

[T. Valko]

You're welcome. Thanks for the feedback!