Highlight Approximate Match in a column (Not exact Match)

[ialwayscapital]

Guys I am stuck again !

I have a list of names in column A. I want to highlight the names if it matches 70%

For example :

1. Ram Kumar
2. Rama Kumar
3. Akash Tripathi
4. Aakash Tripathi
5. Mr. Ram Kumar
6. Akash M Tripathi
7. Ram Kumar

Excel conditional formatting finds Row 1 & 7 as exact match, but it can't help with others. Where as Row 1,2,5 & 7 are actually same person. I need formula for Conditional Formatting.

Somehow, I found one formula but its not working in my case.

=IF(A1<>"",COUNTIF($A$1:$A$23,"*"&MID(A1,1,INT(LEN(A1)*0.7))&"*")>1)

Excel Fuzzy Lookup thing is not for me, Because I want conditional formatting formula, which can highlight cell instantly after entering data. This sheet gets regular entries from various persons.

Thanks.

 

[ialwayscapital]

Just to add to my question, I managed to add one helper column (column B), where i put the formula

=Clean(Trim(Proper(A1)))

So now, all the entries has no extra spaces or special characters. "SPACE" can be the middle point for the formula to look for left & right and do the job. Additionally there can be more than one SPACE. i.e Akash M Tripathi

 

[ialwayscapital]

dump. Anyone ?