Nested CONTAINS within IF(COUNTIF()) for complex database

Hello, all

I have bit of an interesting Bug in my database. My database has over 13700 rows by 5 columns. I am using a search button that utilizes both macros and formulas. I wrote a formula to allow me to search all the columns at the same time. I have a column labeled basic description and there is both numerical and text data within this column.

Desired result:
I want to be able to search the entire database only using pieces of information. I want to be able to do a partial search and have a match. If the data in a cell is "the good brown dog" i want to be able to search "good dog" or "the brown" and it populate the cell "the good brown dog" as a search result.

I had this previously working however using the contains function on column H but it limited me to be able to only search data in column H. I need to be to do a partial search across all the columns and rows in the document.

The formula used is =IF(COUNTIF(F9:J9,"*"&$H$2&"*")>0,1,0).. I need a contains function built into this to allow me to search contains parameters in F9:J9

Please open the database below and do a search "wrench insert (modified)". I want to be able to search "wrench (modified)" and have the same search results. but through this, I must still have the ability to search all the columns as well.

Thank you

this UDF was used

Please Login or Register  to view this content.

It probably would've been wise to continue this in the old thread:

https://www.excelforum.com/excel-for...arch-tool.html

Nevertheless, the original CONTAINS was designed for a one cell range. The version below should work on larger ranges:

Please Login or Register  to view this content.

It works like SEARCH, so use CONTAINS(SearchTerm, SearchedRange) to return the number of cells in the range that contain all of the search terms. The formula in B9 uses:
=CONTAINS($H$2,$F9:$J9)

H3 should be changed to:
=COUNTIF(B9:B169,">0")

And your new Search macro should also be adjusted to account for values greater than one:

Please Login or Register  to view this content.

Try out the attachment to see if it's working as desired. Please also try to edit your post to include [CODE] tags around the code you posted above. If you're quick, you can fix it before the mods tell you to.

CAntosh you are a true Excel Wizard! Thank you so much!

I see what you mean about the issue with no search term in H2 producing a zero when it should return every entry. This occurs because the CONTAINS function regards a blank as a null search entry, so it returns 0 matched rows. The simplest work around is to include an IF clause for a blank search box, so H3 would be:

=IF(H2="",COUNT(B9:B169),COUNTIF(B9:B169,">0"))

I'm afraid I'm not sure where the 13693 expectation is coming from? Your sample workbook includes entries from row 9 through row 169, so the total in H3 wouldn't exceed 161, by my understanding. With the formula above, I get a correct "161" for a blank H2. I also get a correct "161" for a hyphen in H2. What am I missing? Is 13693 the number of rows in the actual workbook? What incorrect result are you getting for a hyphen in the search box?

Are you sure you need one? Clicking 'delete' on H2 will clear the search box. With the "search" macro in post #3, clicking the search/refresh button with an empty search box will return the entire list.

If you decide you need one, the macro below should clear H2 and unfilter your table:

Please Login or Register  to view this content.

If I'm understanding you correctly, then you can enable macros on the computers that will use the workbook by clicking file > Options > Trust Center > Trust Center Settings > Macro Settings > Enable all (or something similar). Be aware that this may also expose you to malicious macros, as well, though, so be careful what you open.

Correct, that should work. I misunderstood your question as relating to enabling macros, instead of protecting sheets. I should read more carefully. Your solution is a good one, just be aware that anytime the password changes, you (or someone) will need to go into the VB Editor to update the code. It's also worth noting that a savvy user could open the VB Editor and learn the password that's meant to protect the sheet. Those two caveats aside, the protect/unprotect lines should do the trick.

Well its funny that said a savvy VB user could just edit the code and see the password. I found a setting that locks your VB code so you cant see any code until you type in the password. If you go to the tools drop down menu and click "VBAproject Properties" and slide over to Protection. Then you can set a password that doesn't allow anyone to view any VBA code unless the password is entered!See the picture below: hehe.png