The Dominant Duplicate

[Patrick]

Hi Guys,

I have a 2 variable table, X & Y. I want to find duplicates in X and, flag the duplicate with a lower Y value. X & Y are text strings and, there are only 4 options for Y.

I tried to write it using sumproducts and countifs but had no luck. Your help is much appreciated. Sample is attached.

 

[p45cal]

see the conditional formatting in column A

 

[Patrick]

see the conditional formatting in column A

Thanks p45cal, that is a great formula. I wont be able to use min if Y is a text string though. If Y could only be 4 different names how could you do it?
Edit: John > Joe > Jack

 

[p45cal]

re:"flag the duplicate with a lower Y value"
so how do you decide, among names which is the lower value - alphabetic?

Otherwise, if there are only 4 different names, will they always be the same 4 names?

 

[Patrick]

re:"flag the duplicate with a lower Y value"
so how do you decide, among names which is the lower value - alphabetic?

Otherwise, if there are only 4 different names, will they always be the same 4 names?

It is not alphabetic, they will need to be assigned values. Yes it will be the same 4 names.

 

[p45cal]

try attached

 

[Patrick]

try attached

Thanks a lot P45cal I can work with this.