0

I'm trying to streamline a job I have, and I'm not entirely sure how to. I'm excel dumb, but this is what I'm trying to do

4 columns A, B, C, and D. I'm worried about B and D. I want to check B for certain words, and check if there's any value in D. If B contains anything other than my designated words and D contains nothing.. I want to delete the entire row.

IE B1, b2, b3, b4 and b5 all says respectively Dog one, dog one, dog two, dog two and dog three... I want to search B for the words "one and three" if they contain either/or they're safe regardless of what's in D. For every entry that does not contain "one" or "three" in B, I want to check D. if D is blank. I want to delete that entire row.

IF I'm not making sense I can try to clarify a little better. I'll be checking daily to see if this ends up answered. Thank you in advance.

edit: I can't install any programs or third party deals on this computer. I'm not sure if that's a problem, but I read somewhere that some scripts required outside programs. I want to streamline this to all be done within excel

5
  • 1
    not completely following, but it sounds like instead of modifying the original values, you might be better off making a copy of each. So make comumns E, F, G, H and have them copy the value of their respective values in the other column (eg: E=A, F=B etc) UNLESS your conditions are met, in which case they remain empty. After that you're still left with a bunch of empty rows, but that makes it easier at least. I'm sure someone has an easier solution to that second problem
    – Blaine
    Sep 30, 2017 at 7:45
  • So the idea is that column B has names and other text. I want to filter column B for that other text and make sure those rows are safe.... for every other row in column B, I want to check column D. if it does not contain anything, I want the entire row deleted. When done I'll be left with rows in B containing my specified words with blank areas in column D... and then the rest of B will be names with an entry of some sort in column D.... It's kind of hard to explain considering I can't use exact details
    – darkzama
    Sep 30, 2017 at 7:52
  • alright, sorry I don't quite have the brainpower at the moment to process that, but it sounds like my solution should work...? Then you can just copy paste the results to wherever you need it
    – Blaine
    Sep 30, 2017 at 8:06
  • The problem is that I wasn't planning on modifying the original values. I'm just trying to search B for certain values and D for any value and delete every row that does not meet those criteria. D is either going to have a number or a letter in it. IF it contains any entry, I want that row to be safe. B is going to contain names and titles, I want to filter for the titles. If it contains those titles that row is ALSO safe, regardless on whether D has any entry.
    – darkzama
    Sep 30, 2017 at 8:09
  • alright cool, then it really should work since you're making a copy of the values instead of modifying them
    – Blaine
    Sep 30, 2017 at 8:20

1 Answer 1

0

There might be a faster way with add-ins (query) or macros. One alternative is this:

  1. First add headers and a filter to your table

enter image description here

  1. In Header 2, select the custom filter enter image description here

  2. Make a custom filter with "does not contain" and both arguments you want. enter image description here

  3. Now you should have only the filtered results. You can remove the empty lines in D manually.

  4. If there are many rows, filter 'blanks' in Dand create an extra column called 'marked for deletion' where you write something in there for only the D (example "delete"). enter image description here

Then unfilter everything and sort the 'marked for deletion column in any order (e.g. A->Z). All the rows to be deleted should now be grouped together at the top or bottom of the list.

NOTE: be very careful deleting more than one row at the same time while using filters, as you might accidentally delete filtered rows.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .