Removing duplicates at conditional positions

[billiboy]

Hi!

I have two columns, A (animals) and B:

A--------------B
Elephant..........1
Monkey...........22
Horse..............3
Elephant..........4
Whale..............3
Horse..............4

I want another column, C, to display the animals without duplicates. In case of a duplicate, the animal should be displayed at the row with the highest B-value.

I.e. I want the column C to look like this:


A--------------B---------C
Elephant..........1............0
Monkey...........22...........Monkey
Horse..............3............0
Elephant..........4............Elephant
Whale..............3............Whale
Horse..............4............Horse

Do anybody have an idea how to do this?

 

[MrKowz]

Can you sort columns A and B? That might make this task easier.

 

[billiboy]

The values in columns A and B are constantly changing, so I'm afraid they can't be sorted

 

[dcamos]

Put in C1
=IF(MAX(IF($A$1:$A$6=A1,$B$1:$B$6,0))=B1,A1,0)
Confirm with Ctrl+Shift+Enter

Assumes that A1 to A6 has the animals and B1 to B6 has the counts

 

[billiboy]

It worked. Thanks man!