Highest valued cell on another worksheet

**sheldon8** · 08-09-2012, 05:56 PM

i need to find the highest price for each part number. Part numbers, some multiple of the same with different revisions, are listed in column A, revisions in column B. the revisions at time have different prices. I need to have one part number, with the highest, most recent rev and the corresponding price on sheet2, which has the duplicates removed. can anyone help me out with this? i have tried everything i know...
hope this makes sense, file attached

**Trebor76** · 08-09-2012, 08:53 PM

Hi sheldon8,

Welcome to the forum!!

Put this formula into cell B2 of Sheet2 and copy down to cell B9...

=SUMPRODUCT(--(Sheet1!$A$2:$A$20=A2),--(Sheet1!$C$2:$C$20=C2),Sheet1!$B$2:$B$20)

...and put this array* formula into cell C2 of Sheet2 and copy it down to cell C9:

{=MAX(IF(Sheet1!$A$2:$A$20=A2,Sheet1!$C$2:$C$20))}

Regards,

Robert

* You must press CTRL+SHIFT+ENTER after typing or modifying array formulas. When done correctly, Excel will automatically add braces, {}, around the formula. Do not add braces yourself.