Filter column based on values from a different column

[dragosm]

Hi, nice to meet you all!

I'm struggling with this for a while now. I'm not very familiar with Excel formulas. I'm using it only at a beginner level.
I've searched on the internet to find a solution to this but unfortunately with no success so far.

Please use this image for reference:

I want to filter out some keywords (column A) based on some negative words (column B) and to be able to do this automatically and populate column C with the keywords (entries from column A) that don't contain any of the words from column B.

Is there any way of doing this automatically? Kind of a super filter?
Btw I'm using Office 2019.

Thank you!

 

[dragosm]

Anyone who's encounter this before?
Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
With a helper column, how about

+Fluff 1.xlsm
	A	B	C	D
1	Data	Exclude
2	Ullswater	south	0	Ullswater
3	Cleobury Mortimer	and	0	Cleobury Mortimer
4	Waldon	St	0	Waldon
5	Marple South	valley	1	Boulsworth
6	Stanley		1	OsFatherley & Swainby
7	Birches Head and Central Forest Park		1	Longbridge
8	Hambleden Valley		1	Kirkburton
9	Boulsworth		0	Old Laund Booth
10	OsFatherley & Swainby		0	Tong
11	Longbridge		0	Crosby Ravensworth
12	Kirkburton		0	Alport
13	St Teath and St Breward		1	Rawnsley
14	Colne Valley		1	Bradwell
15	Old Laund Booth		0	Ermin
16	Tong		0	Shaw
17	Crosby Ravensworth		0	Shadsworth with Whitebirk
18	Alport		0	Quinton
19	Royton South		1	Padfield
20	Great Chell and Packmoor		1	Norden
21	Rawnsley		0	Bishops Frome & Cradley
22	Bradwell		0	Richmond
23	Teign Valley		1	City
24	Shaftesbury West		1	Bradford Moor
25	Ermin		0
26	St Mary's		1
27	Shaw		0
28	Shadsworth with Whitebirk		0
29	Cropredy, Sibfords and Wroxton		1
30	Haslemere Critchmere and Shottermill		1
31	Quinton		0
32	Bowling and Barkerend		1
33	Padfield		0
34	Norden		0
35	Bishops Frome & Cradley		0
36	Richmond		0
37	City		0
38	Bradford Moor		0
Main

Cell Formulas
Range	Formula
C2:C38	C2	=MAX(--ISNUMBER(SEARCH($B$2:$B$5,A2)))
D2:D38	D2	=IF(ROWS(D$2:D2)>COUNTIFS($C$2:$C$38,0),"",INDEX($A$2:$A$38,AGGREGATE(15,6,(ROW($A$2:$A$38)-ROW($A$2)+1)/($C$2:$C$38=0),ROWS(D$2:D2))))

 

[dragosm]

Hi thank you for this but I get only "0" in column C ... I've been using the exact code you wrote (of course I changed the range) but is not working!
Thanks!

 

[Fluff]

The formula in col C may need array entry, try confirming it with Ctrl Shift Enter, rather than just Enter & then drag down.

 

[dragosm]

You are a lifesaver!
It worked!
Thank you a lot!

 

[Fluff]

You're welcome & thanks for the feedback.