Convert 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!

On Wed, 22 Mar 2006 07:47:28 -0800, MaxNY23 <MaxNY23@discussions.microsoft.com>
wrote:

>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!


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?


"Ron Rosenfeld" wrote:

> On Wed, 22 Mar 2006 07:47:28 -0800, MaxNY23
> 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
>

Record a macro while doing it


Regards,

Peo Sjoblom


"MaxNY23" <MaxNY23@discussions.microsoft.com> wrote in message
news:CD97A0BA-E8C6-4409-985E-4BE9F9DDF841@microsoft.com...
>I can't mark thousands of rows every month, I want Excel do do the
> marking/matching via a formula or macro.
>
> Anything else?
>
>
> "Ron Rosenfeld" wrote:
>
>> On Wed, 22 Mar 2006 07:47:28 -0800, MaxNY23
>> 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
>>

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.


"Peo Sjoblom" wrote:

> Record a macro while doing it
>
>
> Regards,
>
> Peo Sjoblom
>
>
> "MaxNY23" <MaxNY23@discussions.microsoft.com> wrote in message
> news:CD97A0BA-E8C6-4409-985E-4BE9F9DDF841@microsoft.com...
> >I can't mark thousands of rows every month, I want Excel do do the
> > marking/matching via a formula or macro.
> >
> > Anything else?
> >
> >
> > "Ron Rosenfeld" wrote:
> >
> >> On Wed, 22 Mar 2006 07:47:28 -0800, MaxNY23
> >> 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
> >>
>
>
>

"MaxNY23" <MaxNY23@discussions.microsoft.com> wrote in message
news:0042ADC8-221B-4ADA-993B-B13BDE2929E7@microsoft.com...
> 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!

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

On Wed, 22 Mar 2006 09:11:27 -0800, MaxNY23 <MaxNY23@discussions.microsoft.com>
wrote:

>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 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


--
Kevin Vaughn


"MaxNY23" wrote:

> 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!

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

On Wed, 22 Mar 2006 13:35:28 -0800, Kevin Vaughn
<KevinVaughn@discussions.microsoft.com> wrote:

>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

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
> 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.

His attitude is hardly encouraging either

--

Regards,

Peo Sjoblom

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

On Wed, 22 Mar 2006 18:15:20 -0800, "Peo Sjoblom"
<peo.sjoblom@^^nwexcelsolutions.com> wrote:

>> 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.
>
>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.
--
Kevin Vaughn


"Ron Rosenfeld" wrote:

> On Wed, 22 Mar 2006 13:35:28 -0800, Kevin Vaughn
> <KevinVaughn@discussions.microsoft.com> wrote:
>
> >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
>
> 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
>

On Thu, 23 Mar 2006 08:20:48 -0800, Kevin Vaughn
<KevinVaughn@discussions.microsoft.com> wrote:

>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.
>--
>Kevin Vaughn

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.)
--
Kevin Vaughn


"Ron Rosenfeld" wrote:

> On Thu, 23 Mar 2006 08:20:48 -0800, Kevin Vaughn
> <KevinVaughn@discussions.microsoft.com> wrote:
>
> >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.
> >--
> >Kevin Vaughn
>
> 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
>