Highest Value

[vlook fomula]

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

 

[Jacob Skaria]

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

 

[vlook fomula]

Dear Jacob,
i applied same fomula but result is "0 "

 

[Jacob Skaria]

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

 

[Bernd P]

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

 

[Jacob Skaria]

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,INDEX(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]

Sorry Bernd; i didnt get you..

 

[vlook fomula]

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]

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

 

[vlook fomula]

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