Please Help, trying to figure out an advanced word seach

[Downwinder]

I have a spreadsheet with 16,000 rows. There are 12 columns and 5 of the columns have a large amount of text in each cell.

Lets just say the 16,000 lines are related to fruit. I want to do a word search for "apple" that takes any row that the word "apple" appears, copies it, and pastes it into a new worksheet. The entire row, not just the cell it appears in. I am not concerned about duplicity, I can remove duplicates later.

Any suggestions on how I would go about this? I was thinking a Macro at first but I am not sure that would get me where I want to go. I also want to be able to repeat the process, eventually having separate worksheets for "banana", Orange", "lime", etc.

Thanks for the help!

[6StringJazzer]

Welcome to the Forum Downwinder! Nice to see another Virginian.

It might be possible to do this with formulas but it would be rather complicated. Other folks might revel in creating complex formulas, but if it were me I would be writing a macro. I can give more guidance if you provide a sample file.

The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

P.S. "duplication" not "duplicity".

[MrShorty]

I might be inclined to use a filter for this:

simpler autofilter: https://www.wikihow.com/Use-AutoFilter-in-MS-Excel
more complex advanced filter: https://www.contextures.com/xladvfilter01.html

Autofilter could not handle a "column B contains apple or column C contains apple or column D ..." , so you would need to use advanced filter if you stick with that exact approach. I could see adding a helper column of some kind that would either combine the text in the 5 columns or that would determine which fruits are spoken of in those 5 columns, then a simple autofilter on that helper column might work. Filters can sometimes filter out duplicates, too, so you might be able to solve that part of the problem as well.