+ Reply to Thread
Results 1 to 11 of 11

Keyword search

  1. #1
    Registered User
    Join Date
    02-27-2004
    Location
    Australia
    Posts
    16

    Talking Keyword search

    Hello

    Not sure this has been done by someone already.

    I am trying to create an input function using the Inputbox which can narrow the search as one types letter sequentially in the inputbox. The list from which the data is taken is predefined in a single column in a different sheet. The function would be similar to the Google search function - as one types the letter "e" in the inputbox a list of words ( from the predefined list ) beginning with "e" would appear in a drop down box. When the second letter "x" is typed a list beginning with “ex” would appear in the drop down box. When the third letter “c” is typed after “ex” a list beginning with “exc” would appear. As more letters are typed it is narrowing to the word being searched. Thanks in advance.
    Last edited by 4sharkfins; 10-22-2010 at 09:26 AM. Reason: Probelm Solved

  2. #2
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Kalgoorlie Australia
    MS-Off Ver
    Excel 2007/10
    Posts
    251

    Re: Keyword search

    Hello 4SF
    I don't know how to have it narrow the search down as you type, but you can narrow a search down several other ways, inc, filters, using sub groups, search function, Ctrl F, pivot tables (I not clued up on pivot tables) and others. The search function requires clicking out of the input cell before "searching" begins.
    I'm sure there is a way using VBA, but again I'm happy to record a macro, but I don't understand the writing of it.
    How big is the list, is it static?
    I have spread sheets with 30,000 rows on them and can brake them down to a couple very quickly.
    Regards
    Peter
    Why not attach a dummy copy of what you want

  3. #3
    Registered User
    Join Date
    02-27-2004
    Location
    Australia
    Posts
    16

    Re: Keyword search

    Hello peter

    I have attached part of the actual list. The whole list is static and in the order of 3000 rows in one single column. Note: word/s in each row resides in one cell only.

    Monomethylarsonic Acid
    % Ash (Dried Sample)
    % Ash (As Received Sample)
    Dibutyl Tin
    Mg meq
    Hydrogen Fluoride (HF)
    Magnesium Mg^++
    Ignition Temperature
    % LOI (Dried Sample)
    % LOI (as received)
    Monobutyl Tin
    % Moisture
    Drying Temperature (Ash/LOI)
    Purity H^^2SiF^^6
    Purity Na^^2SiF^^6
    Sieve Type
    Tribuyl Tin
    Temperature for Drying
    Chromium (III) by difference
    Mole Ratio*
    MPN Coliforms
    MPN E. coli
    Conductivity Flood Water
    Ionic Strength
    Sodium Absorp. Ratio*
    Hypophosphite (PO2)
    Orthophosphite (PO3)
    Report SWA results
    Turbidity Flood Water
    TSS Flood Water
    Zinc
    MW Report Comments
    Clostridium perfringens (Treated)
    Cryptosporidium oocysts
    Free Chlorine (Micro Water)
    Giardia cysts
    Micro Waters Flag
    Total Chlorine (Micro Water)
    Water Micro Comment
    Start Time/Date
    Turbidity (Water)
    Sample Blank
    Sample Counts
    Sample Corrected Counts
    Monitor Element Concentration
    Monitor Element
    Monitor Blank
    Monitor Counts
    Blank Corrected Counts
    XRF Scan Major Components
    XRF Scan Minor Components
    Aluminium by XRF Semi Qualitative
    Arsenic by XRF Semi-Qualitative
    Barium by XRF Semi-Qualitative
    Bismuth by XRF Semi Qualitative
    Bromine by XRF Semi-Qualitative
    Calcium by XRF Semi-Qualitative
    Cadmium by XRF Semi-Qualitative
    Cerium by XRF Semi-Qualitative
    Chloride by XRF Semi-Qualitative
    Cobalt by XRF Semi-Qualitative
    Chromium by XRF Semi-Qualitative
    Cesium by XRF Semi-Qualitative
    Copper by XRF Semi-Qualitative
    Dysprosium by XRF Semi-Qualitative
    Iron by XRF Semi-Qualitative
    Gallium by XRF Semi-Qualitative
    Gadolinium by XRF Semi-Qualitative
    Germanium by XRF Semi-Qualitative
    Hafnium by XRF Semi-Qualitative
    Mercury by XRF Semi-Qualitative
    Iodide by XRF Semi-Qualitative
    Potassium by XRF Semi-Qualitative
    Lanthanum by XRF Semi-Qualitative
    Magnesium by XRF Semi-Qualitative
    Manganese by XRF Semi-Qualitative
    Molybdenum by XRF Semi-Qualitative
    Sodium by XRF Semi-Qualitative
    Niobium by XRFSemi- Qualitative
    Neodymium by XRF Semi-Qualitative
    Nickel by XRF Semi-Qualitative
    XRF Scan Comment
    Phosporous by XRF Semi-Qualitative
    Lead by XRF Semi-Qualitative
    Praesodium by XRF Semi-Qualitative
    Rubidium by XRF Semi-Qualitative
    Sulphur by XRF Semi-Qualitative
    Antimony by XRF Semi-Qualitative
    Scandium by XRF Semi-Qualitative
    Selenium by XRF Semi-Qualitative
    Silica by XRF Semi-Qualitative
    Samarium by XRF Semi-Qualitative
    Tin by XRF Semi-Qualitative
    Strontium by XRF Semi-Qualitative
    Tantallum by XRF Semi-Qualitative
    Terbium by XRF Semi-Qualitative
    Thorium by XRF Semi-Qualitative
    Titanium by XRF Semi-Qualitative
    Thallium by XRF Semi-Qualitative
    Uranium by XRF Semi-Qualitative
    Vanadium by XRF Semi-Qualitative
    Tungsten by XRF Semi-Qualitative
    Ytterbium by XRF Semi-Qualitative
    Zinc by XRF Semi-Qualitative
    Zirconium by XRF Semi-Qualitative
    XRF Scan Trace Components
    Word Document for Inorganics
    NaHCO3 - calculated
    Na Meq

  4. #4
    Registered User
    Join Date
    02-27-2004
    Location
    Australia
    Posts
    16

    Talking Re: Keyword search

    Hello Peter

    Could not find the attachment button before. I attach the list as a file this time
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Kalgoorlie Australia
    MS-Off Ver
    Excel 2007/10
    Posts
    251

    Re: Keyword search

    G'day 4sf

    Please have a look at this.
    You will have to ENABLE MACROS, once open you can add the rest of the data to the list, push the SORT LIST button.
    Or key into the green cell the word you are searching for and press the FIND button, and I figure you may be able to figure out what the reset button is for.
    If you macro settings are to low,I assume you have 2003 change them by going to TOOLS,
    OPTIONS, MACRO SECURITY, and set it to medium, so you can choose to allow a macro or not.
    If for what ever reason you don't want to ENABLE MACROS you can still manually DATA SORT,and the FIND is just selecting the filter, and the RESET is just unselecting the filter.
    The search formula has nothing to do with macros. Just remember to use the GREEN CELL IS INPUT.
    You could also protect the sheet so you can't accedently stuff the formulas up, but allow you to select just that cell and to USE FILTER.
    Regards
    Peter
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-27-2004
    Location
    Australia
    Posts
    16

    Re: Keyword search

    Hello Peter

    Thanks for the very thorough reply. I will give it a go to see if it fits in with what I am doing. At least this is a start for me. Will keep you informed.

  7. #7
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Kalgoorlie Australia
    MS-Off Ver
    Excel 2007/10
    Posts
    251

    Re: Keyword search

    No probs
    Regards
    Peter

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Keyword search

    Hi 4sharkfins

    I suspect this is what you're looking for. If not, please ignore. If so, we can develop it further depending on your needs.

    John
    Attached Files Attached Files
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  9. #9
    Registered User
    Join Date
    02-27-2004
    Location
    Australia
    Posts
    16

    Re: Keyword search

    John

    Thanks. I will give it a go.

  10. #10
    Registered User
    Join Date
    02-27-2004
    Location
    Australia
    Posts
    16

    Re: Keyword search

    John/Peter
    Thanks for the suggestions and ideas. I adapted and plagiarised your ideas and codes to get it to work the way I wanted.

  11. #11
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Kalgoorlie Australia
    MS-Off Ver
    Excel 2007/10
    Posts
    251

    Re: Keyword search

    Hello
    Just realised that i had stuffed the "FIND" macro on the original attachment up, this should be better if its of any use. Ctrl F will do what you are after but this will group them all together.
    Green cell is the required text/word. Macros will have to be "enabled".
    As you add to the end of list hit the "sort list" button to put it into alpabetical order.
    You can protect sheet allowing you to edit the list and the green cell, also allow edit objects and use auto filter.
    Regards
    Peter
    Attached Files Attached Files
    Last edited by peterjuhnke; 12-22-2010 at 09:26 PM.

+ 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