Searching for deconstructed string..?

[EME]

I'm not sure if the title of this thread is accurate to my question because I've never encountered this problem:

Let's say in one cell, A1, I have the text "01HUFFING" and in B1 I have "HFFNG." My question is, is it possible to search for a combination of any 4 characters in order from A1 - not excluding any other characters in between like spaces, numbers, other letters etc - within B1 and/or vice versa? So I'm trying to get excel to check to see if 0*1*H*U, 1*H*U*F, H*U*F*F, etc. is in B1 and then return a simple "Y" or "True" in a given cell if it is. I have just under 100,000 rows to apply this to so being able to write a macro or simple program would expediate this process greatly. Thanks.

Also, I'm wondering if it is possible to perform this same action but comparing a text file to a cell versus a cell to a cell and return the value found in the text file instead of "Y" or "True".

[Phil_V]

Presumably from what you are saying, and the example you have given;

So I'm trying to get excel to check to see if 0*1*H*U, 1*H*U*F, H*U*F*F, etc. is in B1

A1, I have the text "01HUFFING" and in B1 I have "HFFNG."

Would return FALSE?

An example workbook, with a few lines of expected result would be useful here to avoid wasted time spent coding an incorrect macro.

[EME]

Presumably from what you are saying, and the example you have given;


Would return FALSE?

An example workbook, with a few lines of expected result would be useful here to avoid wasted time spent coding an incorrect macro.

Here would be 3 example comparions:

05WHITING WHITING
GIVENSMO GVNS
ONTARIO85 ONT85

Each of this would have a "TRUE" next to it if the macro I'm thinking of worked ideally. Each of the values in the right column is a combination of at least 4 sequential characters from the value in the cell to its immediate right with spaces, abscences or other characters in between. I don't know if this is possible or if it is possible, if it's practical for the amount of data I'm going through. I'll check this thread every few minutes so let me know if there's anything I can clarify further.

[shg]

Try this:

Please Login or Register  to view this content.

E.g., =KindaLike("05WHITING", "WHITING") returns True.

[StephenR]

I bumbled about and came up with a solution too, but shg's is much more elegant and concise. Very neat sir.

Please Login or Register  to view this content.

[Phil_V]

As above, this was my attempt, but Shg's seems more elegant

Please Login or Register  to view this content.

Test Data:

Please Login or Register  to view this content.

[EME]

Thanks for all the replies, I'm meeting with some of the programmers later today, hopefully these will work and they look like they will.

[EME]

shg, tried your code and it worked well for certain entries like the ones I listed, but there are many for which it did not work. I think my examples may have been unclear or maybe I am misinterpreting your code or implementing it incorrectly. The code you gave seems to search for all of a given cell's characters with anything in between within another cell's characters. For instance, it would return "TRUE" for

abc agbhc1

but it would return false for

hefty hfty

Is there any way around this? To repeat my original quandry: I need the code to search for a sequential series of 4 characters from a given cell within another cell with any other characters allowed to fall between. Besides the "hefty" example above, another that should return as "TRUE" is:

b_ice blueice1

Phil_V and StephenR, I have not yet run your suggestions but they seem to be along the same lines and I feel like I might run into the same sort of error, if I am mistaken, please let me know because I'm not that adept with VB so I could be missing something. Thanks.

[shg]

it worked well for certain entries like the ones I listed, but there are many for which it did not work.

We can only work with the data you provide.

=KindaLike("agbhc1", "abc") returns TRUE as you say

=KindaLike("hefty", "hfty") returns TRUE, not FALSE

=KindaLike("blueice1", "b_ice") returns FALSE (there is no underscore in the first word)

Perhaps more examples and explanation?

Do you mean test all combinations of four characters (left to right) in the first word, and see if they appear in the same order in the second word? That's quite a different problem.

[EME]

We can only work with the data you provide.

=KindaLike("agbhc1", "abc") returns TRUE as you say

=KindaLike("hefty", "hfty") returns TRUE, not FALSE

=KindaLike("blueice1", "b_ice") returns FALSE (there is no underscore in the first word)

Perhaps more examples and explanation?

Do you mean test all combinations of four characters (left to right) in the first word, and see if they appear in the same order in the second word? That's quite a different problem.

I think so, yes. So for the last example

b_ice blueice1

would return true because, for one reason, the combination from b_ice found in blueice1 is "b*ice" and the code would have ideally searched for "b*i*c*e" among others as possible sequential 4 character sequences within blueice1. I think this could be a bulky code but it would still be much faster to write and run than doing all this manually. But is this a little but more clear? I can provide more examples if necessary.

[shg]

I'd want to see MANY more examples and expected results.

[EME]

I'd want to see MANY more examples and expected results.

