First thank you for looking at this
I have a list of part numbers that appear multiple times and I want to first
extract each unique part number and then use that value and find the maximum
price listed for it. I have tried this formula for the max price part
=MAX(OFFSET(A3,MATCH(A3,$A$3:$A$3500,0)-1,9,1,1))
but it doesn't give the right result, it seems to find the first match. And
I have no idea on the unique part number part.
Please help if possible
Thank you
Joe

Hi,
Say Part no is ColA and Price is ColB
Data entered from row 2 onwards

Create a helper col in C of all the unique part numbers
You can create this by entering =INDEX(A2:A10,MATCH(0,-ISBLANK(A2:A10),0))
in C1

Then In C2 enter this array formula using Crtl,shift & enter

=IF(COUNT(MATCH(A$2:A$10,C$1:C1,0))<COUNT(1/(A$2:A$10<"")),INDEX(A$2:A$10,MATCH(0,(A$2:A$10< "")-ISNA(MATCH(A$2:A$10,C$1:C1,0)),0)),"")

Copy down to your last entry

Then in D2 enter array formula, Ctrl,shift & enter

=MAX(IF($A$2:$A$10=C2,$B$2:$B$10))

Copy down to your last unique part number



"Joe Gieder" wrote:

First thank you for looking at this
I have a list of part numbers that appear multiple times and I want to first
extract each unique part number and then use that value and find the maximum
price listed for it. I have tried this formula for the max price part
=MAX(OFFSET(A3,MATCH(A3,$A$3:$A$3500,0)-1,9,1,1))
but it doesn't give the right result, it seems to find the first match. And
I have no idea on the unique part number part.
Please help if possible
Thank you
Joe

Try a pivot. Get both results in a flash. If "PartNum" is the col header for
part numbers, "Val" the col header for the values, create a pivot on the
source data. In LAYOUT, drag n drop the PartNum into both ROW & DATA areas.
Set it to Count of PartNum in DATA. Drag n drop Val into DATA, set it to Max
of Val. Click to Finish. Hop over to the pivot sheet, just drag "Data" and
drop it over "Total" to re-arrange it into columns. Voila, there you go, all
the unique part numbers listed with their corresponding counts and maximums.
You're done in 15 sec. Success? hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Joe Gieder" wrote:
First thank you for looking at this
I have a list of part numbers that appear multiple times and I want to first
extract each unique part number and then use that value and find the maximum
price listed for it. I have tried this formula for the max price part
=MAX(OFFSET(A3,MATCH(A3,$A$3:$A$3500,0)-1,9,1,1))
but it doesn't give the right result, it seems to find the first match. And
I have no idea on the unique part number part.
Please help if possible
Thank you
Joe

Hi,

First extract the unique values by using advanced filters (check the box for
advanced filters and leave the criteria blank). Now you can use the MAX()
finction

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Joe Gieder" wrote in message
...
First thank you for looking at this
I have a list of part numbers that appear multiple times and I want to
first
extract each unique part number and then use that value and find the
maximum
price listed for it. I have tried this formula for the max price part
=MAX(OFFSET(A3,MATCH(A3,$A$3:$A$3500,0)-1,9,1,1))
but it doesn't give the right result, it seems to find the first match.
And
I have no idea on the unique part number part.
Please help if possible
Thank you
Joe