Boolean search - macro

[kf1511]

Hi Guys,

I have a database with text in multiple cells. I want to have a search functionality to find certain words, for example to be able to find "orange" OR "yellow", or "orange" AND "yellow". What's more, I need excel to return results, of where these words are. So let's say it's my database:

A1 A2
Fruits This lemon is yellow.
Books This book is orange.

Now I'm putting that "orange OR yellow" and the excel is giving me: Fruits, Books.
I'm putting "orange AND yellow" and the excel is returning 0 answers.

Is there a chance to code something like that?

[6StringJazzer]

Definitely can be done but it depends on how your data is organized. Also, how do you want the use to enter these queries? Please provide a file with sample data and a samples of queries.

BTW it would be better to use a real database like Access for this, because this kind of thing is built into SQL queries.

[kf1511]

Example workbook attached. Unfortunately I have to use Excel for this, not Access

[6StringJazzer]

I get the idea of what you want to do but your sample data is just the same record over and over, so not useful for testing.

If you want the user to be able to enter a free-form boolean expression like

(orange OR yellow) AND blue

then you have to have a grammatical parser and that is more work than I am able to do for you here.

If you structure it so the use can enter some search terms and pick OR and AND is a little more manageable. An example of this is how Excel Autofilter works.

and or.jpg

[kf1511]

Thanks for this! I edited the sample workbook. Yes, the Excel Autofilter works the way I want, but as you mentioned, ideally if they also could put OR and AND in the same time

[kf1511]

Does anyone have any idea?

[Akuini]

Does anyone have any idea?

Try this:

• The operators " AND " & " OR " must be capital letter, but without "(" and ")". For example:
  red OR blue AND book
  red AND blue AND glove OR ball
• Type the keywords in C2 then hit commandbutton.
• I'm assuming that data in sheet Database col A (Title) are unique.
• The result (start at F6) may or may not be have the same order as data in col A (Title).

Please Login or Register  to view this content.

[kf1511]

Works perfectly, thank you so much!!!

[Akuini]

You're welcome, glad to help & thanks for the feedback.

[Akuini]

I forgot something, how big is your data?
The code uses Application.Transpose which has a limit of 65536 item to process. So, if the result is more than 65536 items then it will generate a wrong result. Unfortunately when this happens there aren't any error message.
So, if that could be that case then we need to amend the code.

[kf1511]

Akuini, the data will be getting bigger with time but I don't think it will exceed 65536! Is that a number of rows?

[Akuini]

I don't think it will exceed 65536! Is that a number of rows?

Yes.
It's ok if data won't exceed 65K rows.