 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2006Convert text strings to a code or number
In column A and B I have text representing an Entity and its Intercompany, respectively. In column C I have concatenated the two. So now in Column C, for example, I have "BillTom" in row 1 and "TomBill" in row 900. I need Excel to convert the text into some sort of code, ASCII or number that shows they are EQUAL. I need something that shows "TomBill" and "BillTom" are the same thing, just in a different order. I cannot build an IF formula or use Find/Replace, as I will have thousands of these types of combinations. Thanks! >In column A and B I have text representing an Entity and its Intercompany, >respectively. In column C I have concatenated the two. So now in Column C, [quoted text clipped - 8 lines] > >Thanks! You could mark entries which are duplicates by your rules. Would that be OK. For example, in a helper column or as a conditional formatting formula: =COUNTIF($C$1:$C$10,B1&A1) would be 1 or more if the reverse concatenation existed in column C --ron I can't mark thousands of rows every month, I want Excel do do the marking/matching via a formula or macro. Anything else? > On Wed, 22 Mar 2006 07:47:28 -0800, MaxNY23 > You could mark entries which are duplicates by your rules. Would that be OK. [quoted text clipped - 6 lines] > > --ron Record a macro while doing it Regards, Peo Sjoblom >I can't mark thousands of rows every month, I want Excel do do the > marking/matching via a formula or macro. [quoted text clipped - 12 lines] >> >> --ron Please reread my original request. I don't think you guys get what I'm trying to do. Record a macro while doing WHAT? I'm looking for a function or formula that converts text to a number. Sort of like the CODE function but for more that just the first letter of a cell. I cannot manually mark each row to distinguish it. Again there will be thousands of rows and hundreds of text combinations. > Record a macro while doing it > [quoted text clipped - 18 lines] > >> > >> --ron >I can't mark thousands of rows every month, I want Excel do do the >marking/matching via a formula or macro. > >Anything else? You did not indicate HOW you wanted the duplicates to be marked, so I gave you a general solution to detect duplicates. If you are not able to apply that information to your problem, then you will need to be more detailed in explaining, the sentence you wrote: "I need something that shows "TomBill" and "BillTom" are the same thing, just in a different order. exactly what you mean by "SHOWS". If you apply the formula I gave you as a conditional format formula, you could format your duplicates differently from the non-duplicates. To me, this is one way of "SHOW"ing that various entries have been duplicated. I guess you mean something else by "SHOWS" but you'll have to be more specific. --ron I also think you need to be a bit more explicit about the nature of the "Entity" and its "Intercompany". Your example just shows two 4 character names, but is this representative of what you will have in reality? Could you, for example, have "ApplesPears" and "PearsApples", or "PineappleGrapes" and "GrapesPineapple" - how would you know where to split the combined strings? Pete Pete_UK wrote... >I also think you need to be a bit more explicit about the nature of the >"Entity" and its "Intercompany". Your example just shows two 4 >character names, but is this representative of what you will have in >reality? Could you, for example, have "ApplesPears" and "PearsApples", >or "PineappleGrapes" and "GrapesPineapple" - how would you know where >to split the combined strings? You should quote or summarize relavant context. If the OP's goal is just to check whether some simple swapping of substrings in one string produces another string, e.g., AAAABBB -> AAAA BBB -> BBB AAAA -> BBBAAAA in which case AAAABBB and BBBAAAA are deemed equivalent, then this could be done using built-in functions and defined names. The name could be seq referring to =ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$65536,1024,1)) and if the two strings were in A1 and A2, the formula would look like =SUMPRODUCT(--(EXACT(MID(A1,seq,64)&LEFT(A1,seq-1),A2)))>0 > In column A and B I have text representing an Entity and its Intercompany, > respectively. In column C I have concatenated the two. So now in Column [quoted text clipped - 9 lines] > > Thanks! Max, Not familiar with VBasic or macros myself, I'd use CHBASE from the morefunc.dll that may not be installed. I'd convert separately your first and second columns from base 36 to Base 10 give a value, a code if you will, for each side, and compare both sides against each other in another column.... way out there, but I've such a simple mind... Beege I previously copied the following formula from the site (don't remember who originally contributed it,) and I modified it slightly to get rid of Upper and subtracting 33. Note: this is far from perfect, but I doubt there is a perfect solution to your request. =SUMPRODUCT(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))) TomBill 691 BillTom 691 FrankJohn 897 JohnFrank 897 DAD 201 BEB 201  SignatureKevin Vaughn > In column A and B I have text representing an Entity and its Intercompany, > respectively. In column C I have concatenated the two. So now in Column C, [quoted text clipped - 8 lines] > > Thanks! >I previously copied the following formula from the site (don't remember who >originally contributed it,) and I modified it slightly to get rid of Upper [quoted text clipped - 9 lines] >DAD 201 >BEB 201 Is the OP interested in telling whether or not TomBill and BillTom are composed of identical word strings, or is he interested in knowing if the ASCII codes of the letters add up to the same value? There are many combinations that will add up to 691: TomBill BillTom KimJune JuneKim WilmaJo etc. --ron > Is the OP interested in telling whether or not TomBill and BillTom are > composed [quoted text clipped - 11 lines] > > etc. His attitude is hardly encouraging either SignatureRegards, Peo Sjoblom >> Is the OP interested in telling whether or not TomBill and BillTom are >> composed [quoted text clipped - 13 lines] > >His attitude is hardly encouraging either Perhaps he wants us to read his mind? --ron This is true which is why I pointed out the trivial example of DAD and BEB equalling the same amount. But I admit your examples adding up to 691 are better than my examples. SignatureKevin Vaughn > >I previously copied the following formula from the site (don't remember who > >originally contributed it,) and I modified it slightly to get rid of Upper [quoted text clipped - 24 lines] > etc. > --ron >This is true which is why I pointed out the trivial example of DAD and BEB >equalling the same amount. But I admit your examples adding up to 691 are >better than my examples. Ah, <sound of hand slapping forehead>. At the time I posted, I didn't understand why you had those examples and I just skipped over it. --ron :) BTW, how long did it take to come up with your examples? I would have liked to come up with examples like those, but I thought it would take a while with a lot of trial and error for examples that long (especially ones that made sense like yours did.) SignatureKevin Vaughn > >This is true which is why I pointed out the trivial example of DAD and BEB > >equalling the same amount. But I admit your examples adding up to 691 are [quoted text clipped - 6 lines] > > --ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.