Posted by Aladin Akyurek on May 15, 2001 11:26 PM

Carl

In C1 array-enter: =SUM(1*ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),B1)))

In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time (instead of just ENTER).

Copy down the formula as far as needed.

Aladin

Posted by Carl W on May 16, 2001 1:07 AM

Thanks Aladin,

Works like a dream. Whats the deal with array formulas as opposed to regular formulas?

Carl

Posted by Aladin Akyurek on May 16, 2001 9:40 AM

Carl,

The formula will flounder on some specific pairs of words.

Working on it...

Aladin

Posted by Mark W. on May 16, 2001 1:22 PM

Aladin, this will work...

=SUM(IF(FREQUENCY(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),ROW($65:$90))*FREQUENCY(CODE(MID(UPPER(B1),ROW(INDIRECT("1:"&LEN(B1))),1)),ROW($65:$90)),1))

There may be a simpler approach, but I've grown

quite fond of FREQUENCY(). : )

Posted by Aladin Akyurek on May 16, 2001 1:46 PM

Carl -- Mark's FREQUENCY formula is just what we need. Thanks Mark. , this will work... =SUM(IF(FREQUENCY(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),ROW($65:$90))*FREQUENCY(CODE(MID(UPPER(B1),ROW(INDIRECT("1:"&LEN(B1))),1)),ROW($65:$90)),1)) There may be a simpler approach, but I've grown

Posted by Mark W. on May 16, 2001 2:46 PM

Aladin, this modification will guard against the

encroachment of special characters (one can't be

too careful), but must be entered as an array

formula...

{=SUM(IF(FREQUENCY(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1)),ROW($64:$90))*FREQUENCY(CODE(MID(UPPER(B2),ROW(INDIRECT("1:"&LEN(B2))),1)),ROW($64:$90))*MID(0&REPT(1,26)&0,ROW($1:$28),1),1))} -- Mark's FREQUENCY formula is just what we need. Thanks Mark. , this will work... : =SUM(IF(FREQUENCY(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),ROW($65:$90))*FREQUENCY(CODE(MID(UPPER(B1),ROW(INDIRECT("1:"&LEN(B1))),1)),ROW($65:$90)),1)) : There may be a simpler approach, but I've grown

Posted by Carl W on May 17, 2001 8:19 PM

Re: Even more cautious (thanks again)

Thanks Aladin and Mark,

I work in psycholinguistics and you are making my job a lot easier. Much appreciated. I picked up that the first formula didn't work on words that had repeated letters, in that it counted those letters as occuring twice in the second word. Thanks again for the correction.

Carl.