My Google and/or reading comprehension skills are failing me this afternoon, so I could really use some help.
I’ve got an Excel spreadsheet that has about 3,600 rows and 9 columns. Within this spreadsheet, I am interested in the name “Smith.” According to Excel’s “Find All” command, “Smith” appears in 91 places in the spreadsheet, in various rows and columns.
What I want to be able to do is filter out everything in the spreadsheet EXCEPT for the rows that contain “Smith” anywhere in the row, meaning I’d be left with 91 rows or less (I’m assuming less, because “Smith” probably appears in more than one column in the same row on a few occasions). I realize it’s late on a Friday afternoon/evening, but if someone can offer help, I’d really appreciate it.
It always helps to mention the version of any software you’re using … options may not differ, but navigation might.
There’s probably a more elegant way to do this, but here’s a quick-n-dirty. (It’s Friday night for me, too …)
Use the Filter feature. Click anywhere in your data range. In Excel 2010, on the far right of the Home tab, click the Sort & Filter button, then Filter. This will add drop-down arrows to each of your column. In each column, UNcheck “smith” where it appears. You can select and copy this filtered data set to another sheet if you wish.
It might be Friday night but every day I’m Excellin’…
Put that data in a pivot table–click anywhere on the data, then choose “Insert Pivot Table.” Now you have better options. Move the field “Name” to “Row Labels”, and you can choose not only “Does Not Equal” Smith, but “Does Not Contain” Smith. Or maybe “Does Not Start With” Sm So you can get “Smyth” or “Smithe” too.
Sounds like the tricky thing is that “Smith” could be in any column, right? So setting up an ordinary filter would be laborious if not impossible.
If it were me, I’d set up a new column, and in fields thereunder set up an IF condition. Something like IF ((column A is like smith) OR (column B is like *smith) OR . . . ). Set TRUE = “Yes”, FALSE = “No”.
Then set up your filter and make sure it includes the new column, and simply filter for “Yes” or “No” as you prefer.
I know the above description is very sloppy (I’m away from the office), but if you know how to set up an IF statement then you may be able to figure it out.
What I described is a metric ton easier than fooling with IF statements, or even Pivot Tables. I’m not even sure a Pivot Table would work in this case.
Except for the small problem that your way won’t work.
The OP wants all rows with Smith in any column. What you explained was how to find all rows with Smith in no columns. Even if we grant that you wrote “uncheck Smith” when you meant to write “uncheck all choices *except *Smith”, that would still leave the filter configured to find all rows with Smith in all columns.
A simple filter just can’t perform an OR operation across columns.
I’d be curious to know what sort of data this is that the OP has organized in this way. It’s certainly not well-arranged for the task at hand.
Hi, folks – sorry for disappearing. Had a different work emergency that cropped up. For the record, what I ended up doing was a quick-'n-dirty solution was to run filters on each of the columns where I knew “Smith” might pop up (there were only three) and pasting the results for each filter into a separate worksheet. Granted, this created for some overlap (because, as I said, Smith sometimes appeared in more than one column), but the universe of results was small enough that we were able to deal with that manually.
To address LSLGuy’s question, for anyone who is still interested in tackling this question, here’s a more detail explanation of what my spreadsheet was about.
The spreadsheet contained information about telephone calls – one call per row. For each call, there was a column that contained a unique ID # for the call, the call date, the call duration, the initiator(s) of the call, the call’s recipient(s), and summary information about the call that could be anywhere from a couple of words to a paragraph or two of text (along with a few other columns which aren’t really relevant for this exercise). I knew that Smith’s name might sometimes be in the caller column, sometimes in the recipient column, and would sometimes be referenced in the call’s summary (even if he was neither the caller nor the recipient at times). But there were also lots of calls that did not reference Smith at all.
So my goal was to narrow down the list of 3,600 calls to a list that contained only the calls where Smith was referenced in any way.
Let’s make this simple - let’s assume you have two columns, “Name” and “Notes”. Smith could appear anywhere in those columns. So let’s say your chart looks like this:
Name Notes
Smith This cell does not contain the word you're looking for.
Jones Look - the last word of this sentence is Smith.
Bill Smith I got nothing here.
Anderson Nothing here either.
Boone Nothing here either.
Carmichael Nothing here either.
Delaney Look - the last word of this sentence is Smith.
Edwards Nothing here either.
Franklin Nothing here either.
Grant Nothing here either.
Marcus Smith Nothing here either.
Is that about right? Now set up a little chart over on the right, with a column for each column that “Smith” might appear. In our case, it’s just the two. In yours it may be 3-4 total. Make it look like this:
Name Notes
*Smith*
*Smith*
Make sure that each “Smith” is on a different row. (You can put this on another worksheet if you want to keep your original clean.)
Now go into the Data tab, and select “Advanced” under the filter area. For the “list range” select your big table. For “Criteria range” select your little chart you created. That should do it, and spit this out:
Name Notes
Smith This cell does not contain the word you're looking for.
Jones Look - the last word of this sentence is Smith.
Bill Smith I got nothing here.
Delaney Look - the last word of this sentence is Smith.
Marcus Smith Nothing here either.
There’s an option to copy this to a different worksheet when you do the Advanced Filter if you want to do it that way.
Asimovian - What I would do is filter each column for containing Smith, as you did. Then as I filtered each column, I’d put an identifier in the same new column (column I). So after you’ve run all your filters you filter column I for the identifier (the word “Smith” for example) and you have all the rows that contain Smith.