Is it possible to find multiple target items in a row? For instance, I would like to find all rows that contain “test” in a cell and “this” in another cell.
If not, is there any way to copy selected rows? For instance, I can use Ctrl-H to find all occurrences of cells that contain “test”. If I could select those rows and copy them into another sheet then I would be able to search those rows for cells that contain “this”.
Thanks -
george
Thanks - I downloaded this and am trying to figure out how it is done. The problem is that I also wish to find rows where D is in the First column and A is in the Second.
Thanks -
george
Welcome George!
As @dscheikey already said - this task is performed by the filter.
As @Villeroy showed in his example - the helper column makes filtering easier.
Let’s do it together, step by step.
Add a helper column at the end of your original table. In the first cell, enter a heading - it is desirable that it starts with a letter that is not found in other headings - this is not necessary, but it will make it a little easier to set the filter parameters. For example, I named my helper column Search.
In the second cell, enter a formula that will combine all the cells on the left into one long string - =CONCAT(A2:H2) (This is in my example, the test data ended in column H, this letter may be different for you.)
To write the formula, I pressed = and started typing CON… Until the yellow tooltip showed me the word CONCAT. Then I pressed Enter and the function appeared in the cell along with parentheses. I pressed the left arrow (the cursor moved to cell H2 and the formula became = CONCAT(H2) ), pressed Ctrl+Shift+left arrow and selected the entire range of cells up to column A. After that, I pressed Enter again and finished entering the formula.
I selected the cell with the formula and by pressing Ctrl + Shift + End selected all the cells in this column until the end of the data. Pressing CTRL+D filled the entire column with copies of the formula.
It remains quite a bit - apply the filter: Alt-D (Data menu), two times F (select More Filters), Enter (select Standard Filter).
Tab - select field Condition, C - choose Contains, Tab - choose Values, type test
Then twice Tab, A, Tab, S (choose Search - It was for the quick selection of this value that I chose the name of the auxiliary column 
), Tab, C, Tab, this, Enter.
Honestly, even despite the concise description, the process itself took much less time - see for yourself:
Okay, thanks, I was able to do that and it works.
Cheers -
george
Hello! You can put an autofilter over your data. This gives you a dropdown in the first line of your data with which you can set your filter.
Select your data including the heading and use the menu Data - AutoFilter.
I hope this helps you.
I am not completely sure how to use this. When I select everything and choose AutoFilter it gives me drop-downs on each column. If I select a value it shows me only the selected value from that column.
I need to search the entire worksheet for a value, regardless of column, then search for another value within any cell in the selected rows. Perhaps there is a way to do this with AutoFilter than I am not understanding.
Thanks -
george
I find it very hard to insert all the different values of an item using multiple filters. A much easier solution I found for selecting multiple items using regular expression. Select Data > More Filter > Standard Filters … . Then select Options > Regular Expressions. Then select the column in the Filed Name. Select Contains in the Condition and in the value put all values in parenthesis; separated by |. e.g. (value1|value2|value3|…).