Hello,
I have a problem with searching through records to find specific rows. For example, lets say I have a list of book names. Each record has an ISBN, Title, and Author. I want to be able to enter a search phrase "Harry Potter" into a cell then filter the list to only show the records that contain the words "harry" and "potter". I think advanced filtering needs to be used, but I'm stuck on how to do it. How do you know what column to search in. Any help with this would be much appreciated.
Last edited by Yoruichi; 04-29-2010 at 03:12 PM.
Hello Yoruichi,
Welcome to the forum.
To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook. - So, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Doing this will ensure you get the result you need!
Thank you for the advice,
I've attached a workbook with an example of what I'm trying to do
Entering the word "Harry" into a cell should return all records with that specific word, whether it's a part of the title or author.
just use a helper column say col d
and for your example
put in d7
=ISNUMBER(SEARCH($F$1,B7&C7)) and drag down
now put harry in F1 then auto filter col d on true
Last edited by martindwilson; 04-25-2010 at 07:36 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Your solution solved my problem. Thanks for the help.
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hello again,
I've found out that it didn't solve my problem exactly. If I want to search for two words, for example: 'Harry' and 'Secrets', the solution you gave returns the result as FALSE (see attached workbook).
It appears as though the two words have to be next to each other for it to return true. How can this be fixed so that it returns true for Harry and Secrets?
Here's my suggestion...this allows up to 3 words to be put in the search box. It breaks those words out in column F and the key works properly with the array formula in column D.
A bit complicated, but it does the job.
Many thanks.
i'd use advanced filter with wild cards
see
http://www.contextures.com/xladvfilter01.html
in attached i used col f as the filter criteria
Thanks martin, but I wanted to be able to enter a few search words into the same cell.
JBeaucaire, your method does exactly what I've been trying to do, however when I tried to copy your way into my worksheet, it doesn't give me the same results. Could you please have a look and tell me what I'm doing wrong. I'm quite puzzled about it.
Again, many thanks to both of you for your help.
The formula in E16 is an array formula. After entering the formula, confirm it by pressing CTRL-SHIFT-ENTER, not just ENTER.
When you CSE that formula you will see braces { } appear around your formula to confirm the array is active. Then you can copy that cell downward to activate the array in the other rows.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks