Advanced Filter

[carddard]

Hi guys,

I'm using advanced filter and allow me to give you an example of my problem:
One of the criteria is for e.g. "CL". That means I only want those with "CL". However, those with "CLS", "CLABC", "CLAM" are also picked up. I only want those with exactly "CL".

Anybody can help?

Thanks!

 

[Stormseed]

If I understand correctly then you are trying to filter the records which only have "CL" as its value ?

If so then you can write an IF() condition and do a filter on the results of the IF() condition.

 

[carddard]

Hi. I think you've misunderstood my question.
I am using advanced filter and "CL" is only one of the criteria.
Because normal autofilter cannot achieve what I want, I am using advanced filter.
Isn't advanced filter supposed to give me exactly what I want?
Why is it picking up those with "CL" in them and not those with exactly "CL"?

 

[Stormseed]

Can you post an example please ? Perhaps, the example will give a clear picture of what you are trying to do ?

 

[jeffreybrown]

If just ran an advanced filter with your criteria, at least if I understand correctly, and came up with this.

Chech copy to another location

List Range: $I$13:$K$15
Criteria Range: $L$13:$L$14
Copy to: $M$13

Check unique records only

Sheet222

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"><COL style="WIDTH: 91px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Account</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Line#</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Comments</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Account</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Account</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Line#</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Comments</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">C</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">345</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">big</TD><TD style="TEXT-ALIGN: center">CL</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">CL</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">346</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">fun</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">CL</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">346</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">fun</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">CLA</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">324</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">do</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[carddard]

Let me show you an example so you can try it for yourself:

Download sample data

 

[pgc01]

Hi carddard

Why is it picking up those with "CL" in them and not those with exactly "CL"?

That's the way the Advanced Filter works. It will give you anything starting with "CL".

For an exact match use in the criteria:

="=CL"

 

[carddard]

Thanks.