Number of letters in common in different cells
Posted by Carl W on May 15, 2001 10:02 PM
If I have a word in A1 and a word in B1 what logic string will produce the number of letters in common regardless of letter position in word. I would like this value to be displayed in C1.
Many Thanks,
Carl.
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
Caution
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
Re: Caution
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
Re: Caution
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
Even more cautious
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.
