words seach and copy records containing those words???

I am trying to find out if something like this can be done using the advanced filter or something.

basically I would like to check two columns(say Columns B & F) if they contains any of search words(say department names, MD-1, MD-52 & VA-23). B & F columns contain 0 or more department names separated by / like this MD-1/MD-34.
If any record contains those search words, I would like copy that record with specific column values( I don't need everything from that row, just a few specifica columns say columns A, B, F,U,Z)
Thanks much.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
The text below describes how to do various advanced filters on a table inlcuding filtering on text contained in a string. Once you have filtered the table as desired just select and copy the desired columns and only those rows that are visible will be copied.

The advanced filter function is similar to the AutoFilter function but allows much more complex criteria. Besides a data table a separate criteria range is used to create the complex filter criteria. A criteria range must contain, as it's first row, headers that match headers in the data table. The header values must match the data table header values exactly. Various conditions are placed under the headers to create the criteria. Examples of conditions are:


Multiple conditions can be placed in additional rows. The conditions are treated with OR logic:


In this case all records with color of red OR blue are visible and all others are hidden. To use AND logic use the same row and multiple columns:

Color   Price
Red     >10
Blue     <20

In this case all records with color = red AND price > 10 are visible as are records with color = blue AND price < 20.

Wildcard characters can be used to do partial matches. For example, to filter on the Part Number field for any part numbers that contain "abc" use the following filter criteria:

   Part Number

These are all of the wildcard characters that can be used:

   ? (question mark) Matches any single character, e.g., sm?th finds "smith" and "smyth"

   * (asterisk) Matches any number of characters, e.g., *east finds "Northeast" and "Southeast"

   ~ (tilde) followed by ?, *, or ~ matches a question mark, asterisk, or tilde respectively, e.g., fy91~? finds "fy91?"

To define an advanced filter, set up the data table and the criteria range, both with headers. Make sure the criteria range is above the data table or it may become hidden when filtering. Select the data table and choose the menu command Data->Filter->Advanced Filter. In the dialog box select the text field criteria range enter the range of cells representing the criteria range. (Note that if any completely blank row is included the filter criteria will not work as expected.) Click OK to filter the data table. To show all the records again choose the menu command Data->Filter->Show All.

Formulas can be used as criteria but certain restrictions apply. When using a formula the header in the criteria range must be blank. The formula must be entered as a valid Excel formula that evaluates to True or False. The first parameter must be a relative reference to the first row or the name of the column. Assume the following data table:

Color   Price
Red     10
Blue     20
Green  30

The following two formulas will find all records with a price greater than the average of all prices:


The second formula, when entered, will result in a #NAME? error - this is OK and can be ignored.

Other things to know about using advanced filters:

Only one advanced filter can be defined on a single worksheet.

When changing an advanced filter criteria the visible and hidden records are not reset. To reset the filter choose the menu command Data->Filter->Advanced Filter and click OK. To create an automatic refresh, name the data table "DataTable" and the criteria range "Criteria" and add the following code to the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, [Criteria, DataTable]) Is Nothing Then
      [DataTable].AdvancedFilter _
         Action:=xlFilterInPlace, _
         CriteriaRange:=[Criteria], _
   End If

End Sub

If the data table and/or the criteria range change in size then the advanced filter parameters need to be reset. Choose the menu command Data->Filter->Advanced Filter, modify the data table and criteria range parameters, and click OK.

Instead of filtering the table in place, the matching records can be copied to another location. To implement this feature, choose the menu command Data->Filter->Advanced Filter, select the radio button "Copy to another location", enter the destination range in the "Copy to" text edit box, and click OK.

To show only unique records, choose the menu command Data->Filter->Advanced Filter, check on the check box "Unique records only", and click OK. Two records are considered the same when all fields are equal.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dkim18Author Commented:
This is so much better and more info than from MSDN.
This is very helpful.
Now I don't even have to write up any VB to do this.
Thanks so much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.