Duplicate lettering in names
 

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.

Duplicate lettering in names
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rocko" wrote:

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.

Duplicate lettering in names
 

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.

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rocko" wrote:

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.

Duplicate lettering in names
 

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

--
Biff
Microsoft Excel MVP


"Rocko" wrote in message
...
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.

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rocko" wrote:

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.

Duplicate lettering in names
 

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(IN DIRECT("1:"&LEN(A1))),1),"")))<LEN(A1)-1


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"Rocko" wrote in message
...
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.

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rocko" wrote:

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.

Duplicate lettering in names
 

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

"T. Valko" wrote:

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(IN DIRECT("1:"&LEN(A1))),1),"")))<LEN(A1)-1


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"Rocko" wrote in message
...
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.

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rocko" wrote:

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.

Duplicate lettering in names
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rocko" wrote in message
...
Thank you Biff & thanks for the advice also. Works perfectly.

"T. Valko" wrote:

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(IN DIRECT("1:"&LEN(A1))),1),"")))<LEN(A1)-1


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"Rocko" wrote in message
...
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.

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rocko" wrote:

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.