Advanced Filter NOT Criteria with Partial Text

[big44life]

Hello,

I am having trouble with this as I would like to use Advanced Filter using NOT criteria for partial text.

I have customer names in Column A and Revenue in Column B. The customer names contain various store name and numbers so I would like to filter out customers that don't contain the criteria.

For example, I would like to filter out all customers that are not "CVS", "Walgreens", and "RITE AID" from the list of customers even as some customer names are CVS 123, etc.


Is there a way to do this with Advanced Filter?

Any help would be great! Thanks!

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe would be possible using a formula as criteria, but we need more details about your data and expected results.

A small data sample (~10 rows) along with expected results would be helpful.

M.

 

[big44life]

Thanks for the reply! here is an example of the data:

Column A Column B
CVS 123 1200
CVS 998 633
RITE AID 224 686
Walgreens 34 356
Jo's Liquor 188
Sammy's Restaurant 96
CVS 976 458
RITE AID 190 589

My goal is to use an advanced filter to extract the customers that are not CVS, Walgreens, or RITE AID.

I thought possibly adding an additional column with an IF function to label each record "National Account" and "Non-National" and filtering from there but was hoping there was a way in Advanced Filter.

Appreciate the help!

 

[Marcelo Branco]

Are the data exactly like?

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Header1​

[/td][td]

Value​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

CVS 123​

[/td][td]

1200​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

CVS 998​

[/td][td]

633​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

RITE AID 224​

[/td][td]

686​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Walgreens 34​

[/td][td]

356​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Jo's Liquor​

[/td][td]

188​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Sammy's Restaurant​

[/td][td]

96​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

CVS 976​

[/td][td]

458​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

RITE AID 190​

[/td][td]

589​

[/td][/tr]
[/table]

M.

 

[big44life]

Correct! I apologize about the formatting in my last reply.

 

[Marcelo Branco]

Try something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Header1​

[/TD]
[TD]

Value​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Exclude​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

CVS 123​

[/TD]
[TD]

1200​

[/TD]
[TD][/TD]
[TD]

FALSE​

[/TD]
[TD][/TD]
[TD]

CVS​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

CVS 998​

[/TD]
[TD]

633​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Walgreens​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

RITE AID 224​

[/TD]
[TD]

686​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

RITE AID​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Walgreens 34​

[/TD]
[TD]

356​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Jo's Liquor​

[/TD]
[TD]

188​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Sammy's Restaurant​

[/TD]
[TD]

96​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

CVS 976​

[/TD]
[TD]

458​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

RITE AID 190​

[/TD]
[TD]

589​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Exclusion list in F2:F4

Leave D1 empty
Formula in D2
=SUMPRODUCT(--ISNUMBER(SEARCH(F$2:F$4,A2)))=0

Then try Advanced Filter
List Range: $A$1:$B$9
Criteria Range: $D$1:$D$2

After filter

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Header1​

[/TD]
[TD]

Value​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Jo's Liquor​

[/TD]
[TD]

188​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Sammy's Restaurant​

[/TD]
[TD]

96​

[/TD]
[/TR]
</tbody>[/TABLE]


Hope this helps

M.

 

[big44life]

Fantastic! Thank you for the help!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.