Calculate # of shared elements in cells [closed]

I am struggling to get a calc formula to do the following calculations:

A B D   G H A
A B C    2     1
A D E    2     1
A F G    1     2
B H I    1     1

As in the example, I need for the formula to compare two cells, and tell me how many common elements they share -without me stating which elements are there in any of the two cells, although they are always letters from the alphabet, and any given letter is never repeated in the same cell-. Just for clarification, the example is comparing what would be B1 with A2, A3, A4 and A5, and C1 to A2, A3, A4 and A5 respectively.

Any ideas on how to achieve it? Thanks!

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-11-14 15:05:43.778384

Sort by » oldest newest most voted Hi Enric, I think the next array formula can do what you want:

B2: {=(LEN($A2)*LEN(B$1))-SUM(LEN(SUBSTITUTE(B$1;MID($A2;ROW(INDIRECT("$A1:$A"&LEN($A2)));1);"")))} as array formula do no write the brackets { }, enter the formula with [Ctrl+Shift+Enter], and do not drag between cells, copy and paste. ROW(INDIRECT("$A1:$A"&LEN($A2))) do the iteration letter by letter for the text in A2 with the MID() function. In this case we calculate the result deducting to the total combination for both text, the sum of lengths for B2 after every substitution for every letter coincident with every letter in A2 with a null string.

more

Stats

Asked: 2013-11-16 02:29:26 +0200

Seen: 49 times

Last updated: Nov 16 '13