Comparing two cells minimum 4 continous Characters match

Hi,

I have to compare 2 columns, where one cell of Column B matches to the other cell of Column C.
I applied =IF(B2=C2, "Yes", "No"), this formula gives me the result only for exact match. Atleast 4 continous character matches also I should consider as same and display Yes in Column A. Could anyone help me to get the following output:

eg., Column A|Column B|Column C

Yes |Peter|Peter Ltd
Yes |Robinson|Mark robinson
Yes |Smith|Smit
No |Jack|Jim
Yes |tommy|ktommyson

Thanks
John

Interesting. I had a bit of a struggle with this. I'm sure an expert can come up with a better way but this does appear to work.

In A1, array-enter ..

Code:

---

=PRODUCT(IF(ISERROR(SEARCH(MID(C1,ROW(INDIRECT("1:"&LEN(C1)-3)),4),B1)),1,0))

---

.. and then format the cell as ..

Code:

---

"No";;"Yes"

---

.. and copy down.


(P.S. I have deleted your duplicate thread)

Well, Tony has a good formula there. Another example might be ...

Code:

---

=IF(ISERR(SEARCH(B1,C1,1)),"Nope","Yup")

---

Non-array entered. One serious caveat with this one, that Tony's does not, for your value in B as Smith and C as Smit, this would fail. If they were Smit in B and Smith in C, it would find it. Not exactly sure if you can manipulate it like that or not.

Hi, John. I think the thread was closed in error. Sorry about that! It's open again.

Zack|Tony|Column1|Column2
Y|Y|Peter|Peter Ltd
Y|N|Robinson|Mark robinson
N|Y|Smith|Smit
N|N|Jack|Jim
Y|N|tommy|kjgtommyson

Hi,

Thanks for nice effort from Tony and Zack.

I have run the 2 formula's and given the output above. In Tony's formula, I couldn't match 'Robinson' and '*robinson'.
Can I get this search also? I got that search from Zack's formula, but I couldn't match 'Smith' and 'Smit'. Basically the search is 4 continous characters in all combinations .

Thanks in Advance
John

Hey John,

Tony's works for me on such a specified unit as robinson. Example file attached.

Hi John,

From the results you posted it looks like you didn't array-enter my formula. Perhaps I should have been clearer. When you have typed the formula, enter it by pressing Ctrl+Alt+Enter, and not just by pressing Enter. This is to tell Excel to work on an array of items (each combination of four consecutive letters in the first cell) rather than just a single value.

Hi,

Thanks Tony. I couldn't do array-enter in my spreadsheet. But I got the logic behind it. Can I get the same in VBA where i can enter different no of characters ( 4 or 6 or 11 or etc.,) in cell E1 as attached?

Thanks
John

I'm a little confused. This:

Quote:

---

...ROW(INDIRECT("1:"&LEN(C1)-3))...

---

will automatically take into account the length. What does it need to vary for?

Hi John,

I don't understand what you mean when you say you couldn't array-enter the formula as you seem to have done it somehow.

If you mean can you change the number of characters which are compared, then yes. Change the formula (still array entered) to

Code:

---

=PRODUCT(IF(ISERR(SEARCH(MID(C2,ROW(INDIRECT("1:"&LEN(C2)-$E$1+1)),$E$1),B2)),1,0))

---

Now if E1 contains 4, 4 characters will be checked as before.
But if E1 contains 5, 5 characters will be checked giving results YYNNY.

If, on the other hand, you want VBA code to produce the same results, then again, yes it can be done - and isn't difficult but where do you want the results to go? Or are you looking for a UDF so that you can just enter a simple formula in the cells in column A?

OK, I thought about it after I posted and you must want a UDF, so here you go ..

In an ordinary Module in the Workbook, put ..

Code:

---

Function udfPresent(rngCheck As Range, rngWithin As Range, Optional iLen As Integer = 4) As String
Application.Volatile
udfPresent = "No"
Dim i As Integer
For i = 1 To Len(rngCheck) - iLen + 1
    If InStr(rngWithin, Mid(rngCheck, i, iLen)) > 0 Then
        udfPresent = "Yes"
        Exit For
    End If
Next
End Function

---

and then you can use in A2, for example, ..

Code:

---

=udfPresent(B2,C2,$E$1)

---

Are you trying to see if any 4 character sequence in Column C matches any 4 character sequence in Column B?

Peter|ABC Petez Ltd <--Match first four characters
Zeter|ABC Peter Ltd <--Match any four characters

(TonyJollans, Nice UDF btw!)

If you are just trying to see if only the first four characters on the text in Column B is found in Column C, then this sould work.

1) Case-sensitive

Code:

---

=IF(NOT(ISERROR(FIND(LEFT(B1,4),C1))),"Yup","Nope")

---

2) Not case-sensitive

Code:

---

=IF(NOT(ISERROR(SEARCH(LEFT(B1,4),C1))),"Yup","Nope")

---

Hi Tony,

Could you tell me how to insert the function? What is udf? Could you please incorporate the function in an excel sheet and send it to me as an attachment.

Thanks,
John

Here ya go. In the file you have to enable macros. You have an example in column A of Tony's UDF, which stands for User-Defined Function. Basically you write your own function, kind of like Excel's SUM or MIN or AVERAGE, but not as fast (as they're written in C which will never be VBA). There is also an example of Cosmos75 formula in there with another examlpe of the same w/ a variation (a little shorter). Either way should work for you. :)