MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.