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.

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

<tbody>
</tbody>

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.