Alright, well here are a few, and let's say that excel is taking characters from the left column and searching for them in the right column. Also, to the right of the colon of either TRUE or FALSE is an explanation, for true it will be the sequence of characters excel would have expected to search for in order to determine the validity of the match.

heavys2 hvyside TRUE: "h*v*y*s"

heavys2 hyvside FALSE: same characters as above, but not in sequential order

ADGI ABCDEFG TRUE: "A*D*G*I

ADGI ABCEFGHI FALSE: column on right does not contain "D"

CYPRESSJCT3 CYPRSJUNCTION3 TRUE: there are many possible sequences which should return this result, one is "C*S*J*T"

biglake 5biglake3 TRUE: your previous code would have returned this result also, and there are many sequences I would be looking for to do so also like "b*i*g*l"

SEQUI SEQ99 FALSE: similar to one above; right column does not contain any 4 characters also contained in left column.

I hope these helped clarify, I wasn't sure exactly how many "MANY" examples are but I think these, along with others in previous posts, helps clarify my goal.

[shg]

You agree with these results?

Please Login or Register  to view this content.

Null return means no match.

[shg]

Assuming they are, see attached.

[Phil_V]

Just looking through your attachment out of interest

Please Login or Register  to view this content.

Just to clear up, this defaults to FALSE if omitted, but as you might expect needs to be TRUE to perform a case-insensitive test, FALSE gives a case-sensitive test

[EME]

SHG, all those results would be what I would expect and the code you gave me looks like what I need but I can't get it to run in excel. I open up the macro tab, open VB editor, copy and paste and save it and then try to put it/run it in a cell in excel and it won't run; I'm getting a compiler error, most likely because I'm inserting extra information in the compiler or deleting good information.

edit: when I try to run it, it says "Compile Error: Syntax Error" and then when I click OK it highlights first line in VB editor "Sub Macro3 ()" which is what it's called.

[shg]

Phil,

Thank you for the catch. The comment should say

Please Login or Register  to view this content.

I will correct and repost the attachment.

EME,

There is no Macro3(). You can either copy the file to a folder and import it from the VBE, or copy the code, put it in a module, and delete the first line (Attribute ...)

[EME]

Phil,

Thank you for the catch. The comment should say

Please Login or Register  to view this content.

I will correct and repost the attachment.

EME,

There is no Macro3(). You can either copy the file to a folder and import it from the VBE, or copy the code, put it in a module, and delete the first line (Attribute ...)

Ok, so I've copied it into VB editor in the macros tab in excel, saved it, closed it. Now when i click in a cell, go to Tools>Macro and click on "Macros" nothing comes up. Earlier, when I was experimenting with this, I was able to find the macros but it wouldn't run because of the error I mentioned earlier. The was I found the macro then was to record an arbitrary macro - where the macro3() came from-then go into the VB editor and delete it and paste your code. Keep in mind I know little if any VB and my experience with writing macros in excel began yesterday. Thanks again for all the help, if this works out, it will save a tremendous amount of labor time.

[EME]

Also, I think yesterday I didn't run it as a macro but as a regular function. As in, in a cell I just typed in "=KindaLike(A1,B1)" just like a Vlookup or something. So I just tried to do that and the error I got was "Ambiguous Name Detected: KindaLike"

[shg]

Now when i click in a cell, go to Tools>Macro and click on "Macros" nothing comes up.

That's because it's a function that requires arguments. The only things that show up when you list macros are subs that don't take any.

As in, in a cell I just typed in "=KindaLike(A1,B1)" just like a Vlookup or something.

The code will work as either a worksheet function or in VBA (i.e., you can call it from the worksheet or from other code).

So I just tried to do that and the error I got was "Ambiguous Name Detected:

Because now you have two copies of the macro. Delete the older one.

[EME]

I'll detail my steps and maybe it will give you a better insight into exactly where I'm messing up:

1. I open up the VB editor from Tools>Macro and then paste your entire code minus only the first line into the compiler.

2. I save it, then close the window.

3. In excel, in the third column next to the two containing cells which I want to compare, I type in "=KindaLike(A1,B1,4)" and hit enter.

4. I get the error "Ambiguous Name Detected: KindaLike"

I can't figure out where I'm going astray...

edit: I deleted the previous code, but when you say delete the older macro, how do I do that? When I go to Tools>Macro>Macros, there is nothing listed so I assumed there were none existing and everything was cleared.

[shg]

Go through the other code modules. There another copy of the macro in one of them.

In the VBE, you can do Ctrl+F (Find) and search throughout the project.

[Phil_V]

As Shg said in post #20, you have TWO COPIES of the function.
You need to find where you pasted the old version and delete it.

[EME]

Wow, Phil_V and SHG, I can't thank you guys enough. The code works flawlessly and has saved me countless hours of manual checking.

[shg]

You're welcome. Would you please mark the thread as Solved?