+ Reply to Thread
Results 1 to 9 of 9

Need help creating a search engine that "filters" rows based on the contents of a column.

  1. #1
    Registered User
    Join Date
    09-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Need help creating a search engine that "filters" rows based on the contents of a column.

    Gentle wizards/sorceresses of excelforum,

    I am absolutely useless for programming, so much so, in fact, that I don't even know how hard or easy this request would be to fill. This could be the equivalent of asking you to kindly create a rocket ship using parts of my car, or asking you to tie my shoes for me: I don't know. I've got a spreadsheet I use at work that's roughly 30,000 lines long (and growing every day) that I have to search hundreds of times per day for specific, unique commodity lists my company deals with. I'm looking to make that search easier.

    Sample.xlsx

    What I'm looking for is a formula that turns the cell containing the word "description" into a search field that will filter out any row that doesn't contain EACH term I enter in the field (including words *containing* the words I enter). For instance, if I entered the word "brake" into the search field, I would want the commodities corresponding to codes 333333.3333, and 555555.5555 to appear and all other commodities to be hidden. If I entered the words "auto parts," I would want only commodities 55555.5555 and 777777.7777 to appear. I need both "code" columns of any row that is a match to remain visible as well. When I clear the contents of the search cell, I'd want the entire spreadsheet to return, unfiltered. Basically, I'd like a cell that operates exactly like Excel's filter for "Containing X *and* Containing Y" does, only it would allow for any number of words to be entered. If that's impossible, 10 words would be sufficient. Some of the descriptions in our spreadsheet are very lengthy, and keying in on them is difficult.

    If I've been unclear in any particulars, please feel free to ask questions. I do appreciate any help that you might provide. I pray that I haven't broken any of the forum rules with this thread, and if I have, please let me know.

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Need help creating a search engine that "filters" rows based on the contents of a colu

    My first reaction was to laugh because the task is so simple. But when thinking about it really is rather advanced. It just happens to be that Excel has a rather advanced and very handy feature called Autofilter. Mark a cell somewhere in the middle of your datarange and click the filter button as you can see on the picture.
    Excel automatically defines the boundaries for the data set as the first empty column and the first empty row. It also assumes that your top row consists of headers that are not to be filtered or sorted.

    By clicking the little arrow in the cell of your choice a menu of choices appear. Click the one that says Text Filters and then the option Contains. You can now type in brakes there and Autofilter does it's magic. See attached image.
    Autofilter.jpg
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    09-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need help creating a search engine that "filters" rows based on the contents of a colu

    Thanks for responding! I opened up Excel with my home PC and immediately saw the feature that you're referring to. But with my home PC, I have Office 2010. At work we have Office 2007, and that feature isn't something I recognize from that version. Are you sure it's available for 07? Might it be an setting I have to activate?

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Need help creating a search engine that "filters" rows based on the contents of a colu

    It was available back in 2003 and probably long before that. It could be that the controls are located slightly different in Excel 2007 but I'm confident you'll find them.
    My guess is that this is the most utilized feature in Excel and has been for many years.

  5. #5
    Registered User
    Join Date
    09-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need help creating a search engine that "filters" rows based on the contents of a colu

    I'll certainly try this tomorrow morning. If this is something it turns out I've had access to this whole time, I'm going to feel pretty silly. Thanks!

  6. #6
    Registered User
    Join Date
    09-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need help creating a search engine that "filters" rows based on the contents of a colu

    You have DRASTICALLY overestimated my abilities. I can't find any method of activating the search option you point out in 2010 using 2007. I've used trial and error, the Excel help menu, and online rescources. Does anyone who's familiar with 2007 know of an onboard search feature like the one I described in my original post? Or how to create the formula that will execute that function?

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need help creating a search engine that "filters" rows based on the contents of a colu

    Click on the Data tab, then on the Filter icon (shaped like a filter funnel, in the middle of the ribbon). This will give you the drop-downs on each of your headers.

    I can remember this facility being available in Excel 95 !!

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    09-25-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need help creating a search engine that "filters" rows based on the contents of a colu

    I definitely have the filter options available, its just a question of being able to filter by more than 2 words.

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Need help creating a search engine that "filters" rows based on the contents of a colu

    Ah, I only read part of your first post, and then some of the others. Sounds like Advanced Filter would be more useful to you. I'm about to go out, but you can find out all you need to know from Debra Dalgleish's site, here:

    http://www.contextures.com/xladvfilter01.html

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1