Posted by Aladin Akyurek on January 10, 2001 4:26 PM

Array-enter:

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

Hope it works.

Aladin

Posted by dd on January 11, 2001 12:36 AM

No, doesn't work. For example :-

"ad" is equivalent to 197 and "bc" is also equivalent to 197.

Posted by Mark W. on January 11, 2001 3:24 AM

dd is right! Also, your outer AND() function is

superfluous. Care to try again?

Posted by Mark W. on January 11, 2001 3:26 AM

dd, would you like to take a stab at it?

Posted by dd on January 11, 2001 3:32 AM

dd, would you like to take a stab at it?

Still grinding the knife.

Posted by Tim Francis-Wright on January 11, 2001 7:34 AM

Write a formula using only built-in Excel functions

How about

=IF(LEN(A1)<>LEN(B1),FALSE,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(B1))),1))),ROW(INDIRECT("1:"&LEN(B1)))))

?

Posted by dd on January 11, 2001 8:45 AM

Yes! That's great!

I was trying a different route. Couldn't get it down to one formula. Purely for interest, is there any way of getting one formula out of the following?

If one word is in A1 and the other in A2, enter in B1 and fill to B26 :

=IF(LEN(UPPER($A$1))-LEN(SUBSTITUTE(UPPER($A$1),CHAR(ROW()+62),""))=LEN(UPPER($A$2))-LEN(SUBSTITUTE(UPPER($A$2),CHAR(ROW()+62),"")),TRUE,FALSE)

In C1 enter =IF(COUNTIF(B1:B26,TRUE)=26,TRUE,FALSE)

Posted by Mark W. on January 11, 2001 11:40 AM

Posted by Mark W. on January 11, 2001 11:42 AM

Here's what I came up with...

{=AND(LEN(A1)=LEN(B1),ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),B1)))}

Posted by Tim Francis-Wright on January 11, 2001 12:34 PM

Back to the drawing board... anyone have a solution?

{=AND(LEN(A1)=LEN(B1),ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),B1)))}

This gives some false positives:

A1 = state and

B1 = sates

returns TRUE (because each letter in A1 is in B1.

Unfortunately, the same problem plagues my answer

(for reasons I don't quite fathom). Any ideas?

Posted by Mark W. on January 11, 2001 12:50 PM

Good grief!!! Processing...

Posted by Tim Francis-Wright on January 11, 2001 12:55 PM

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

LINEST(yarr, xarr, FALSE) fits the data to

y = mx + b. The FALSE argument fixes b = 0.

In order for m to equal 1, I believe that the

sorted elements must equal each other.

Can anyone prove or disprove this? Or come

up with a better way of comparing the two arrays?

Posted by Mark W. on January 11, 2001 1:00 PM

{=NOT(ISERROR(AND(SEARCH(MID(A7,ROW(INDIRECT("1:"&LEN(A7))),1),B7)=SEARCH(MID(B7,ROW(INDIRECT("1:"&LEN(B7))),1),A7))))}

Posted by Mark W. on January 11, 2001 1:07 PM

...still doesn't catch {"state","sates"}!

Posted by dd on January 11, 2001 4:52 PM

...still doesn't catch {"state","sates"}!

Ian's seems to be the one. However, Aladin's formula could be incorporated with yours but I'm still not sure if it would then cover all situations:

=AND(LEN(A1)=LEN(B1),ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),B1)),SUM(CODE(LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))=SUM(CODE(LOWER(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)))))