Matching a small # string against a larger # string

After a considerable amount of searching, I cannot find an exact answer to my quest, and am confused at this point if I should be trying to use match, vlookup, both, or a completely different function.

It seems like a simple task. I have a 7 digit string of numbers, from 1 to 50. They are random, but in consecutive order, i.e 3,15,22,26,33,41,48. I then have a listing of larger strings of 25 numbers that are organized the same way, 1-50, random and in order. I want to find the first time that the 7 digit string occurs in one of the 25 digit strings.

I used the wildcard * with the Match function, but would only get an affirmative hit if the order of the 7 digit string happened to match exactly the same way inside of a 25 digit string. I.E., 21,22,26,27,31,32,40 would match if inside the large string was 15,21,22,26,27,31,32,40,43 but not if inside the large string was 15,21,22,26,27,30,31,32,40,43.

Any help would be appreciated.

Originally Posted by mikerickson

If 21,22,26,27,31,32,40 is in A2 and 15,21,22,26,27,30,31,32,40,43 is in M2

then
=AND(ISNUMBER(FIND(TRIM(MID((SUBSTITUTE(SUBSTITUTE(","&$A2,",",REPT(" ",255),{1,2,3,4,5,6,7}),",",REPT(" ",255),{1,2,3,4,5,6,7})),255,255))&",",M2))) will return TRUE.

What change would be made to that to find the first occurrence of the 7 string in column M with multiple strings?? Would M2:M102 at the end work?

Originally Posted by FDibbins

I just tried to =match() based on what you want to find, with the 2 large strings, and it gave me a result of 2. With short string in A1, and your 2 long strings in C1:C2, try something like...

=IF(MATCH($A$1,$C$1:$C1)=1,"1st Found","")

Copy down...let me know if this is what you want?

It worked, if I used $C1:$C1 instead of $C$1:$C1, when I copied down. But then it would still show a positive hit when I went back and changed the small string so that it shouldn't match. Not sure what is going on, but I will look at it more.

Originally Posted by mikerickson

You'd have a much easier time if you split all the numbers into their own cells, rather than working with delmited strings.

I can do it either way. I wasn't having any luck either with them in their own cells, I would actually prefer it that way. Any suggestions?

Btw, I never could get your other formula to return "True". I copied/pasted it, and entered it in manually and still got "False" when comparing just the 2 strings.

Originally Posted by mikerickson

Did you try entering it with Ctrl-Shift-Enter?

Yea, not making a difference. Must be doing it wrong.

Tried a few more times with the Ctrl-Shift-Enter. It's getting entered correctly as an array, but still showing false on just the 1 test. What was the idea on having the "strings" in multiple cells as opposed to one?

Originally Posted by mikerickson

Select the cell an use Text to Columns with a comma delimiter.

I understand that part. I was curious on your earlier post that I would have an easier time having it set up this way. What is the easier way solution? I have made recent progress with an extensive IF/AND/OR formula. But not sure that is the easiest way. I appreciate the suggestions by the way, I am narrowing it down.

Thanks for the additional suggestion, looks like a winner. I will make sure both forums are updated and shown as "solved" if it works as good as it appears it will. Thanks again for the input.

Crap. I looked at it to quickly. I have multiple 7 string and multiple 25 strings to test. I don't see a way to have that multiple testing. Your earlier solution with the commas is exactly what I'm looking for, but now with multiple cell strings instead of single cell strings.

I do see where cross-posting gets confusing. To update, I have a solution for single celled strings with spaces and commas. I can move forward with either and appreciate the input. I would still like to test a solution with the strings in multiple cells if someone has one.

The single cell with spaces solution is here:
http://www.mrexcel.com/forum/showthread.php?t=625583

I could not get the macro/vba section of my Developer tab to work, and have officially given up. I did take your comma solution and modified it for spaces, since it looks "cleaner" on the worksheet, and it's working great. Assuming that any "multiple cell" solutions would require a macro, I am calling this one solved. Thanks for the input.

For anyone interested a solution was posted for the "multiple cell" string issue on the other forum, here:

http://www.mrexcel.com/forum/showthr...=625583&page=2