I have a worksheet that has columns A through AB and rows 1 through 4950. I want to extract every single row that contains a specific word within that row, whether that word is found in column A, B, Z or AB. Essentially, I want to search the entire data set for the specific word and return the row that it's found in, except I only want unique results. So if a row has the word found in multiple columns, it will only return that row once. I'm not sure if this will help at all, but below is a picture of the "Find and Replace" results that show a snippet of some of the cells that contain the criteria which shows how the word can be in multiple different columns of the same row, but the criteria isn't always found in those same columns.
I should also mention that this filtering must be done by a partial search, such that if the keyword I'm looking for is "Foo", then the row that contains a cell with "This is Foobar" will be extracted. Additionally, I'd like to be able to preserve the whole row of data such that any other cells in the row that may or may not contain the keyword can still be viewed. I don't mind if the solution to this is done by a formula or another built-in method of Excel. If there's anything else I need to clarify, please let me know, thanks.
CodePudding user response：
Don't be afraid to use many columns in your solution.
First copy your input sheet, so you can always go back to the original.
In BA to CB put the same formula - in BA2 =IF(ISERROR(FIND("<search-string>",A2)),0,1) and drag in both dimensions.
Then in CD2 put =sum(BA2:CB2) and drag down.
Now you can sort the row on column CD, and delete the ones which have 0 in column CD.
CodePudding user response：
After some more deep diving into the functions and possibilities of excel, I stumbled across a video on Youtube detailing how to do exactly what I wanted to do. This is the video I'm referring to. It makes use of Power Query by clicking somewhere in a table, going to the Data tab, selecting the "From Table/Range" and creating a query and such. The video will explain it heaps better than I can. Hopefully this can help someone else out in the future.