|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 19th, 2010, 02:40 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
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
April 19th, 2010, 02:51 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 19th, 2010, 02:58 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 19th, 2010, 04:06 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 19th, 2010, 05:32 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 19th, 2010, 07:12 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 19th, 2010, 07:14 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
April 19th, 2010, 09:42 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
