Filter words only not containing.

[LEFROGUE]

Hello,

i am wondering how to filter words in a data set. e.g men, male without it being part of other words (lament, female etc).

Is there a way of using advanced filter for this rather than a formula?

Thankyou

 

[Skyybot]

Use Exact Match.

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this is any use. In my sample the top table is "Table1" and the bottom table is "Table2"

LEFROGUE.xlsm
	B
1
2	Words to Filter:
3	Men
4	Male
5	males
6
7
8
9	Data set
10	female
11	men
12	lament
13	male
14	menopause
15	male
16	mens
17
18
Sheet1

Sub Fltr()
  ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:=Split(Join(Application.Transpose(Range("Table1[Words to Filter:]")))), Operator:=xlFilterValues
End Sub

Result:

LEFROGUE.xlsm
	B
1
2	Words to Filter:
3	Men
4	Male
5	males
6
7
8
9	Data set
11	men
13	male
15	male
17
18
Sheet1

 

[DanteAmor]

Hi and welcome to MrExcel.

Is there a way of using advanced filter

With advanced filter use this. Notice that in cell C2 there is a formula.

Libro1
	A	B	C
1	DATA SET		Words
2	female		FALSO
3	men
4	lament
5	male
6	menopause
7	male
8	mens
9	tamales
10	malestar
11	males
Hoja1

Cell Formulas
Range	Formula
C2	C2	=ISNUMBER(SEARCH("|"&A2&"|","|Men|Male|males|"))

List range: $A$1:$A$11
Criteria range: $C$1:$C$2

Result:

Libro1
	A
1	DATA SET
3	men
5	male
7	male
11	males
Hoja1

--------------------​

Another option is with an auxiliary column and your table of words, then you only filter by column B those that are different from error:

Libro1
	A	B	C	D
1	DATA SET	Aux		Words
2	female	#N/A		Men
3	men	1		Male
4	lament	#N/A		males
5	male	2
6	menopause	#N/A
7	male	2
8	mens	#N/A
9	tamales	#N/A
10	malestar	#N/A
11	males	3
Hoja2

Cell Formulas
Range	Formula
B2:B11	B2	=MATCH(A2,$D$2:$D$4,0)

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[LEFROGUE]

Hi and welcome to MrExcel.



With advanced filter use this. Notice that in cell C2 there is a formula.

Libro1
	A	B	C
1	DATA SET		Words
2	female		FALSO
3	men
4	lament
5	male
6	menopause
7	male
8	mens
9	tamales
10	malestar
11	males
Hoja1

Cell Formulas
Range	Formula
C2	C2	=ISNUMBER(SEARCH("|"&A2&"|","|Men|Male|males|"))

List range: $A$1:$A$11
Criteria range: $C$1:$C$2

Result:

Libro1
	A
1	DATA SET
3	men
5	male
7	male
11	males
Hoja1

--------------------​

Another option is with an auxiliary column and your table of words, then you only filter by column B those that are different from error:

Libro1
	A	B	C	D
1	DATA SET	Aux		Words
2	female	#N/A		Men
3	men	1		Male
4	lament	#N/A		males
5	male	2
6	menopause	#N/A
7	male	2
8	mens	#N/A
9	tamales	#N/A
10	malestar	#N/A
11	males	3
Hoja2

Cell Formulas
Range	Formula
B2:B11	B2	=MATCH(A2,$D$2:$D$4,0)

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Hi Dante,
Thanks for the help.

How do you do it if there are multiple words in first column e.g 'male pain' or 'symptoms of man flu'

How do you filter those out aswell?

Regards

James

 

[Peter_SSs]

How do you do it if there are multiple words in first column e.g 'male pain' or 'symptoms of man flu'

Could you please give us a better representative set of sample data (with XL2BB) and also show us the expected results?

Did you try the vba suggestion that I made?

 

[LEFROGUE]

excle mr.xlsx
	B	C	D
2	Keyword		Keyword
3	Dogs		menstrual breast pain
4	men		breastfeeding pain
5	male		mastitis pain
6	Goat		male mastitis
7	Cow		breast pain
8	Bovine		breast tenderness
9	Foxes		menopause breast pain
10	Cats		fibrocystic breast disease
11	Foxes		mastalgia
12			mastitis symptoms
13			tender breast meaning
14			nipple pain
15			sternum pain
16			boobs sore after period
17			foxes mastitis
18			pain under left breast
19			sharp pain in left breast that comes and goes
20			mastitis treatment
21			engorgement
22			breast abscess
23			breast pain before period
24			sharp pain in breast
25			pain in middle of chest bone
26			pain under breast bone above stomach
27			pain under right breast
28			men sore breast
29			pain between breasts in center of chest
30			sore boobs
31			left breast pain
32			breast pain causes
33			sharp pain under left breast
34			pain under my left breast and ribs
35			sharp pain in breast that comes and goes
36			right breast pain
37			pain in upper stomach under breasts
38			sharp pain under left breast that comes and goes
39			bovine mastitis
40			female chest pain left side above breast
41			pain between breasts in center of chest female
42			painful lump in breast
Sheet1

Hi Peter, here it is. Thanks for help

 

[Peter_SSs]

Hi Peter, here it is.

OK, thanks. What about this? (do it manually)

and also show us the expected results

And also what about this question?

Did you try the vba suggestion that I made

 

[LEFROGUE]

Hi peter , have over 5000 lines of data for 30 data sets will take a while doing mannually. Tried the VBA you suggested, do not know how to use it. Would you mind using that mini sheet to use it please?

 

[Peter_SSs]

You still have not given the expected results for that sample data in post #7.
I need that to be sure that I understand what it is you are trying to do. You are obviously very familiar with your data and requirement but we helpers have absolutely no idea other that what you show us and tell us in detail.