Search multiple keywords to filter column

[Bat71]

Hi

I have a spreadsheet that will be updated weekly with info drawn from a tender/contract website.

This will be going to multiple people and they would like to filter according to keywords in the tender 'description' box.

I have managed to set up a VBA/macro search box where they can input a keyword and the list will be filtered accordingly, for that one word.

I'd like to be able to go a step further and allow them to click on a macro button that would filter the list for ANY of a list of keywords in the description column.

e.g if they were interested in IT related contracts they would like to see only contracts containing any of the words "Network" "Data" "IT" "Computer" "Cloud" etc. etc.

Is there an easy way to do this?

I'm guessing it would use Advanced Filter rather than autofilter (which I used for the basic search) but not sure of the possibilities.

Many thanks in advance

[Rob342]

Hi Bat71
You need to post a copy of the workbook as a test sample and possibly a copy of what you want the results to look like
Rob

[Bat71]

Hi Bat71
You need to post a copy of the workbook as a test sample and possibly a copy of what you want the results to look like
Rob

Hi Rob

I can't really post a copy as the information which I have pasted into the workbook is from a paid website.

I'm really just asking is there a way to filter a column based on a list of keywords.

So each contract has a description field, and I want to be able to look for any word (from a list of words) that appears in that description and then filter that column.

e.g. my keywords are "IT" "Cloud" "Artificial Intelligence" "Networking" and I would like the filter to only return those rows where one or more of these words appear, so that the person who is only interested in IT contracts will only see those.

I might be trying to do something that isn't really possible!

[Rob342]

Hi
Which column do these key words appear and what sheet
or a copy of the code you are using for your search routine

Rob

[大灰狼1976]

Hi Bat71!
Autofilter can also implement the functions you want,
But the lower version of EXCEL may not be applicable, not sure.
please refer to the attachment.

[大灰狼1976]

You can also used wildcard character like "*B*,*E" and so on.

[Bat71]

Hi 大灰狼1976, that is what I need!

I will have a go at putting this in sheet.

Many thanks

Matt

[大灰狼1976]

You're welcome

[Bat71]

Hi

I have tried this and works in your example, but the descriptions I have are long lists, e.g. like this:

Urban Planning, Urban Regeneration, Masterplan, Town Planning, Community Engagement, Area Development, Spatial Development, Economic Regeneration, Community Planning, Spatial Planning, Planning Consultants, Regeneration Partner

So for example, if i wanted to filter those descriptions which mention the keyword 'Planning' or 'Development', is this possible?

[Artik]

Bat71, read post #6 carefully.

Artik

[Bat71]

Ah, ok, will try it out!

[Bat71]

Bat71, read post #6 carefully.

Artik

Works!

Thanks all

[Bat71]

Just one more quick question, if I want to keep the keywords on a separate sheet, just to keep things tidy, what do I need to add to this line?

arr = Split([r1], ",")

[Bat71]

Also, I can only seem to add two (wildcard) keywords for some reason, any more than that and the search stops working

[Bat71]

I've just read that 2 wildcards might be my limit!

[Artik]

what do I need to add to this line?

arr = Split([r1], ",")

arr = Split(Worksheets("OtherSheetName").[R1], ",")

or

arr = Split(Worksheets("OtherSheetName").Range("R1").Value, ",")

Also, I can only seem to add two (wildcard) keywords for some reason, any more than that and the search stops working

With this method, you can filter up to two words.


..::Edit

You can also use the advanced filter. On the example given by 76 1976:
1. F1 cell - empty!
2. in F2 - F4 consecutively

 = FIND ("RI", A2)
= FIND ("CO", A2)
= FIND ("TT", A2)

Do not worry about the fact that formulas return an error.
3. Define an advanced filter, as a range of criteria to indicate F1: F4.


Note, the FIND function is case-sensitive! ::..

Artik

[Bat71]

Thanks, is there a workaround for more than 2?

[Artik]

Read my Edit above

Artik