Find and delete rows using multiple values

You cannot delete rows (or anything else, really) with formulas or functions. At best, you could use a formula to ID which rows to delete, filter rows on that result, then delete them all together

Originally Posted by Logit

.
If you don't have a LONG list of terms/words to search for ... this is one way :

Thanks Logit. I have a long list (hundreds) of terms to search. This is the reason why I want the list to be in separate column and make a compare/search function. Is there any way to do that?

Originally Posted by FDibbins

You cannot delete rows (or anything else, really) with formulas or functions. ...

Hi, is there a way to find/select rows in column A if it contains a term from column B?

Probably, yes, can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Hi FDibbins. Attached here is the clean file. I'm a webmaster and presently cleaning my link profile. Column A are the links pointing to my site. Column B are good links that I wanted to remove from Column A. So, what will be left on Column A are links that are still not yet on my "Good Links" list.

Please take note that not all terms on Column B are still present on Column A. My "Good Links" list have accumulated for years and some websites may be inactive now or have removed the links pointing to my website.

Thanks.

Based on your file, try this...
C2=MATCH("*"&B2&"*",$A:$A,0)
D2=MATCH(ROW(),$C:$C,0)
both copied down.

Then filter on D, uncheck #N/A and you will be left with the rows to delete in col A. Note that if you delete the entire row, you will lose anything you have in col B in that same row.

A better option would be to do the filter, then just delete the cell contents of what shows, then either SORT A or use the Remove Duplicates

Thank you for that formula. I applied what was instructed. I don't know If I have done the right thing, but the results on Column A shows some of links (with terms dell.com, bullguard.com, etc...) which I wanted to hide/remove. What I wanted to do is hide/delete from column A if it has a match listed in column B. This way, what is left in Column A are links that I need to check.

sample2.png

Building on, or stealing from (sorry about that), Ford's suggestion, apply the formula in column C down as far as row 309 so that it includes all the domains in the 'Remove These Domains' list and not the domains under the 'IN QUESTION ????' list. Now apply the formula in column D down to row 2552.
Next, populate column E with the web sites whose domains are not in the 'Remove' list using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Hi JeteMc, first of all, let me thank you for the help. I am exploring the sheet with your formula, why is it that modifying any item on the sheet changes the value of Column E to #NAME? Is there any special formatting on this sheet?

Also, I can still see the value dell.com on rows 1895 etc of Column E... It is not supposed to be there because it was listed in Column B.

By the way, you can erase the "IN QUESTION" rows. I may or may not include it in this column.

Hoping for your patience guys.

Llesseur - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Here is more of a brute force approach.
1) The domains to be deleted are copied and pasted transpose into C1:KX1
2) The range C2:KX2552 (which may be hidden) is populated using: =ISNUMBER(SEARCH(C$1,$A2))
3) KY2:KY2552 is populated using: =COUNTIF(C2:KX2,TRUE)
4) KZ2:KZ2552 is populated using: =INDEX(A$2:A$2552,AGGREGATE(15,6,(ROW($2:$2552)-1)/(KY$2:KY$2552=0),ROW(1:1)))
The next column and cell are used for testing the presence of the domain in LM1 which is a drop down linked to C1:KX1 so that you can change the domain in that cell to check for the occurrence of other domains that should have been deleted.
5) LM2:LM2552 is populated using: =ISNUMBER(SEARCH(LM$1,KZ2))
6) LN2 is populated using: =COUNTIF(LM2:LM2552,TRUE)
Unfortunately the size of the file is too large to upload (5+ Mb). If you have problems applying the above I'll reduce the number of linking pages and domains to be deleted until I get the file size reduced to less than 1000 Kb.
Let us know if you have any questions.

I was able to upload a .zip file
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.