Using Advanced Filter: problem with using wildcards

I'm working with 6 columns of data and 1000 to 300,000+ rows. Column A (Company name) Column B (address) Column C (city) Column D (zip) Column E (phone) Column F (type of company). I'm using Column J for my keyword filter set.

I'm cleaning up a database of construction companies. I'm trying to shrink the list by filtering by business names to eliminate everything that is not construction based. I get the jest of how the filter works, but the wildcards are messing with me. I'm having difficulty eliminating certain words. For example, I try to eliminate the words "law" and "lawyer", but keep the word "lawn".

I tried '=law and '=lawyer? but it doesn't work. It's happening with other words as well. Like "realty" and "body". I think i figured out that using two asterisks along with the equal sign, that they basically cancel each other out. '=*word* is that correct?

So far i've got 250 something keywords i want to filter for. The further i get into it, the more words i add to the list. The list i was handed when i started the position a month ago was 50 words long.

So basically, i need help fixing the word difference problem.
I'm trying trying to be more efficient, but the more i try, the less efficient i get. :-/

I've attached the company and keyword sheet.

I appreciate any help that anyone can provide.

I'm confused.

But i'll try and answer you. I have the keyword list that i'm using to scrub the business info list for unwanted companies. Take keyword list and throw them in column J on the Business Info sheet and use same name as column A (as i'm wanting to sort the list based on info in the company name). I need to figure out how i can get all variations of a single word/or phrase.

Does that make any sense what so ever?

Thanks for the help.

I'm not familiar with Array's. Will this help distinguish between "law" "lawyers' and "lawn"? the way i was going about it, when filtering for law and lawyers, it would pick out lawn as well and i'd have to go through 10k+ records and sort through by hand. Does that make sense?

Also, could you reccomend where i could find more information on array's so that i can figure out exactly how the above formula works?

alright, i am now thoroughly confused. I tried copy and pasting the formula, but it just gives me a value error. i'm not even sure how it's supposed to work. do i still have to select all of column a? or do i just copy and paste the formula in any cell and it will do it's thing.

My head hurts.

Hemesh,

THANK YOU! you are a rockstar.

It works great.

Hope you have a wonderful weekend!