Extract Rows based on Key Words

I have a worksheet with over 200k rows of data. Column A has 3rd party vendor names (text) with no vendor codes. The other columns are spend data and dates.

I would like to categorize all similar companies by the vendor name. For example:
column A entries:
Law Group ABC
XYZ Systems
A & A Lawyers

I want to develop something that runs through column A and pulls all entries with the word "Law" in it. Ideally, the entire row of data will move to a bucket where all law spend will aggregate. I would like to run multiple key word searches on the same data in order to extract all law spend (for example include the word "attorney" and "legal").

Any recommendations on the easiest way to perform these key word extractions on a repeatable basis? The function will be performed on 2-3 similar data sets.

Thank you,

Brent

Brent,

I have messed with an example sheet. Check it out and let me know if that is what you were looking for.


Simeon

Simeon,

This is very close. The only issue with this model is that if I search multiple key words, the new search replaces the results from the previous search. Is there a way to add results rather than putting everything that was found on row 4?

Thank you for your help.

Patel,

I am working through this solution today. The concept makes sense to me but I am working through a couple complexities. I will give you an update later today.

Thank you,

Brent

Brent,

Let me do a little more tinkering and get back to you.

Simeon