Code to concat "Jo Smith" and "Ja Smith" if there are multiple "J Smiths"
 

If there are names in a list that are similar, like Roosevelt Smith and
Rosette Smith, how could they be concatenated in a third column such
that the first letter that is different in the first name be used in
the combo. For instance, the names would be combined as Rv Smith and
Rt Smith. Note that Rosette only has one "o" whereas Roosevelt has
two, so code that simply counts same letter placements will not work.
So, I have a firstname field and a lastname field, and I want a third
field with the formula. Thanks much for any help ;-)
Arnold

Code to concat "Jo Smith" and "Ja Smith" if there are multiple "J Smiths"
 

the first letter that is different in the first name
Roosevelt
Rosette
'R' is not different therefore it would be eliminated
Roosevelt would be "v Smith"
Rosette would be " Smith" because all letters from Rosette appear in
Roosevelt.
Even if we skip the first letter comparison and start with the 2nd letter,
we would get only
"R Smith" for Rosette, not "Rt Smith" because Roosevelt has a "t".
The same for
Daniel Smith
Danielle Smith
Both would be only "D Smith"
My burning question to you is why try to do this?

Mike F

"Arnold" wrote in message
ps.com...
If there are names in a list that are similar, like Roosevelt Smith and
Rosette Smith, how could they be concatenated in a third column such
that the first letter that is different in the first name be used in
the combo. For instance, the names would be combined as Rv Smith and
Rt Smith. Note that Rosette only has one "o" whereas Roosevelt has
two, so code that simply counts same letter placements will not work.
So, I have a firstname field and a lastname field, and I want a third
field with the formula. Thanks much for any help ;-)
Arnold

Code to concat "Jo Smith" and "Ja Smith" if there are multiple "J Smiths"
 

Thanks for replying

Good--you gave an even better example with Daniel vs. Danielle. The
reason for doing this is for space and traditional-saving business, but
I would also just like to know how.

Is it possible for code to compare 2 or more firstnames in a range
letter by letter and, in cases like Daniel and Danielle and Rosette and
Roosevelt, automatically choose the first capital letter followed by a
different second letter? This would be cool.

Code to concat "Jo Smith" and "Ja Smith" if there are multiple "J Smiths"
 

My burning question to you is why try to do this?

David Cox, just one of the many "David Cox"s


"Mike Fogleman" wrote in message
m...
the first letter that is different in the first name
Roosevelt
Rosette
'R' is not different therefore it would be eliminated
Roosevelt would be "v Smith"
Rosette would be " Smith" because all letters from Rosette appear in
Roosevelt.
Even if we skip the first letter comparison and start with the 2nd letter,
we would get only
"R Smith" for Rosette, not "Rt Smith" because Roosevelt has a "t".
The same for
Daniel Smith
Danielle Smith
Both would be only "D Smith"
My burning question to you is why try to do this?

Mike F

"Arnold" wrote in message
ps.com...
If there are names in a list that are similar, like Roosevelt Smith and
Rosette Smith, how could they be concatenated in a third column such
that the first letter that is different in the first name be used in
the combo. For instance, the names would be combined as Rv Smith and
Rt Smith. Note that Rosette only has one "o" whereas Roosevelt has
two, so code that simply counts same letter placements will not work.
So, I have a firstname field and a lastname field, and I want a third
field with the formula. Thanks much for any help ;-)
Arnold

Code to concat "Jo Smith" and "Ja Smith" if there are multiple "J Smiths"
 

Creating a rule for the 2nd letter when all letters match would be the
tricky part. Even if you choose the last letter in the name if all letters
match you would get Rv and Re or Dl and De. In my example with Dl & De how
could you tell one from the other? What if all letters match including the
last letter?
Rose
Roseanne
Both would Re. Again, how would you tell them apart?
Now you would need two rules in an "IF" statement. What if there are more
than 2 similar names to distinguish from?
Rose
Roseanne
Rosanna
Roanna
The logic would be mind boggling trying to deal with all names that are
similar and all the spelling possibilities that a parent can create to make
their child unique, and still get a result that has some meaning or
relationship to the real name. Even if you come up with a MegaRule to cover
all possibilities, that result still would not be consistent for a
particular name. Rose would be Re when compared with Roseanne, but when
compared to Roanna it would be Rs, which would then break the rule for
Roseanne, etc......
IMHO, way too complicated to tackle with Excel.

Mike F

"Arnold" wrote in message
oups.com...
Thanks for replying

Good--you gave an even better example with Daniel vs. Danielle. The
reason for doing this is for space and traditional-saving business, but
I would also just like to know how.

Is it possible for code to compare 2 or more firstnames in a range
letter by letter and, in cases like Daniel and Danielle and Rosette and
Roosevelt, automatically choose the first capital letter followed by a
different second letter? This would be cool.

Code to concat "Jo Smith" and "Ja Smith" if there are multiple "J Smiths"
 

<IMHO, way too complicated to tackle with Excel

I don't think so. Once we get a correct specification of what to do when, it can almost certainly be done in Excel (and in a dozen
of other applications)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mike Fogleman" wrote in message m...
| Creating a rule for the 2nd letter when all letters match would be the
| tricky part. Even if you choose the last letter in the name if all letters
| match you would get Rv and Re or Dl and De. In my example with Dl & De how
| could you tell one from the other? What if all letters match including the
| last letter?
| Rose
| Roseanne
| Both would Re. Again, how would you tell them apart?
| Now you would need two rules in an "IF" statement. What if there are more
| than 2 similar names to distinguish from?
| Rose
| Roseanne
| Rosanna
| Roanna
| The logic would be mind boggling trying to deal with all names that are
| similar and all the spelling possibilities that a parent can create to make
| their child unique, and still get a result that has some meaning or
| relationship to the real name. Even if you come up with a MegaRule to cover
| all possibilities, that result still would not be consistent for a
| particular name. Rose would be Re when compared with Roseanne, but when
| compared to Roanna it would be Rs, which would then break the rule for
| Roseanne, etc......
| IMHO, way too complicated to tackle with Excel.
|
| Mike F
|
| "Arnold" wrote in message
| oups.com...
| Thanks for replying
|
| Good--you gave an even better example with Daniel vs. Danielle. The
| reason for doing this is for space and traditional-saving business, but
| I would also just like to know how.
|
| Is it possible for code to compare 2 or more firstnames in a range
| letter by letter and, in cases like Daniel and Danielle and Rosette and
| Roosevelt, automatically choose the first capital letter followed by a
| different second letter? This would be cool.
|
|
|

Code to concat "Jo Smith" and "Ja Smith" if there are multiple "J Smiths"
 

Thanks for all the insight. I suppose I wasn't specific enough. In
all of these cases, such as Rose, Roseanne, Rosanna, etc., it would not
matter what letter combination (first letter plus another) the code
would choose, just as long as they are different--we'll know which
person is which. The resulting abbreviations for this example could be
Ro for Rose, Re for Roseanne, and Ra for Rosanna. I am dealing with
rather small sample set, and the chances of more than two similar names
are slim. Hope this helps. Thanks again,

Arnold