I'm not sure that I'm clearly understanding what the goal is either, but you'll find my effort below. My interpretation is that you want to be able to put search term(s) in H2 and find out which items from your table contain all of those terms, but you're frustrated because all of the easy solutions require that the terms in H2 exactly match the word order of the way they appear in the table. If I've got that right, then my first thought was to use a UDF, since it's already a macro-enabled file. I used the following UDF:
This function operates like SEARCH, but it independently checks each word in the search box against the cell to be searched, returning the percentage of search words that are contained in the searched cell. Matches, therefore, return a value of 1, or 100%. If the search term is "brown fox" and the searched cell is "brown leather shoes", the formula will return a .5, or 50% match (it matched one of the two search terms). Using the following in B8:
=CONTAINS($H$2,$H8)
...and filling down turns column B into your key column. Filtering by 1 in that column returns all 100% matches, so your "show locations" button works the same and your COUNT cell just requires a basic COUNTIF formula. I think columns C:E are no longer needed. I also slightly modified your "show locations" code to make it more adaptable. Take a look at the attachment to see if I'm understanding things adequately. If I've got it right, your request still might be possible with a standard formula, but I wasn't seeing it. JohnTopley might have better luck; he's usually stronger with formulas than I am.
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by cytop
Bookmarks