| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highest Value
Hi,
If sheet1 has some data which is each material code has diff supplier rate now I want highest rate on sheet 2 against material code how I can do that with the formula? Example sheet1 M.code S.rate 1002 105 104 106 1003 110 102 1004 125 130 sheet2 M.code S.rate 1002 106 1003 110 1004 130 Regards Zafar |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highest Value
In Sheet2 with material code in cell A1
=SUMPRODUCT(MAX((Sheet1!$A$1:$A$100=A1)*Sheet1!$B$ 1:$B$100)) If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Hi, If sheet1 has some data which is each material code has diff supplier rate now I want highest rate on sheet 2 against material code how I can do that with the formula? Example sheet1 M.code S.rate 1002 105 104 106 1003 110 102 1004 125 130 sheet2 M.code S.rate 1002 106 1003 110 1004 130 Regards Zafar |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highest Value
Dear Jacob,
i applied same fomula but result is "0 " "Jacob Skaria" wrote: In Sheet2 with material code in cell A1 =SUMPRODUCT(MAX((Sheet1!$A$1:$A$100=A1)*Sheet1!$B$ 1:$B$100)) If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Hi, If sheet1 has some data which is each material code has diff supplier rate now I want highest rate on sheet 2 against material code how I can do that with the formula? Example sheet1 M.code S.rate 1002 105 104 106 1003 110 102 1004 125 130 sheet2 M.code S.rate 1002 106 1003 110 1004 130 Regards Zafar |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highest Value
In sheet1 fill the blanks with the material code
1. Select the data range in ColA (say A1:A100). 2. Press F5. From 'Goto window'Special from Options select 'Blanks' and hit OK 3. This will select all blanks. 4. Now press = (equal sign) 5. Then press Up Arrow to reference the cell just above 6. Now press Ctrl and Enter key together If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Dear Jacob, i applied same fomula but result is "0 " "Jacob Skaria" wrote: In Sheet2 with material code in cell A1 =SUMPRODUCT(MAX((Sheet1!$A$1:$A$100=A1)*Sheet1!$B$ 1:$B$100)) If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Hi, If sheet1 has some data which is each material code has diff supplier rate now I want highest rate on sheet 2 against material code how I can do that with the formula? Example sheet1 M.code S.rate 1002 105 104 106 1003 110 102 1004 125 130 sheet2 M.code S.rate 1002 106 1003 110 1004 130 Regards Zafar |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highest Value
Hello Jacob,
There could be more than one with the max value in sheet1, I think. I suggest to array-enter into Sheet2!C2: =MAX((Sheet1!$A$2:$A$100=A2)*Sheet1!$B$2:$B$100)) then copy down... Regards, Bernd |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highest Value
Probably will have to try with..with code in Sheet2 A2
=SUMPRODUCT(MAX((Sheet1!$A$2:$A$100=A2)*Sheet1!$B$ 2:$B$100)) OR =MAX(IF((Sheet1!A2:A100=A2),Sheet1!B2:B100)) 'array entered enter with CTRL+SHIFT+ENTER OR without filling the blanks in sheet1 try with the below formula =MAX(OFFSET(INDIRECT("Sheet1!A" & MATCH(A2,Sheet1!A:A,0)),0,1,IF(ISNA(MATCH(TRUE,IND EX(INDIRECT("Sheet1!A" & MATCH(A2,Sheet1!A:A,0)+1 & ":A1000")<"",),)),1000,MATCH(TRUE,INDEX(INDIRECT( "Sheet1!A" & MATCH(A2,Sheet1!A:A,0)+1 & ":A1000")<"",),)),1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: In sheet1 fill the blanks with the material code 1. Select the data range in ColA (say A1:A100). 2. Press F5. From 'Goto window'Special from Options select 'Blanks' and hit OK 3. This will select all blanks. 4. Now press = (equal sign) 5. Then press Up Arrow to reference the cell just above 6. Now press Ctrl and Enter key together If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Dear Jacob, i applied same fomula but result is "0 " "Jacob Skaria" wrote: In Sheet2 with material code in cell A1 =SUMPRODUCT(MAX((Sheet1!$A$1:$A$100=A1)*Sheet1!$B$ 1:$B$100)) If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Hi, If sheet1 has some data which is each material code has diff supplier rate now I want highest rate on sheet 2 against material code how I can do that with the formula? Example sheet1 M.code S.rate 1002 105 104 106 1003 110 102 1004 125 130 sheet2 M.code S.rate 1002 106 1003 110 1004 130 Regards Zafar |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highest Value
Sorry Bernd; i didnt get you..
"Bernd P" wrote: Hello Jacob, There could be more than one with the max value in sheet1, I think. I suggest to array-enter into Sheet2!C2: =MAX((Sheet1!$A$2:$A$100=A2)*Sheet1!$B$2:$B$100)) then copy down... Regards, Bernd |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highest Value
Dear Jacob
as per your advise i did the same and result is not found for your review. sheet 1 M.CODE Value 1002 101 1002 102 1002 103 1003 201 1003 202 1003 203 1004 301 1004 302 1004 304 Result on sheet 2 SUMPRODUCT(MAX(Sheet1!$A$1:$A$9=Sheet2!A1)*Sheet1! $B$1:$B$9) 1002 0 1003 0 1004 0 please help me Regards "Jacob Skaria" wrote: In sheet1 fill the blanks with the material code 1. Select the data range in ColA (say A1:A100). 2. Press F5. From 'Goto window'Special from Options select 'Blanks' and hit OK 3. This will select all blanks. 4. Now press = (equal sign) 5. Then press Up Arrow to reference the cell just above 6. Now press Ctrl and Enter key together If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Dear Jacob, i applied same fomula but result is "0 " "Jacob Skaria" wrote: In Sheet2 with material code in cell A1 =SUMPRODUCT(MAX((Sheet1!$A$1:$A$100=A1)*Sheet1!$B$ 1:$B$100)) If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Hi, If sheet1 has some data which is each material code has diff supplier rate now I want highest rate on sheet 2 against material code how I can do that with the formula? Example sheet1 M.code S.rate 1002 105 104 106 1003 110 102 1004 125 130 sheet2 M.code S.rate 1002 106 1003 110 1004 130 Regards Zafar |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highest Value
There is a syntax error in the formula you tried....Copy paste the formula
from my previous post...There are 3 solutions suggested in the same post...which you can try out... If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Dear Jacob as per your advise i did the same and result is not found for your review. sheet 1 M.CODE Value 1002 101 1002 102 1002 103 1003 201 1003 202 1003 203 1004 301 1004 302 1004 304 Result on sheet 2 SUMPRODUCT(MAX(Sheet1!$A$1:$A$9=Sheet2!A1)*Sheet1! $B$1:$B$9) 1002 0 1003 0 1004 0 please help me Regards "Jacob Skaria" wrote: In sheet1 fill the blanks with the material code 1. Select the data range in ColA (say A1:A100). 2. Press F5. From 'Goto window'Special from Options select 'Blanks' and hit OK 3. This will select all blanks. 4. Now press = (equal sign) 5. Then press Up Arrow to reference the cell just above 6. Now press Ctrl and Enter key together If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Dear Jacob, i applied same fomula but result is "0 " "Jacob Skaria" wrote: In Sheet2 with material code in cell A1 =SUMPRODUCT(MAX((Sheet1!$A$1:$A$100=A1)*Sheet1!$B$ 1:$B$100)) If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Hi, If sheet1 has some data which is each material code has diff supplier rate now I want highest rate on sheet 2 against material code how I can do that with the formula? Example sheet1 M.code S.rate 1002 105 104 106 1003 110 102 1004 125 130 sheet2 M.code S.rate 1002 106 1003 110 1004 130 Regards Zafar |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Highest Value
Hi Jacob Sharia
Thank you very much for help me and problem resolved with the following formula =MAX(IF((Sheet1!A2:A100=A2),Sheet1!B2:B100)) 'array entered enter with CTRL+SHIFT+ENTER thanks again Zafar "Jacob Skaria" wrote: Probably will have to try with..with code in Sheet2 A2 =SUMPRODUCT(MAX((Sheet1!$A$2:$A$100=A2)*Sheet1!$B$ 2:$B$100)) OR =MAX(IF((Sheet1!A2:A100=A2),Sheet1!B2:B100)) 'array entered enter with CTRL+SHIFT+ENTER OR without filling the blanks in sheet1 try with the below formula =MAX(OFFSET(INDIRECT("Sheet1!A" & MATCH(A2,Sheet1!A:A,0)),0,1,IF(ISNA(MATCH(TRUE,IND EX(INDIRECT("Sheet1!A" & MATCH(A2,Sheet1!A:A,0)+1 & ":A1000")<"",),)),1000,MATCH(TRUE,INDEX(INDIRECT( "Sheet1!A" & MATCH(A2,Sheet1!A:A,0)+1 & ":A1000")<"",),)),1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: In sheet1 fill the blanks with the material code 1. Select the data range in ColA (say A1:A100). 2. Press F5. From 'Goto window'Special from Options select 'Blanks' and hit OK 3. This will select all blanks. 4. Now press = (equal sign) 5. Then press Up Arrow to reference the cell just above 6. Now press Ctrl and Enter key together If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Dear Jacob, i applied same fomula but result is "0 " "Jacob Skaria" wrote: In Sheet2 with material code in cell A1 =SUMPRODUCT(MAX((Sheet1!$A$1:$A$100=A1)*Sheet1!$B$ 1:$B$100)) If this post helps click Yes --------------- Jacob Skaria "vlook fomula" wrote: Hi, If sheet1 has some data which is each material code has diff supplier rate now I want highest rate on sheet 2 against material code how I can do that with the formula? Example sheet1 M.code S.rate 1002 105 104 106 1003 110 102 1004 125 130 sheet2 M.code S.rate 1002 106 1003 110 1004 130 Regards Zafar |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Next highest Value | Excel Worksheet Functions | |||
| Highest, Second Highest , Third Highest and so on | Excel Discussion (Misc queries) | |||
| Display the Highest, Second Highest, Third Highest and so on... | Excel Discussion (Misc queries) | |||
| 2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions | |||
| second highest value | Excel Discussion (Misc queries) | |||
Linear Mode
