Remove duplicate items only after finding the highest value of the duplicate items

[Higgy843]

Good evening one and all, I'm new to forums so please be patient. I will try to get all the information across as needed.

Basically I have a list of products with associated prices. The product list has duplicates. I would like to narrow the list to single items but only when I have found the highest price for each duplicated item.

I have tried Vlookup, INDEX, MATCH a combination of all the lot but can't seem to get it. Would really appreciate some assistance.

Archers	£0.78		Archers	£0.79
Archers*	£0.79
Bacardi / White Rum	£0.78		Bacardi / White Rum	£0.78
Blue Curacao	£0.71		Blue Curacao	£0.71
Bombay Sapphire	£1.24		Bombay Sapphire	£1.24
Brandy	£0.53		Brandy	£0.53
Cactus Jacks Apple / Cherry Sours / Kola	£0.33		Cactus Jacks Apple / Cherry Sours / Kola	£0.33
Cinzano Bianco	£0.43		Cinzano Bianco	£0.43
Cointreau	£1.14		Cointreau	£1.14
Coors Light Can	£1.63		Coors Light Can	£1.77
Coors Light Can*	£1.77
Coors Light Can**	£1.40
Crabbies Alcoholic Ginger Beer	£2.89		Crabbies Alcoholic Ginger Beer	£2.89
Crabbies Alcoholic Ginger Beer*	£2.48
Crimson Finch (Shiraz)	£7.03		Crimson Finch (Shiraz)

<tbody>
</tbody>

Function I have to remove duplicates in Column D:
=IF((COUNTIF(A:A,A1)<>1)+(A1=""),"",A1)

Need to find the highest price of item first though before this is executed, so it would need to be nested into another function I suspect.

The values are taken from an inventory list and can be removed there so the references cannot be absolute. they must look for the product, see if it is a duplicate, find the highest price and record before removing the duplicated product and lower prices.

I'm using Excel 2013 but have 2007 to operate on if needed.

Many thanks for your time and efforts guys, hope it's clear. I can provide a spreadsheet if required just couldn't see where to upload it.

Ash

 

[Mike Szczesny]

You can use a frequency formula to select the unique items. You can use a =max formula to get the largest value. Assume your items are in A1:A10 and amounts are in B1:B10. Place these formulas in D1 and E1

D1:

=IFERROR(INDEX($A$1:$A$10,SMALL(IF(FREQUENCY(IF($A$1:$A$10<>"",MATCH($A$1:$A$10,$A$1:$A$10,0)),ROW($A$1:$A$10)-ROW($A$1)+1),ROW($A$1:$A$10)-ROW($A$1)+1),ROWS($D$1:D1))),"") Use Cntrl-Shift-Enter. copy down This will show only the unique items. E1: =Max(if($A$1:$A$10=$D$1,$B$1:$B$10)) The item in D1 is your first unique item. This will solve the highest number for this unique item. Copy formula down for each next unique item in list. <tbody> </tbody>

<tbody>
</tbody>

 

[Higgy843]

Amazing, thanks for the super fast response. I'll study the formulae and figure out how it's done.

Thanks again.

 

[Mike Szczesny]

I probably should have used $D1 instead of $D$1 to make the max formula select the next item in column D. But I think you can get the sense of the max formula