+ Reply to Thread
Results 1 to 35 of 35

fuzzy search in a list

  1. #1
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    fuzzy search in a list

    Hi! I have an issue with an excel-based tool that i am developing. There is a list of about 400 chemicals that should be available to a user to choose from (now in a drop-down menu, but not vital). As those are chemicals, users often are trying to enter the names as they know them and not exactly as they are present in the list. Client wants a sort of a fuzzy search for them. As ideally imagined: put cursor into the cell, type the name of the chemical, and a one/two/some the most closely matching appear as an option to enter. The trick is that the tool is to be distributed in many different companies with different policies, so the client doesn't want to use marcros or add-ins that need administrator rights to install. Any ideas anyone please? would be highly appreciated! =)

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    ...., so the client doesn't want to use marcros or add-ins that need administrator rights to install.
    If that would rule out xlsm-files then I guess you would be stuck to use a list with all kinds of synonyms/spellings.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: fuzzy search in a list

    Can you give an example of 1 of the more widely named chems?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    Re: fuzzy search in a list

    Quote Originally Posted by Tsjallie View Post
    If that would rule out xlsm-files ...
    Hm... Actually don't know what their position on that... What would be the suggestion? =) A list with all kinds of synonyms and spellings sounds terrifying ...

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: fuzzy search in a list

    .xlsm files are excel files that contain macros/VBA

  6. #6
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    Re: fuzzy search in a list

    Starting from cellulose that can be also cellulose fiber/ cellulose fibre/ cellulose additives, to something completely crazy with numbers and scary words in names (with thus a lot of opportunities to screw it), and to china clay, which has a lot of synonyms like natural china clay, synthetic china clay, aluminium silicate, Mica pigment or kaolin...

  7. #7
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    Re: fuzzy search in a list

    Quote Originally Posted by FDibbins View Post
    .xlsm files are excel files that contain macros/VBA
    =/ oh, of course they are... silly me... no, i can't deliver the tool in .xlsm

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: fuzzy search in a list

    I'm guessing this has been done manually (with some mind reading) for some time and there is a lot of data available from which to construct a paired synonyms lookup. Can you give us a sampling (copy and paste?) in upload that includes some of the "scariest" data? A hundred or so might give us something to work with.

    If you are not familiar with how to upload:


    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
    • be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  9. #9
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    Re: fuzzy search in a list

    Here is a file imitating the situation.
    The first tab is with the list (as in the real file there are more data attached to every article, the format isn;t really changeable).
    The second tab is with the "user interface", and this one is as changeable as long as it does the work and gets friendlier to a user =)
    Attached Files Attached Files

  10. #10
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    As your stuck to standard Excel functionality I think Autofilter comes closest to what you looking for, but still far from spot on.
    You can have the list of chemicals in seperate sheet with autofilter on.
    But users would still have to manually copy the found chemical to the "user interface". Not very friendly.

    Alternatively you couldn't use macros, but save the workbook as .xls (Excel 97 - Excel 2003 format), but do not consider this as an advice.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: fuzzy search in a list

    I am wondering if the latest solution offered in this thread might show a way forward: https://www.excelforum.com/excel-gen...ed-please.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  12. #12
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    Re: fuzzy search in a list

    Quote Originally Posted by Tsjallie View Post
    As your stuck to standard Excel functionality I think Autofilter comes closest to what you looking for, but still far from spot on.
    You can have the list of chemicals in seperate sheet with autofilter on.
    But users would still have to manually copy the found chemical to the "user interface". Not very friendly.

    Alternatively you couldn't use macros, but save the workbook as .xls (Excel 97 - Excel 2003 format), but do not consider this as an advice.
    No, not really friendly XD But i think i will play around the lists vs categories of chemicals option...

  13. #13
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    Re: fuzzy search in a list

    Quote Originally Posted by AliGW View Post
    I am wondering if the latest solution offered in this thread might show a way forward: ...
    Not really. I think in the thread you mention the main problem is "dynamically" extracting unique values (i guess drop down lists based on the value in the adjucent cell should be very doable with INDIRECt in Data validation?)? And my list is already with unique values, just complicated ones... Or do I misunderstand something in that thread?
    So far i guess I will go for introducing categories of chemicals and offering to choose a category and then a chemical, to make it more bearable for users....
    Thanks for trying to help! =)

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: fuzzy search in a list

    The bit about that thread to which I allude is the limiting of the list in the drop-down - the list is effectively filtered by the first letter. It occurred to me that this might work for you. Did you look at the latest sample workbook in that thread?

  15. #15
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    Re: fuzzy search in a list

    I am honestly not sure which bit then you refer to... (slicing one?) �� would appreciate if you navigate me a bit
    As for filtering by the first letter - definitely an idea. but what if the value a user is entering starts with a letter/word that is, for example, second or third in database's set for this chemical (e.g. the user's trial is to type in "acid blahblahblah" and in the dataset its "blahblahblahish acid")? i assume could be more or less manually solved but i am not sure i would have project time to do that XD

  16. #16
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: fuzzy search in a list

    Ali, I haven't looked at the thread that you suggested, but given that you say it works on first character entered, I can't see how it could work with something like this.
    Quote Originally Posted by nataliache View Post
    and to china clay, which has a lot of synonyms like natural china clay, synthetic china clay, aluminium silicate, Mica pigment or kaolin...
    Given that this needs more of a thesaurus than a fuzzy match, maybe having 2 dropdowns for name insted of 1 would be more friendly.

    First dropdown with a list of simplified names to choose from, using your example in post #6, Cellulose and China Clay.
    Then the second dropdown as a dependent to the first, lists out all available synonyms for the selection.

  17. #17
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    If you change the formula to have multiple wildcards, it will also take parts of the item searched for.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Brilliant solution BTW.

  18. #18
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    Hmm, adding this extra wildcard looks nice and seemed to work at first glance, but it doesn't as the list is sorted but not filtered.
    Back to the drawing board.

  19. #19
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    Looks like this formula works better:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Not perfect though. The list still may have items not containing the typed string, because it's not filtered.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    Re: fuzzy search in a list

    Quote Originally Posted by Tsjallie View Post
    Looks like this formula works better:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Not perfect though. The list still may have items not containing the typed string, because it's not filtered.
    This is not how the client saw it, but yay that's genius!! i think that would work in more than a half of cases, which is good enough for me =) I am so totally taking it on board! Thank you! ❤
    Last edited by nataliache; 08-15-2018 at 03:04 AM.

  21. #21
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    I think I just figured out how to filter the list with secondary lists, but it depends on how static the list of chemicals will be.
    If its a fixed number of items then I think I can get it to work.
    If it's growing on regular basis then that would spoil the idea.
    Last edited by Tsjallie; 08-15-2018 at 05:19 AM.

  22. #22
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    Here's a new version with filtered datavalidation list.

    On the Chemicals sheet I added a column Picklist with formulas looking up chemicals with the string in the activecell in their name.
    I converted the chemicals list and the picklist to a tabel with 2 column. This to make sure the picklist is always the same size as the chemicals list.

    Now select a cell in the userinterface Name column a start typing a string (e.g. "clay"). Then clicking the dropdown will present a list of chemicals having "clay" in the name.

    Note:
    the picklist is updated each time the calculation process is executed. Changing a cells content will trigger the calculation, but just selecting a cell will not.
    So if you just select a cell a click the dropdown then the list may not be updated yet. In that case just press [F9].
    Unfortunately this cannot be automated.
    Attached Files Attached Files

  23. #23
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: fuzzy search in a list

    Tsjallie, I can't get this one to work even with [F9].

    FWIW I've had difficulty building DV lists with formulas that reference Tables.

    Do you think you could make a dynamic named range work? Would that take care of the growing list part?

  24. #24
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    Re: fuzzy search in a list

    It works for me! But would you mind to explain how did you make the Picklist? Is it a data table or an array formula? and did you convert it to a table before or after filling in the formulas?.. excel tells me i can't enter array formulas in tables, but the formula contains table references... I am confused... this is so much above my level XD

  25. #25
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    Hi Dave,
    I believe you're still on Excel 2007. Maybe referencing tables isn't supported in that version.
    Does the RefersTo-box in the Names Manager show #Ref! for Chemicals and/or Picklist?
    What if you try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    for the Chemicals name?

    Or maybe the CELL-function with "address" is not supported.
    You could enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in any cell and then select an other cell.
    When pressing [F9] the address of the newly selected cell should be displayed.

    The named ranges Chemicals and Picklist are dynamic as they reference Table1[Chemical] and Table[Picklist] resp.
    The two list tied together in one table handle the growing (or shrinking) of the list.
    Last edited by Tsjallie; 08-16-2018 at 01:58 PM.

  26. #26
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    Hi Nataliache,
    good to hear that it works for you.

    The Chemicals list and the Picklist are combined in one data table (Table1).

    This table was created as follows:
    - Entered the column name "Chemical" to B1
    - Converted the Chemicals list to a table (select B2. Then in the Insert tab select Table)
    - Added a column "Picklist" (select C1 and type "Picklist". Excel does the rest)
    - In C2 entered the formula. Excel automatically copies down that formula.
    The formula needs to be an array-formula because not all functions used in it support arrays.

    It shouldn't be a problem to enter array formulas in tables. Works fine in Excel 2010, so should be ok for Excel 2013 too.
    Do you get an error message?

  27. #27
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    Re: fuzzy search in a list

    Tsjallie, Works now! but with all the other things stuffed into the tool it takes ages to calculate the results... Nevertheless, I want to be you when I grow up XD thanks a lot!

  28. #28
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    it takes ages to calculate the results
    Can you be more specific about "ages"?
    Exactly how big is that list. Maybe it can be speeded up.

  29. #29
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    Maybe a small addition to the formula for the picklist speeds up things.
    It makes that the formula only goes through the list when the active cell is in the Names part of the userinterface.
    Best to give that range a name. I named it ChemNames which refers to Sheet2!$B$5:$B$32.
    Attached Files Attached Files

  30. #30
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    Re: fuzzy search in a list

    It used to take about 5 sec before the fuzzy search addition, and was about 5-8 with your first version, and with the working cool one it was about half an hour... I will try the addition on monday, left the laptop in the office =)

  31. #31
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    OK. Keep me posted.
    Let me know if you need any further explaination or assistance.
    If your problem is solved, please mark the thread as [SOLVED] (See Thread Tools in the menu above).

  32. #32
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    Been looking further to speed up the calculation by breaking up the formula in the Chemicals list.
    Calculation now takes abt. 10 secs for 10,000 rows, 20 secs for 15,000 rows and 40 secs for 20,000 rows.
    Guess that's better than half an hour, but still....
    How many rows will the real list be?
    Attached Files Attached Files

  33. #33
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: fuzzy search in a list

    Quote Originally Posted by Tsjallie View Post
    Hi Dave,
    I believe you're still on Excel 2007. Maybe referencing tables isn't supported in that version.
    Does the RefersTo-box in the Names Manager show #Ref! for Chemicals and/or Picklist?
    What if you try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    for the Chemicals name?

    Or maybe the CELL-function with "address" is not supported.
    You could enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in any cell and then select an other cell.
    When pressing [F9] the address of the newly selected cell should be displayed.

    The named ranges Chemicals and Picklist are dynamic as they reference Table1[Chemical] and Table[Picklist] resp.
    The two list tied together in one table handle the growing (or shrinking) of the list.
    Sorry about the delayed response. Long weekend/out of town family .... :

    Actually I downloaded to my Office 365 machine. What's odd is that your upload now works () I didn't get #Ref! errors. The drop downs were just "dead" ... nothing dropped. I can't duplicate the behavior now.

    I haven't tried CELL yet. That's worked for me in other places. I'll have to check this out.

    Yes. I was thinking of an alternative if necessary (it isn't) to the Table for growing/shrinking.
    Last edited by FlameRetired; 08-21-2018 at 05:29 PM.

  34. #34
    Registered User
    Join Date
    08-12-2018
    Location
    Amsterdam, the Netherlands
    MS-Off Ver
    2013
    Posts
    13

    Re: fuzzy search in a list

    Sorry for the long delay. Works, with calculation time about 30-50sec (there are less than a 1000 chemicals, but i run them throurh the what-if for 50 scenarios). Also, now it pretty much always need F9. I think i would go for two options (with the serach you suggested and without a saerch but categorisation) to show to the client and let them decide. Thank you so much for the help! I close the thread now, it was fun =)

  35. #35
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: fuzzy search in a list

    Glad I could help.
    It was indeed an entertaining excercise. Much more fun than Sudoku or Dutch television

+ 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. [SOLVED] Fuzzy number search
    By Ajohn403 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-30-2015, 06:17 PM
  2. fuzzy search for first four letters
    By jolinchew in forum Excel General
    Replies: 2
    Last Post: 06-10-2015, 05:08 AM
  3. [SOLVED] Fuzzy Search
    By CatalystDev in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-03-2014, 06:16 AM
  4. Vlookup fuzzy search
    By lamdl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-31-2014, 07:36 PM
  5. fuzzy search
    By tixbeast in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-24-2013, 02:03 PM
  6. Using VBA to search for a string (fuzzy logic)
    By masond3 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-30-2013, 09:26 AM
  7. Fuzzy search of a list of entries against a text cell
    By PAKoffee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-27-2011, 09:35 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