0

I want find a formula who allows to check like this :

column 1 : row 1 : "AAA AA ABB A" ...

column 2 : row 1 : "LLL BBLL" ...

the formula return TRUE with rows 1 of the two columns because there is "BB" I don't want search "BB" but find if two rows are "similare" like this example. Thanks you =)

Is it possible via formula ? (no vb)

spacecodeur
  • 2,206
  • 7
  • 35
  • 71
  • You need a strict definition on what "similar" is. Otherwise, we have no idea what you mean. – Yaegz Mar 12 '15 at 20:46
  • I think you need fuzzy matching - see [here](http://superuser.com/questions/437387/comparing-similar-text-strings-in-excel) and [here](http://stackoverflow.com/questions/5859561/getting-the-closest-string-match)... – zx8754 Mar 12 '15 at 20:50
  • @Yaegz : "You need a strict definition on what "similar" is " , you are right, i'm sorry. I need to know if any substring in a row in the column one is somewhere in the other column (no need to know the number of the row, just if its true or false). Is that clear? – spacecodeur Mar 12 '15 at 21:16
  • @zx8754 : I am boring I know :) but I'm looking for a solution without vb and new installation, just know if is possible with the "basic" formula – spacecodeur Mar 12 '15 at 21:17
  • Would you consider a match, if it was one B? – zx8754 Mar 12 '15 at 21:23
  • Damn I see, you make a point...If I want check the sub chain with a minimal number of characters (4 for example) ? but the problem begin (too) complex for formula ? – spacecodeur Mar 12 '15 at 21:29
  • 1
    if you only wanted 1 length of substring to match, it could be done, but excel doesn't want to play with 2 arrays for `MID(`. vba will probably be your only choice – SeanC Mar 12 '15 at 21:44

1 Answers1

0

This is maybe possible with the following approach:

enter image description here

This is a German Excel. WAHR=TRUE and FALSCH=FALSE ;-)

Formulas in C4:

{=NOT(AND(ISERROR(FIND(MID($A4,ROW($A$1:INDEX($A:$A,LEN($A4)-$C$2+1)),$C$2),$B4))))}

and in D4:

{=NOT(AND(ISERROR(SEARCH(MID($A4,ROW($A$1:INDEX($A:$A,LEN($A4)-$C$2+1)),$C$2),$B4))))}

These are array formulas. Input them in the cell without the curly brackets and the press [Ctrl]+[Shift]+[Enter] to complete.

The approach is to get an array of $C$2 length substings from string in column A and find/search them in the string in column B.

Axel Richter
  • 56,077
  • 6
  • 60
  • 87