Advanced Filter (does not end with)

[ThePan2002]

Hello. I was trying to filter some data with the "Does not end with" logic in the autofilter...but I need 3 conditions instead of two. So, I created an advanced filter using the does not contain logic.

I have a list of Cities..with "City" being the heading. Each city has a hyphen and a couple of letters describing the county. Here are some examples: Shelbyville-S, Pana-CH, Neoga-CU.

Elsewhere I have three adjacent cells labeled "City" for my filtering. I have the conditions on different rows (so it's treated as an OR).

I have: ="<>*-S*" ; ="<>*-CH*" ; ="<>*-M*"

They work fine individually, but not together. I was wondering if someone knew how to use the "Does not end with" logic...I think that it'd be a lot easier to say that it doesn't end in "-M" or "-S".

Thanks.

 

[Aladin Akyurek]

Hello. I was trying to filter some data with the "Does not end with" logic in the autofilter...but I need 3 conditions instead of two. So, I created an advanced filter using the does not contain logic.

I have a list of Cities..with "City" being the heading. Each city has a hyphen and a couple of letters describing the county. Here are some examples: Shelbyville-S, Pana-CH, Neoga-CU.

Elsewhere I have three adjacent cells labeled "City" for my filtering. I have the conditions on different rows (so it's treated as an OR).

I have: ="<>*-S*" ; ="<>*-CH*" ; ="<>*-M*"

They work fine individually, but not together. I was wondering if someone knew how to use the "Does not end with" logic...I think that it'd be a lot easier to say that it doesn't end in "-M" or "-S".

Thanks.

Should the result list include Istanbul-MP, given your criteria?

 

[ThePan2002]

Well...as of right now...I'm dealing with a small enough list that I don't have anything except for -M...but you are right...I only want -M...so it really should be does not end with.

You know how to do it?

 

[GorD]

Not quite sure how you sort it, but it is the logic that is at fault.- the OR condition

You have asked for does not end with -s OR -CH OR -M.


Well they all don't end with at least two of them so all records are false

The filter is working but has nothing to filter as all records don't meet the criteria.

 

[ThePan2002]

Thank you GorD. You were completely correct about the logic. I've made that mistake on this filter before...I don't know why I think it should be OR.

And, you were right too, Aladin...the filter would include cells that I don't want.

Maybe anyone else can help with the "does not end with" part?

Thanks for all your help thus far.

 

[Aladin Akyurek]

...
And, you were right too, Aladin...the filter would include cells that I don't want.

Maybe anyone else can help with the "does not end with" part?

...

My question was and still is: Given...

City
Shelbyville-S
Pana-CH
Neoga-CU
Istanbul-MP

what is the expected result list?

 

[ThePan2002]

...
And, you were right too, Aladin...the filter would include cells that I don't want.

Maybe anyone else can help with the "does not end with" part?

...

My question was and still is: Given...

City
Shelbyville-S
Pana-CH
Neoga-CU
Istanbul-MP

what is the expected result list?

Neoga-CU and Istanbul-MP would be left.



If the list included:
Findlay-M
Pana-CH
Neoga-CU
Istanbul-MP
Shelbyville-S

Then the result would be Istanbul-MP and Neoga-CU.

 

[Aladin Akyurek]

...
And, you were right too, Aladin...the filter would include cells that I don't want.

Maybe anyone else can help with the "does not end with" part?

...

My question was and still is: Given...

City
Shelbyville-S
Pana-CH
Neoga-CU
Istanbul-MP

what is the expected result list?

Neoga-CU and Istanbul-MP would be left.



If the list included:
Findlay-M
Pana-CH
Neoga-CU
Istanbul-MP
Shelbyville-S

Then the result would be Istanbul-MP and Neoga-CU.

aaAdvFilter thepan2002.xls
	A	B	C	D
1	City		-S
2	Shelbyville-S		-CH	FALSE
3	Pana-CH		-M	City
4	Neoga-CU			Neoga-CU
5	Istanbul-MP			Istanbul-MP
6
Sheet1

C1:C3 houses the Ends-With values of interest

Criteria range is D1:D2.

D1 must be left empty.

D2 houses:

=ISNA(MATCH(REPLACE(A2,1,SEARCH("-",A2)-1,"")&"*",$C$1:$C$3,0))

 

[ThePan2002]

Thank you. I really appreciate your help.

Is there an easy way to add another condition to the filter? I want to filter by City (as you have done), and also filter out any "Agents" named REMAX. I had planned on just adding it to the filter, but I didn't realize that it would be a formula.

Also, if I want to filter a list in a different Sheet, do I just put Sheet1! in front of the values?

 

[Aladin Akyurek]

Thank you. I really appreciate your help.

Is there an easy way to add another condition to the filter? I want to filter by City (as you have done), and also filter out any "Agents" named REMAX. I had planned on just adding it to the filter, but I didn't realize that it would be a formula.

Also, if I want to filter a list in a different Sheet, do I just put Sheet1! in front of the values?

Sheet1 (Source)

aaAdvFilterExtended thepan2002.xls
	A	B	C	D
1	City	Agent
2	Shelbyville-S	BRIAN
3	Pana-CH	JON
4	Neoga-CU	REMAX
5	Istanbul-MP	DAMON
6
Sheet1

Sheet2 (Destination)

aaAdvFilterExtended thepan2002.xls
	A	B	C	D	E	F
1	-S		Agent		City	Agent
2	-CH	FALSE	REMAX		Neoga-CU	REMAX
3	-M
4	REMAX
5
6
7
Sheet2

A1:A4 is used to record the criteria of interest.

B1:C2 is the criteria range with:

B1 left empty;

B2:

=ISNA(MATCH(REPLACE(Sheet1!A2,1,SEARCH("-",Sheet1!A2)-1,"")&"*",$A$1:$A$3,0))

C1: Agent

C2:

=A4

Make sure that you follow the recipe below:

1. Select B1:C2.

2. Activate Data|Filter|Advanced Filter.

3. Check the option "Copy to another location".

4. Set List range to: Sheet1!$A$1:$B$5

5. Set Criteria range to: $B$1:$C$2

6. Set Copy to to: $E$1

7. Leave the option "Unique records only" unchecked.

8. Click OK.