Using CountIf to show Partial Match Value?

So I have a Countif formula that works well for showing unique values:

=IF(COUNTIF($a$2:a2,a2)=1,a2,"")

Is there a way to tweak this to show a value if there is a partial match? I put two examples in my file...the second one would be ideal, since I have a lot of values with misspellings or missing spaces, but I'm not sure if Excel is this sophisticated.

Thanks!