Filter column based on values from a different column

dragosm

New Member
Joined
Jan 27, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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:

2021-01-27_13-16-14.jpg


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!
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

dragosm

New Member
Joined
Jan 27, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Anyone who's encounter this before?
Thanks!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
With a helper column, how about
+Fluff 1.xlsm
ABCD
1DataExclude
2Ullswatersouth0Ullswater
3Cleobury Mortimerand0Cleobury Mortimer
4WaldonSt0Waldon
5Marple Southvalley1Boulsworth
6Stanley1OsFatherley & Swainby
7Birches Head and Central Forest Park1Longbridge
8Hambleden Valley1Kirkburton
9Boulsworth0Old Laund Booth
10OsFatherley & Swainby0Tong
11Longbridge0Crosby Ravensworth
12Kirkburton0Alport
13St Teath and St Breward1Rawnsley
14Colne Valley1Bradwell
15Old Laund Booth0Ermin
16Tong0Shaw
17Crosby Ravensworth0Shadsworth with Whitebirk
18Alport0Quinton
19Royton South1Padfield
20Great Chell and Packmoor1Norden
21Rawnsley0Bishops Frome & Cradley
22Bradwell0Richmond
23Teign Valley1City
24Shaftesbury West1Bradford Moor
25Ermin0 
26St Mary's1 
27Shaw0 
28Shadsworth with Whitebirk0 
29Cropredy, Sibfords and Wroxton1 
30Haslemere Critchmere and Shottermill1 
31Quinton0 
32Bowling and Barkerend1 
33Padfield0 
34Norden0 
35Bishops Frome & Cradley0 
36Richmond0 
37City0 
38Bradford Moor0 
Main
Cell Formulas
RangeFormula
C2:C38C2=MAX(--ISNUMBER(SEARCH($B$2:$B$5,A2)))
D2:D38D2=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

New Member
Joined
Jan 27, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

dragosm

New Member
Joined
Jan 27, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
You are a lifesaver!
It worked!
Thank you a lot!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,976
Members
416,003
Latest member
indyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top