Another Brainteaser
Posted by Mark W. on January 10, 2001 2:48 PM
Write a formula using only built-in Excel functions
that returns a boolean value that indicates if a word
in cell A1 is an anagram of another word in cell B1.
For example, if A1 contained "slime" and B1 contained
"smile" the formula would return TRUE (other examples
include: "looter","retool"; "looks","spook"). Your
solution should work with words of any length.
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
Yep! It works!!!
It works!!!
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
From the drawing board:
=(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
Here's My Fix...
{=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
Not Quite!!
...still doesn't catch {"state","sates"}!
Posted by dd on January 11, 2001 4:52 PM
Re: Not Quite!!
...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)))))
