Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. | | | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
HOW TO SEPARATE THE TEXT AND FIGURE "27419 WK SMITH" INTO TWO CE. | New Users to Excel | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |