Help writing formula to flag text strings with certain words

[joedacus]

I am filtering a large database of addresses before doing a mailout. I have a list of about 30 words in column A (A2:A31) that, if any are present in the organization's name (C2:C27365), I do not want to mail to them.

How do I write a formula that goes through the list of the organization's names (C2:C27365) to 1) see if it has any of the words from the filter list (A2:A31), and 2) place an "x" (or TRUE or FALSE or anything to alert) in the cell to the immediate left (Column B) to flag the presence of a filter word? That way I can autofilter Column B and then delete all the rows with an "x" in Column B.

Your help greatly appreciated!

 

[fairwinds]

Re: Help writing formula to flag text strings with certain w

Hi,

You could try:

=IF(OR(ISNUMBER(SEARCH($A$2:$A$31,C2))),"x","")

Confirmed with Ctrl + shift + enter in B2, then dragged down.

 

[joedacus]

Re: Help writing formula to flag text strings with certain w

Thanks, but I couldn't get it flag ("x") anything except four organizational names that did not have anything in the filter list.

Is there a function that reads a text string for either a single word or, ideally, an entire list from an array, and then puts an "x" or TRUE or FALSE in the adjacent cell if one is present?

 

[fairwinds]

Re: Help writing formula to flag text strings with certain w

I do not know why it did not work for you.

Did you remember to confirm the formula with Ctrl + Shift + Enter?

Book1
	A	B	C	D
1
2	cat	x	It'sacar
3	dog	x	boatdog
4	car		Notthis
5	boat	x	Butthiscar
6	train
Sheet1

 

[joedacus]

Re: Help writing formula to flag text strings with certain w

I can't tell you how much I appreciate your help. I have a database of 27,000+ names that have to be filtered. We're close.

1. Regarding "confirming" the formula, after pasting the formula into column B, I press Ctrl+Shift+Enter, then drag the cells down to copy the formula. I'm not familiar with the term "confirming" so tell me if this correct.

2. The formula is almost working...for some reason it only refers to the adjacent left cell the formula is in (if the formula is pasted in and cell B7 is selected, when I check "Trace Precendents" button, only cell A7 is referenced). Looking at the formula as you have it, I can see that it refers to $A$1:$A$31. Any thoughts? Does it have to do wih confirming?

 

[fairwinds]

Re: Help writing formula to flag text strings with certain w

Hi,

1.
What I mean is:
Select cell B2.
Paste or write the formula in the formula bar.
Then, while cursor is still in the formula bar you press Ctrl + shift + enter simultaneously.
If done corectly you will now see the formula surrounded by curly brackets as in my exhibit.
Now you can drag the formula down.

Ctrl + shift + enter makes the formula an array formula. Read more about them in help file.


2.
When I press trace predecendants an arrow points to one cell in A column and A2:A31 gets a blue border. If that happens the formula is ok.



You say it is almost working. What is the error you get?

 

[joedacus]

Hi, again, thank you for your help. With 27,000+ names to filter through, I'm dying to find a final solution.
I got the CTRL+Shift+Enter part (I didn't have the cursor in the formula bar).

The error that keeps occuring in my project, as well as my reconstruction of your simple dog-cat-train model, is the formula in Column B only refers to the adjacent cell in Column A. If cell B12 is selected, and the formula as written and confirmed is in the formula bar, when I click on Trace Precedents, it selects the $A$2:$A$31 with a blue bounding box, but a horizontal line goes from C12 to B12 and another one starting from A2 to B12. The second tracer is the same in all cells in Column B--they all have a tracer starting at A2 that points to selected cell in Column B.

Also, all cells in Column B with the formula in it have an "x" mark in Column B, even when the organization's name in Column C does not contain any of the filter words in Column A.

I tried to post a screenshot using Colo' HTML but got a bunch of garbage in the text. I can email you a screen shot if you would prefer.

If you can solve this, I'll send you a bag of the latest Starbuck's Black Apron coffee. This has turned out to be a bear. Thank you for your help.

 

[fairwinds]

Re: Help writing formula to flag text strings with certain w

It sounds to me your formula is entered corectly but just to be sure, do you see the curly brackets?

One reason tto why all gets X marked could be if you have empty cells in A2:A31. If that is the case, change formula to

=IF(OR(ISNUMBER(SEARCH(IF($A$2:$A$31="","zzzzzzzz",$A$2:$A$31),C2))),"x","")

Do not forget Ctrl + shift + enter.



This is another option that you do not need to ctrl + shift enter.
See if this works better:


=IF(SUMPRODUCT(LEN(C2)-LEN(SUBSTITUTE(C2,$A$2:$A$31,""))),"x","")

 

[joedacus]

YES! You did it! Tell me where to send the coffee. Email me at joedacus@ev1.net

I know I've said it several times before but I can't begin to tell you how much I appreciate this. I'm behind on getting bids because it's taken me days instead of hours to figure this out.

Thank you very much. I'm serious about the coffee.

 

[fairwinds]

Re: Help writing formula to flag text strings with certain w

Glad I could help!

Thanks for the offer, I do like coffee but I will not have you send it across the ocean...

Maybe I'll drop by on a cup if I ever come to your part of the world instead.