Automating Find and Replace

I have a large spreadsheet with a column of part numbers and a column of corresponding costs. Part numbers sometimes repeat with different costs.

I want to have just one of each part number, and I want the remaining ones to have either the highest corresponding cost from before or the average of the previous costs.

Originally Posted by pb71

Please see attached. Is this what you want? It gives the maximum cost for each part number using a formula or alternatively you can use a pivot table.

this is perfect. i used the pivot table bc it seems 100 times simpler. did you use the lookup wizard to come up with the formula method bc it seems very complicated unless youre a syntax expert