Consulting

Results 1 to 14 of 14

Thread: Comparing two cells minimum 4 continous Characters match

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Posts
    13
    Location

    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

  2. #2
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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 ..

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

    .. and then format the cell as ..

    "No";;"Yes"

    .. and copy down.


    (P.S. I have deleted your duplicate thread)
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  3. #3
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    Well, Tony has a good formula there. Another example might be ...

    =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.

  4. #4
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Hi, John. I think the thread was closed in error. Sorry about that! It's open again.
    ~Anne Troy

  5. #5
    VBAX Regular
    Joined
    Jul 2004
    Posts
    13
    Location
    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

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    Hey John,

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

  7. #7
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  8. #8
    VBAX Regular
    Joined
    Jul 2004
    Posts
    13
    Location
    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

  9. #9
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    I'm a little confused. This:
    ...ROW(INDIRECT("1:"&LEN(C1)-3))...
    will automatically take into account the length. What does it need to vary for?

  10. #10
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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

    =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?
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  11. #11
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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 ..

    [VBA]
    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
    [/VBA]

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

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

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  12. #12
    VBAX Contributor Cosmos75's Avatar
    Joined
    May 2004
    Location
    Alabama, USA
    Posts
    118
    Location
    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
    =IF(NOT(ISERROR(FIND(LEFT(B1,4),C1))),"Yup","Nope")

    2) Not case-sensitive
    =IF(NOT(ISERROR(SEARCH(LEFT(B1,4),C1))),"Yup","Nope")
    ? http://AccessDb.Info ?
    You live and learn. At any rate, you live. - D.Adams

  13. #13
    VBAX Regular
    Joined
    Jul 2004
    Posts
    13
    Location
    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

  14. #14
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •