Home |
Search |
Today's Posts |
#1
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
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
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
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
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
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
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
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Maximum from Column directly above Maximum in Row | Charts and Charting in Excel | |||
Choose the cells where there is a value | Excel Discussion (Misc queries) | |||
choose. | Excel Discussion (Misc queries) | |||
choose? | Excel Discussion (Misc queries) | |||
Choose. | New Users to Excel |