Find one or more words in another column that could have one or more words

[amy22x3]

I just inheriited a spreadsheet with over 30,000 rows with Quote Names and within those quote names is always the Retailer name. When we give our customer a quote number for a discount to give their customer (the retailer) we put the year, quote code, retailer name and length in it. For example "2017 - Q325% - Wal-Mart - 6MO" is a typical quote number we give to customers.

What I'm trying to do is find any new Quote Numbers that I need to track, so once a week I need to comb through all the current quote numbers to find all the new ones. So I've been able to filter out all the current ones I track by using a vlookup, but finding new ones is difficult.

What I have is a list of all the Retailers we track: Wal-Mart, Kroger, Meijer, Home Depot and so on and once a week when I pull current quotes from MSBI new quotes show up but the retailer is always in the middle of the quote so I haven't been able to find a way to find the retailer in the new quotes and give a True or False Result.

Tab 1 Column A Tab 2 Column A Result needed
Wal-Mart 2017 - Q325% - Meijer - 6MO TRUE
Kroger 2017 - Q305% - Home Depot - 6MO TRUE
Meijer 2017 - Q355% - Steinmart- 6MO FALSE
Home Depot 2017 - Q325% - Wal-Mart - 6MO TRUE

Formula I've tried and all variations I can think of:
=countif(Tab1 A:A,Tab2"*"&A1&"*")>0

That formula would work if the Retailer name was at the end or beginning, but it never is and the character length isn't the same due to the Retailer names being different lengths.

Thanks for any help you can give!

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[leelnich]

Hi all- How about:

Please Login or Register  to view this content.

...OR

Please Login or Register  to view this content.

[amy22x3]

I'm trying, but all I get is what appears to be a tiny line pop-up box. And I went to Advanced Edit in my original post to attach. I don't know what I'm doing wrong.

[amy22x3]

I tried both and tried Ctrl-Shift-Enter too.

[amy22x3]

Ok, I found the directions on how to attach.

[leelnich]

Try this in B2, copied down:

Please Login or Register  to view this content.

Named range Retailers_Tracked = 'Retailers Being Tracked'!$A$2:$A$5 in your sample workbook. The following link shows how to set it up for your actual data.
How to define Named Ranges

[amy22x3]

It doens't work. It's giving me false positives.

[amy22x3]

Ok, I got it to work in my play spreadsheet, but not my real one. What is the 3 after the A2 in your formula? Wow, it literally doesn't work again. I don't understand what I could be doing to make it work and then not work.

[leelnich]

Here's my copy. The post#7 formula started looking for retailer names at character 3 of the relevant New Quote, thereby skipping quotes that BEGIN with the retailer name. If 3 characters is not enough, try 10 characters:

Please Login or Register  to view this content.

Or you could start at the first "-":

Please Login or Register  to view this content.

Perhaps you could upload a version with the problem quotes in it.

[amy22x3]

It's just not working for me. The row in red should be a FALSE, but it's true.

Edit: I updated the spreadsheet to take out retailer names in the front, because I think I was complicating the matter. The Retailer name can be anywhere in the the Quote text string.

[leelnich]

GOT IT. You included blanks in your named range, and SEARCH with an empty string always succeeds. Try this:

Please Login or Register  to view this content.

I divided by the length of the retailer name. So SEARCH returns an error if the name isn't found, while SEARCH/LEN returns an error if the name is blank. ( If it's blank, length = 0, causing an #DIV/0! error.) In each case, ISNUMBER returns FALSE.

[amy22x3]

Ok, so this is working on my test data, but not my real spreadsheet. Sometimes the Retailer name can be the first thing listed and when that happens I get a FALSE and also a lot of TRUE's for things that aren't. For example:

New N/A Quote Names Retailer Match?
1100 15th Street TRUE
Advocate Medical Group (AMG) Huntley Clinic TRUE
Battlefield Mall Employee Entrances TRUE

Blanks aren't allowed in Named Ranges? That explains why I kept getting different results between choosing the Column or the exact range.

[Richard Buttrey]

Blanks aren't allowed in Named Ranges? That explains why I kept getting different results between choosing the Column or the exact range.

In general you should use dynamic range names to avoid the problem of either a named range being too long (i.e. with blank cells) or too short, i.e. after new rows have been added.

Define your range name as

Please Login or Register  to view this content.

[amy22x3]

I changed the 10 to a 1 and the formula is working great and the false TRUE's are because there's text in the Quote Name that's similar to the Retailer Name. For example I have a quote for GAMC Blah Blah that should be FALSE, but because AMC is a retailer I'm getting a TRUE. How do I add in a bunch of <> to this formula?

[leelnich]

Blanks aren't allowed in Named Ranges?

Blanks ARE OK so long as the formulas that use the named range allow for them - as in Post#12.

I changed the 10 to a 1 and the formula is working great...

That shouldn't work, you'd be finding the leading names. I thought you were trying to avoid that.

How do I add in a bunch of <> to this formula?

We may be able to use a list of exceptions, similar to the Retailers List. I'll think about it. Perhaps you could compile a list of all the problem combinations you've discovered and post it for test purposes.

[FlameRetired]

Another way.

Ignores the leading names by initiating the strings at "- Q".

Please Login or Register  to view this content.

and this is another way to define dynamic named ranges.

Please Login or Register  to view this content.