Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default How to choose maximum value

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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How to choose maximum value

see your other post

--
Biff
Microsoft Excel MVP


"Omics" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default How to choose maximum value

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}"


--
Jacob (MVP - Excel)


"Omics" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default How to choose maximum value

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" wrote:

see your other post

--
Biff
Microsoft Excel MVP


"Omics" wrote in message
...
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



.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How to choose maximum value

Here's your other post:

http://www.microsoft.com/communities...9-7c4efe45a39c

--
Biff
Microsoft Excel MVP


"Omics" wrote in message
...
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" wrote:

see your other post

--
Biff
Microsoft Excel MVP


"Omics" wrote in message
...
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



.





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default How to choose maximum value

Hi Biff,

It worked. Thanks so much!

Omics

"T. Valko" wrote:

Here's your other post:

http://www.microsoft.com/communities...9-7c4efe45a39c

--
Biff
Microsoft Excel MVP


"Omics" wrote in message
...
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" wrote:

see your other post

--
Biff
Microsoft Excel MVP


"Omics" wrote in message
...
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



.



.

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default How to choose maximum value

Hi Jacob,

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

Omics

"Jacob Skaria" wrote:

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}"


--
Jacob (MVP - Excel)


"Omics" wrote:

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

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How to choose maximum value

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Omics" wrote in message
...
Hi Biff,

It worked. Thanks so much!

Omics

"T. Valko" wrote:

Here's your other post:

http://www.microsoft.com/communities...9-7c4efe45a39c

--
Biff
Microsoft Excel MVP


"Omics" wrote in message
...
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" wrote:

see your other post

--
Biff
Microsoft Excel MVP


"Omics" wrote in message
...
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



.



.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return Maximum from Column directly above Maximum in Row Code Numpty Charts and Charting in Excel 2 November 19th 08 08:29 AM
Choose the cells where there is a value firroo Excel Discussion (Misc queries) 3 August 16th 08 12:08 PM
choose. pierre Excel Discussion (Misc queries) 2 April 25th 08 10:04 PM
choose? pierre Excel Discussion (Misc queries) 2 April 24th 08 09:47 PM
Choose. Rodney New Users to Excel 2 May 2nd 05 04:59 AM


All times are GMT +1. The time now is 12:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"