+ Reply to Thread
Results 1 to 19 of 19

Excel Search Tool to find entries but words in search box are in different order

  1. #1
    Registered User
    Join Date
    03-06-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    6

    Excel Search Tool to find entries but words in search box are in different order

    Hello all,
    I am new in this forum, but I hope there's someone who can help me.
    I am trying to conduct a search engine in excel, to retrieve entries containing the words in my search box. I managed to build a search box to type in words and show the entries which contain the words. The use of wildcards (*) is possible as well.
    But what I did not manage and could not find any help searching the internet is, to find entries which contain the words in my search box but in a different order.
    For example if I have the entries: "I need help" and "help is needed" and I type in my search box "help*need" that both entries containing the words are shown. In my case, only the second entry "help is needed" is display.
    Is this possible and could please anybody help my?
    I upload an example excel sheet with my search function, my search box is yellow.
    Thanks a lot in advance!!
    If you need any further information, just tell me.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel Search Tool to find entries but words in search box are in different order

    In your attached sheet, you showed "I" as your seacrh term. Will they REALLY be as simple as a single character?

    If not, try this:

    =IFERROR(INDEX(E:E,AGGREGATE(15,6,ROW($E$2:$E$8)/(ISNUMBER(SEARCH($B$1&" ",$E$2:$E$8&" "))),ROWS($1:1))),"")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Excel Search Tool to find entries but words in search box are in different order

    For example if I have the entries: "I need help" and "help is needed" and I type in my search box "help*need" that both entries containing the words are shown. In my case, only the second entry "help is needed" is display.
    This requires the search words i.e "help" and "need" to be found as individual searches, not as a string. So the search is find "help" AND "need".

    What about "help" OR "need": is this a requirement ?

    Probably need VBA to do this.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Excel Search Tool to find entries but words in search box are in different order

    See attached:

    Column B has Glenn's formula, Column C has VBA function and Column D has VBA "Change_Event": the latter is invoked by any entry in B1.

    B1 is named range "Search_parm" and the names list is named range "Names"

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    for column C

    select range C2:C11

    type =TRANSPOSE(get_strings()) in function (fx) field then

    ...confirm by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    output is any "name" containing ALL words in search field (words separated by blank)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-06-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    6

    Re: Excel Search Tool to find entries but words in search box are in different order

    Hello,
    thank you both for your quick responses!
    John, thank you for the VBA code. I never used VBA before. I tried to insert both of the codes as VBA modules in the Editor and tried to run the script, but in my worksheet it did not work out, because I guess I did not do it right. I copied the code for the function Get_strings as well as a module in the VBA editor, but it does not appear in the Macro dialog box. But when I type in TRANSPOSE(get_strings()) it returns #VALUE error. I changed the cell references in the code. Do you have any idea what could be my mistake?
    Best regards

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Excel Search Tool to find entries but words in search box are in different order

    The "Worksheet_Change" code goes in the sheet "Tabell1" (see file I posted)

    Right click on tab, "View Code" and copy/paste code.

    To use the FUNCTION:

    Select the range of cells where you are placing the function then in the formula box type =TRANSPOSE(get_strings()) then hit Ctrl+Shift+Enter together (as per my notes in #4)
    Last edited by JohnTopley; 05-03-2018 at 08:23 AM.

  7. #7
    Registered User
    Join Date
    03-06-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    6

    Re: Excel Search Tool to find entries but words in search box are in different order

    Hi John,
    thanks again! I think now I could make it work out. Thanks so much for your quick help!

  8. #8
    Registered User
    Join Date
    03-06-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    6

    Re: Excel Search Tool to find entries but words in search box are in different order

    Hello John,
    sorry but I am really struggeling. I tried to implement it into a different worksheet, but with the same search funktion. I changed the named range of B1 to "search_phrase" and the named range in column D to "elementray_exchanges", but I changed the names in the codes as well. I also changed the range of the search results in the code of "Worksheet_Change" from "D2:D100" to "C2:C4029" and in this sheet the search function is not working and I don't know why. I attached the sheet below.
    Would it be possible if you could check the sheet for me?
    I am really sorry for bothering so much and appreciate every help I can get. Thanks again!
    Best regards!
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    04-14-2014
    Location
    Philippines
    MS-Off Ver
    2007, 2010, 2013, 2016.
    Posts
    164

    Re: Excel Search Tool to find entries but words in search box are in different order

    @Glenn
    Hi all.
    I find Fib's work interesting. Fib, sorry for the interruption.
    I'm using excel 2007 and an Xlfn prefix was added to your formula. What should be change in the formula?
    thanks

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel Search Tool to find entries but words in search box are in different order

    That's because AGGREGATE doesn't exist in Excel 2007. Use this array formula, instead:

    =IFERROR(INDEX(E:E,SMALL(IF(ISNUMBER(SEARCH($B$1&" ",$E$2:$E$8&" ")),ROW($E$2:$E$8)),ROWS($1:1))),"")

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Excel Search Tool to find entries but words in search box are in different order

    @fib,

    I have put the worksheet_change code in sheet "Elementary Exchanges"

    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Excel Search Tool to find entries but words in search box are in different order

    @getafixkwik,

    i appreciate you have a reply from Glenn but ...

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    In future, start a new thread.

  13. #13
    Forum Contributor
    Join Date
    04-14-2014
    Location
    Philippines
    MS-Off Ver
    2007, 2010, 2013, 2016.
    Posts
    164

    Re: Excel Search Tool to find entries but words in search box are in different order

    @John. Noted and Sorry.
    @Glenn. it works. i changed the range to column E:E, calculation slow down. this is the first time i saw excel slow down. anyway, thank you for the help.

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel Search Tool to find entries but words in search box are in different order

    In the INDEX part, the whole column reference is OK. In the SMALL(IF bit, it will be a disaster. use the exact range. That is why I did it the way that I did...

  15. #15
    Forum Contributor
    Join Date
    04-14-2014
    Location
    Philippines
    MS-Off Ver
    2007, 2010, 2013, 2016.
    Posts
    164

    Re: Excel Search Tool to find entries but words in search box are in different order

    Noted Glenn. just an experiment. i narrow down the range of search and it is normally quick. Thanks again.

  16. #16
    Registered User
    Join Date
    03-06-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    6

    Re: Excel Search Tool to find entries but words in search box are in different order

    Hello,
    unfortunately I have another question. How can I adjust the VBA code for Worksheet_Change if I want to use this code for several worksheets in one workbook? I changed the names of the named_ranges, both for the search phrase and the lookup column, that they are unique for the whole workbook and inserted them in the VBA editor for each worksheet. Do I also have to change the label in the first row? "Private Sub Worksheet_Change(ByVal Target As Excel.Range)" Can this only be used once in one workbook?
    Thanks again so much for your help!

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Excel Search Tool to find entries but words in search box are in different order

    Worksheet_Change can go into every worksheet: just click on worksheet tab, "View Code", copy/past code.

    Change ranges as required.


    Please Login or Register  to view this content.
    The higlighted ranges need to changed to those for each sheet i.e UNIQUE named ranges

  18. #18
    Registered User
    Join Date
    03-06-2018
    Location
    Germany
    MS-Off Ver
    2016
    Posts
    6

    Re: Excel Search Tool to find entries but words in search box are in different order

    Thank you again! I realised my mistake. Sorry for asking so many questions and again thanks a lot!

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: Excel Search Tool to find entries but words in search box are in different order

    No apology necessary: you are on a learning curve so it is quite natural that you (or anyone) should ask questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Search Bar that Executes Search Based on Radio Button and Key Words
    By pomo2016 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-06-2016, 09:26 AM
  2. Replies: 4
    Last Post: 05-20-2014, 10:03 AM
  3. [SOLVED] Search box in Excel to search key words in sentences in multiple worksheets
    By fernandoii676 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-05-2012, 01:42 PM
  4. Replies: 2
    Last Post: 10-19-2012, 11:11 AM
  5. Replies: 1
    Last Post: 06-05-2012, 01:39 PM
  6. Replies: 1
    Last Post: 04-04-2012, 07:15 PM
  7. [SOLVED] Binocular search tool, Excel, loses column designation at "Find"
    By Little Rock Ette in forum Excel General
    Replies: 1
    Last Post: 11-16-2005, 01:52 PM

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