0

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 with LibreOffice? Thanks!

0

2 Answers 2

0

If you have always 3 letters in the cells in row 1, you can use:

=IF(ISNUMBER(SEARCH(MID(B$1;1;1);$A2));1;0) + IF(ISNUMBER(SEARCH(MID(B$1;2;1);$A2));1;0) + IF(ISNUMBER(SEARCH(MID(B$1;3;1);$A2));1;0)

in cell B2 and copy/paste to the other cells (the $ to fix the columns/rows are already in the formula)

There are 3 IF, one for each letter to test. If the letter is found, the result is a number and the IF replace it with 1, if not with 0 and the result of the 3 IF is added.

If you have more letter in a column, only add one IF to the formulas of that column.

The quantity of letters in Column 1 can change without any change to the formula.

If you have space between the letters in row 1, like A B D' instead ofABD`, change the 1, 2 and 3 in the MID functions 2nd parameter to 1, 3 and 5.

Result

0

If you always have three letters in the cells in Row 1, you can use:

=SUM(--ISNUMBER(SEARCH(MID(B$1,{1,2,3},1),$A2)))

This is an incremental evolution of laurent’s answer.  I made a couple of optimizations.  First of all,

IF (Boolean_value, 1 , 0)

is a little redundant, since TRUE has the value 1 and FALSE has the value 0.  Booleans can be readily converted to their equivalent integers with the -- prefix; short for - ( - (value) ), this preserves the numeric value but coerces the type to integer.  And then the {1,2,3} is an array that (with the SUM function) eliminates the need to add three nearly identical terms.

To handle strings in Row 1 with varying lengths, use

=SUM(--ISNUMBER(SEARCH(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1),$A2)))

This is like the first answer except we get the length of the string at the top of the column, LEN(B$1), and concatenate it to the string “1:”, forming something like “1:3”.  This looks like a range of rows, and the INDIRECT function treats it exactly that way, returning the address range $1:$3.  And then ROW() of that returns the array {1,2,3}.  This is a trick to create an array of consecutive numbers whose beginning and end are not predetermined.

The above formula must be entered with Ctrl+Shift+Enter.  I’m not sure why it must and the first one doesn’t.

Your example/illustration makes it look like your strings have embedded spaces.  If that’s true (but you don’t want the spaces to count), the formula becomes

=SUMPRODUCT(--(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1)<>" "),--ISNUMBER(SEARCH(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1),$A2)))

which also must be entered with Ctrl+Shift+Enter.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .