Looking for formula to identify several duplicate item codes and change price to highest

Hello,

I'm currently doing up a spreadsheet for service kits for machines, I have many different filters on this worksheet, and many are duplicates. I was wondering if there was a formula that can detect my item codes/numbers, and by using those, check a column/set of columns and set the price as the same for any given part, preferably the highest number of the lot. e.g ff737 may be on the worksheet 5 times, but some values may be inputted months ago not accounting for the increase of price over time, leading to different prices, as such I would like to update the price to the most recent across all duplicates, if possible.

In my attached file, I have a general sample of what it looks like, several columns that may need the 'Buy' price changed to be universal to the highest price for the given item number via formula. If any more information is required please let me know.

Any advice/help is greatly appreciated

Welcome to the forum.

The only way to do this with a formula is to have a central (non-duplicate) list of parts and their curremt prices, then use a lookup formula in each of the BUY columns that pulls the current proce from the lookup list.

Have a look at the XLOOKUP function.

I've mocked up what I mean in the attached.

Thanks for the rep.