Finding duplicates & typos

[JenniferMurphy]

With help I got here some time ago, I was able to build a table that shows how often a string from a master list occurs in a reference list.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Master[/TD]
[TD="align: center"]Tally[/TD]
[TD="align: center"]Reference[/TD]
[TD="align: center"]# Errors[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Formula in D[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]jane[/TD]
[TD="align: center"]2[/TD]
[TD]jane sally polly[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[TD]D5: =COUNTIF($E:$E,"*" & C5 & "*")[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Joe[/TD]
[TD="align: center"]3[/TD]
[TD]joe sam sally[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[TD]D6: =COUNTIF($E:$E,"*" & C6 & "*")[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]sally[/TD]
[TD="align: center"]3[/TD]
[TD]joe sam joe[/TD]
[TD="align: center"]1[/TD]
[TD]Duplicate[/TD]
[TD]D7: =COUNTIF($E:$E,"*" & C7 & "*")[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]sam[/TD]
[TD="align: center"]6[/TD]
[TD]sam poly[/TD]
[TD="align: center"]1[/TD]
[TD]Invalid[/TD]
[TD]D8: =COUNTIF($E:$E,"*" & C8 & "*")[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]polly[/TD]
[TD="align: center"]2[/TD]
[TD]sam peet sam[/TD]
[TD="align: center"]2[/TD]
[TD]Both[/TD]
[TD]D9: =COUNTIF($E:$E,"*" & C9 & "*")[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]pete[/TD]
[TD="align: center"]0[/TD]
[TD]sally polly[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[TD]D10: =COUNTIF($E:$E,"*" & C10 & "*")[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD]sam[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD]jane joe sam[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now I need a way to identify errors in the reference list. E7 contains a duplicate name, E8 contains a name that is not in the master list, and E9 contains both errors.

Can someone help me with a formula that I can put in F5:F12 that will return some indication of these errors, such as a count of the number of errors?

Note that in real life, the reference list may be much longer.

Thanks

 

[Marcelo Branco]

Maybe...

F5 copied down
=LEN(TRIM(E5))-LEN(SUBSTITUTE(E5," ",""))+1-SUMPRODUCT(--ISNUMBER(SEARCH(C$5:C$10,E5)))

Hope this helps

M.