Match a small string against a large string

newbie1973 said:

I have not used UDF's before, so I'm looking forward to trying it. The cell address reference is perfect. Unfortunately, I just installed recently my Excel2010 on a new computer, and I can't get the ALT/F11 to work, it was working on the old machine. Google shows a number of hits on the issue, so I guess that's my next project, either that or use the old machine.

Regardless, I love the idea of using a UDF on this and will report back.

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.

Thanks again for the UDF idea.

Click to expand...

I don't have XL2010 myself, but perhaps you can find the equivalent to the following from XL2007 and implement it on your system. In Excel Options for XL2007, there is an item labeled "Popular" which has its own dialog page and on that dialog page is a check box labeled "Show Developer tab in the Ribbon". Putting a check mark in that check box and clicking OK adds the indicated new tab... on that tab is a button labeled "Visual Basic" which takes you directly into the VB editor. Can you find the equivalent on your system and, if so, does it take you into the VB editor?

Oh, one thing I forgot to mention about the UDF that I posted... the SearchMe range is not restricted to a single column nor a single row for that matter, the range can be any rectangle range of cells (providing the cell the formula is in is not part of that range, of course).

I was able to add the "Developer Tab" via options, via a "Customize Ribbon" tab. Once I open the Developer Tab however, about 1/2 the options are greyed out, including the Virtual Basic and Macro buttons. From some search suggestions, I made changes to my Trust Center settings, but that made no change to the Developer Tab. I do now have a nice yellow triangle warning me about my Macro Security.

newbie1973 said:

I was able to add the "Developer Tab" via options, via a "Customize Ribbon" tab. Once I open the Developer Tab however, about 1/2 the options are greyed out, including the Virtual Basic and Macro buttons. From some search suggestions, I made changes to my Trust Center settings, but that made no change to the Developer Tab. I do now have a nice yellow triangle warning me about my Macro Security.

Click to expand...

Hmm, does anything at this link help?

http://support.microsoft.com/kb/282847

Sure looks and sounds like my issue. I may have to re-install the program. I don't remember proactively selecting "not available" for the VBA when I installed it the first time.

I could not get the macro/vba section of my Developer tab to work, and have officially given up. I did take mikerickson's 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 all the helpful input.

Edit: I guess you don't mark them as such on the forum, but "solved" it is.

Assuming that A2:Y5 contains each set of 25 numbers, AA2:AG2 contains the 7 numbers, and that no numbers repeat, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=MATCH(TRUE,MMULT(ISNUMBER(MATCH($A$2:$Y$5,$AA2:$AG2,0))+0,TRANSPOSE(COLUMN($A$2:$Y$5)^0))=7,0)

Wow. A preliminary testing shows it working perfectly. I will play with it some more tomorrow, but it looks great. I'm amazed what you guys can come up with. Thanks.

newbie1973 said:

Wow. A preliminary testing shows it working perfectly. I will play with it some more tomorrow, but it looks great. I'm amazed what you guys can come up with. Thanks.

Click to expand...

You're very welcome!