How to find partial duplicate vs no duplicate values in a column

[GomaPile]

Hi Guys,

A common problem question (I'm sure its been mentioned here before). How to find partial duplicate vs no duplicate values in a column.

For instance both of these 2 shift times have the same shift code at the end "ED1" - 0630-1500 ED1 & 0700-1500 ED1 (one is 30mins less).

I can't figure out what I'm doing wrong, how to find this partial text ED1 to the right, though it's returning the #VALUE! error (I'm thinking to myself I'm way off the mark).

=IF(K7="For the period of:","",(IF(K7="","",IF(MAX(COUNTIF($K$6:$K$15,$K$6:$K$15),"*"&RIGHT($K6,7))>1,"Duplicate","No Duplicate"))))

Regards
Goma (NASA)

 

[GomaPile]

Hi all,

[SOLVED]
It's all good, I managed to work it out myself, it works now. I removed this piece of coding ',$K$6:$K$15)'. If you guys come up with another solution I would like to know too.... cheers

=IF(K5="For the period of:","",(IF(K5="","",IF(MAX(COUNTIF($K$6:$K$15,"*"&RIGHT($K5,7)))>1,"Duplicate","No Duplicate"))))

Thanks
Goma (NASA)