Ask Your Question

Calculate # of shared elements in cells [closed]

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

EnricGTorrents gravatar image

updated 2013-11-16 02:32:20 +0200

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 flag offensive 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

1 Answer

Sort by » oldest newest most voted

answered 2013-11-16 03:37:03 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower


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

Seen: 49 times

Last updated: Nov 16 '13