| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| counting duplicate names | Excel Worksheet Functions | |||
| Deleting duplicate names | Excel Discussion (Misc queries) | |||
| Duplicate names | Excel Discussion (Misc queries) | |||
| How do you delete duplicate addresses, but keep duplicate names? | Excel Discussion (Misc queries) | |||
| deleting duplicate names | Excel Discussion (Misc queries) | |||
Linear Mode
