Forum Discussion
Searching for multiple words in an Excel spreadsheet (ugh, why is this so hard?)
I created a sheet named Data with some sentences in cells in several rows/columns.
I also created a sheet named Keywords with a range of words in A2:A18 - in your real workbook, that would extend to A41 or so.
I selected this range, clicked in the Name/Address box on the left hand side of the formula bar, typed the name Keywords and pressed Enter. This names the range with the keywords.
In cell C2 on the Keywords sheet, I entered the formula
=FILTER(Keywords, COUNTIF(Data!A1:Z100, "*"&Keywords&"*"), "-none-")
This range looks at the Keywords range, and returns only the items that satisfy the condition that
COUNTIF(Data!A1:Z100, "*"&Keywords&"*")
returns a non-zero value. If none of the keywords satisfy the condition, "-none-" is returned.
COUNTIF(Data!A1:Z100, "*"&Keywords&"*") looks at the range Data!A1:Z100 - you can expand or shrink this as you like. It counts the number of cells in this range that contain a keyword, plus possibly other text: "*" is a wildcard that stands for any number of characters (including none).
An alternative would be to highlight the keywords that occur on the Data sheet - see the attached version. I created a Conditional Formatting rule for the Keywords range.
Hello. Thanks for your reply, but I don't know what to do with the workbook you provided once downloaded? Edit: I noticed the formula. Can you explain the formula and step me through the logic of what it's doing so I understand it better?
I created a sheet named Data with some sentences in cells in several rows/columns.
I also created a sheet named Keywords with a range of words in A2:A18 - in your real workbook, that would extend to A41 or so.
I selected this range, clicked in the Name/Address box on the left hand side of the formula bar, typed the name Keywords and pressed Enter. This names the range with the keywords.
In cell C2 on the Keywords sheet, I entered the formula
=FILTER(Keywords, COUNTIF(Data!A1:Z100, "*"&Keywords&"*"), "-none-")
This range looks at the Keywords range, and returns only the items that satisfy the condition that
COUNTIF(Data!A1:Z100, "*"&Keywords&"*")
returns a non-zero value. If none of the keywords satisfy the condition, "-none-" is returned.
COUNTIF(Data!A1:Z100, "*"&Keywords&"*") looks at the range Data!A1:Z100 - you can expand or shrink this as you like. It counts the number of cells in this range that contain a keyword, plus possibly other text: "*" is a wildcard that stands for any number of characters (including none).
An alternative would be to highlight the keywords that occur on the Data sheet - see the attached version. I created a Conditional Formatting rule for the Keywords range.
Thanks for this info.
I couldn't figure out how to EXTEND the Keyword range (for example, if I wanted 100 Keywords), but noticed if I went to Formulas->Name Manage, I could find EDIT the Keywords in there and change the range. I just couldn't figure out how to extend the Keywords with the main formula or mouse clicks, etc, lol. Is editing it via Formulas->Name Manage, the right way or is that just another way of doing it?
Another way would be to insert empty cells in between the existing keywords, then enter new keywords in those cells. This will expand the Keywords range automatically.
Ah, I hadn't thought to just insert between them lol. Should have thought of that.
I also figured out another way to tackle this issue overall too... mostly with google searches and piecing together various things into a formula that looks like this....
If column A (A1-A30) has my keywords I wanted to check against, I can have the following formula in column B (copy/pasted in all 30 rows, and just adjusting A1 to A2, and so on...
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(A1,'C:\Temp\Spreadsheet-to-be-checked)'!$B:$B)))>0,"Yes","No")
Where it will check the entire spreadsheet (located in C:\temp) for those keywords in A1-A30 again everything in column B of the spreadsheet (the keywords would ONLY be in column B). and if it finds the keyword in Column B it will report Yes in that row, or report No in the row if it doesn't find it.
The only thing I have to do is, for each NEW spreadsheet (located in C:\temp) I just have to go to Data-> Workbook Links -> Change Source .... to the correct file I want to check for keywords.
Both methods seem to work well. Thanks for your help so much!