# Array Formulas, Strings/Anagrams (Brainteasers Continued)

Posted by Aladin Akyurek on January 11, 2001 6:16 PM

First things first. Here is the test-set.

smile slime

looter retool

looks spook

smile SLIME

smile slim

maria amari

ad bc [ due to dd ]

aba baa

aaa aaa

smile slime

state sates [ killer case ]

statute state

zaaaza saaaza

Here is my second array formula:

=LEN(A1)=LEN(B1)*(SUM(LN(CODE(LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))=SUM(LN(CODE(LOWER(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1))))))

This time I'm applying an LN transformation. It passes the test pairs. I also expect it to fail on some yet unknown case.

Ian's formula (posted by dd) passes the test too.

I posted my first formula while I knew it would break down, judging from my struggle in trying to use CODE to design a formula-based sorting routine for text values. DD's case just did that. It generated (I like to suppose) valuable methods of transformations at Tim's hand.

Tim's (I believe his second) formula

=(LINEST(SMALL(CODE(LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),ROW(INDIRECT("1:"&LEN(A1)))),SMALL(CODE(LOWER(MID(B1,ROW(INDIRECT("1:"&LEN(A1))),1))),ROW(INDIRECT("1:"&LEN(A1)))),FALSE)=1)*(LEN(A1)=LEN(B1))=1

is a striking construction. It appears to handle well strings of equal length. I don't yet understand why it can't be adjusted to work with strings of unequal length. Maybe I missed something here?

Other earlier posted formulas (e.g., Mark's), including the following,

=LEN(A1)=LEN(B1)*(CHAR(SMALL(CODE(LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),ROW(INDIRECT("1:"&LEN(A1)))))=CHAR(SMALL(CODE(LOWER(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1))),ROW(INDIRECT("1:"&LEN(B1)))))) [ includes Tim's use of small ]

all fail on state/sates pair. There is a mystery here, perhaps a bug: In testing preceeding formula on state/sates, I get the following

{"a";"e";"s";"t";"t"}={"a";"e";"s";"s";"t"}

which Excel evaluates to TRUE. Is this not a bug? When one rewrites these string arrays as

=CODE({"a";"e";"s";"t";"t"})=CODE({"a";"e";"s";"s";"t"}),

the result is FALSE. Mark, Tim: any ideas, conjectures on this peculiarity?

Aladin