Delete rows NOT containing 1 of multiple strings in a column

[jarochnrol]

I have a table of action items that are still open, and Amy Bob, and Charlie are on my team.

When I run a report, the table shows the current asignee in column N. I need a macro that sorts according to column N and deletes everybody except Amy, Bob and Charlie.

Here's a shortened version of the table:

Item#-Info---Asignee
1-------info1--David
2-------info2--Bob
3-------info3--Amy
4-------info4--Charlie
5-------info5--Greg


My goal:

Item #-Info---Asignee
3-------info3--Amy
2-------info2--Bob
4-------info4--Charlie

Thanks!

[JBeaucaire]

1) In an empty column, enter a "key" formula to make this easy, starting in row2, then copied down:

=OR(ISNUMBER(SEARCH($N2, {"Amy","Bob","Charlie"})))

2) Turn on the AutoFilter then do a filter on that column for FALSE

3) Delete all visible rows

4) Turn off the Autofilter and clear the helper column of the formulas you added.

Done.


You can turn on the macro-recorder to turn that into a macro pretty easily, edit the resulting macro to add the formula in cells going down far enough to always cover the range of data you deal with typically, 500-1000 rows, perhaps.

[jarochnrol]

Great idea! I tried it and it worked well, except some tasks are left blank and have no asignees. Excel recognizes both the team members as well as the blanks as "true". How should I avoid this?

[JBeaucaire]

Avoid how? What's supposed to happen with blank rows?

If you want them deleted, too, then maybe this:

=AND(N2<>"",OR(ISNUMBER(SEARCH(N2, {"Amy","Bob","Charlie"}))))

[jarochnrol]

Works perfect. Thanks a million!

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.