Macro to lookup string in a cell

[lfc4life]

Hello,

I need a macro to lookup a string in a cell against a column on another worksheet. Attached is the sample spreadsheet of what I am looking for. "Sheet 2" has the master data or the column against which I want to do a look up. "Sheet 1, Column A" is what I want to look up and "Sheet 1, Column B" is the output which is "Yes" when the string is present in the master data and "No" when it is not.

So, for example in the attached spreadsheet, "Sheet 1, cell (2,1)" = "Apple Iphone 7 USA". Now this string contains "Apple" which is present in "Sheet 2, Column A" so the output will be "Yes". Similarly, "Sheet 1, cell (4,1)" = "Motorola Razr USA Oppo China", now none of these words is present in the "Sheet 2, Column A", hence the output is "No".

Thanks for all the help and let me know if you have any questions.

[jindon]

Try

Please Login or Register  to view this content.

[lfc4life]

Thank you. That works perfectly.
Since I am trying to learn VB, can you please explain the logic here?

[jindon]

In short, using Regular Expression to evaluate if the string in sheet2 appears to sheet1 cells in col.A.

I guess this is easier to understand, but not quite the same.
Difference is that Regular Expression will only find independent word like Apple, but Like operator also matches to Apples. etc.

Please Login or Register  to view this content.

[lfc4life]

Thanks a lot. Final question: What if i want output in column E instead of column B?

[jindon]

Better change whole code to store col.A and col.E separately, but easy change would be
1)

Please Login or Register  to view this content.

2)

Please Login or Register  to view this content.

[lfc4life]

Sorry, I forgot to clarify.
Can you suggest changes in the second code you provided to get output in column E?

The first code is difficult to understand.
Thank you.

[jindon]

2) replace all a(i, 2) with a(i, 5)

[lfc4life]

Awesome and thanks.
I hope i can develop skills like you.