Duplicate lettering in names

[Rocko]

I need to go through a list of first names & identify ones which have at
least 2 or more of the same letters. For example "Angela" = 2 x a, or
Patricia = 2 x a & 2 x i. I hope this is clear. Any help would be greatly
appreciated.

 

[Shane Devenshire]

Hi,

Try the following array formula:

=MIN(LEN(SUBSTITUTE(A1,MID(A1,ROW(A$1:A$11),1),)))<LEN(A1)-1

to make it an array press Shift+Ctrl+Enter to enter the formula.

It is set up for names up to 11 characters, but you can make them any length
by change the 11 in A$1:A$11 to a larger number.

 

[Rocko]

Thanks very much for your prompt response. The only minor glitch I had was
with the casing of my names. For instance it didn't recognise "Elise" as
having 2 e's, but once I changed all the names in my list to lower case,
which was an easy fix, it worked brilliantly. Thanks again for your help
Shane.

 

[T. Valko]

The only minor glitch I had was with the
casing of my names....once I changed all
the names in my list to lower case

Better to change the formula so that it works with your data rather than
changing the data so that it works with the formula!

Try this version. Accounts for case. (still array entered):

=MIN(LEN(SUBSTITUTE(UPPER(A1),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")))<LEN(A1)-1

 

[T. Valko]

Ooops!

Correction:

=MIN(LEN(SUBSTITUTE(UPPER(A1),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")))<LEN(A1)-1

Should be:

=MIN(LEN(SUBSTITUTE(UPPER(A1),MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1),"")))<LEN(A1)-1

 

[Rocko]

Thank you Biff & thanks for the advice also. Works perfectly.

 

[T. Valko]

You're welcome. Thanks for the feedback!