Array Formulas, Strings/Anagrams (Brainteasers Continued)

I added sstabb; sttaab and
cx; ln to the test cases

the ln transformation fails for cx;ln
(because 99*120 = 108*110).

I changed LN to SQRT in this formula, and
I couldn't find either a false negative
or false positive.

Unfortunately, it fails on sstabb; sttaab.

It works when the SECOND string is longer.
I ran into the dreaded six-parens problem;
otherwise, I could just have put
IF(LEN(A1)<>LEN(B1) at the front of the
formula...

If all letters are assumed to be the same
case (or if we are case-sensitive), the
following works:

As far as I know, Excel compares only the first
members of arrays. I haven't figured out why.

--Tim F-W

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 I added sstabb; sttaab and
YEP. I LOVE IT.

I HAD USED sqrt TOO. I THOUGHT I BROKE DOWN TOO, BUT NOW I CAN'T FIND ANY TRACE OF THIS ON MY SHEET. FUNNY. OK, SO FAR SO GOOD.
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. Unfortunately, it fails on sstabb; sttaab. : 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? It works when the SECOND string is longer.

SIX-PARENS PROBLEM? SO THIS IS WHAT I WAS RUNNING UP AGAINST ONCE IN A WHILE. IT HELPS KNOWING THAT IT HAS A NAME. If all letters are assumed to be the same

ALAS. =IF(LEN(A1)<>LEN(B1),FALSE,LINEST(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))),SMALL(CODE(MID(B1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))),FALSE)=1) : 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 As far as I know, Excel compares only the first

Aladin