How come AdvancedFilter is not working? 1

[feipezi]

Hi,

I tried to filter and copy part of a data tab to another tab where some manipulation may be carried out. Here is part of the statements:


....
Range(ThisWorkbook.Names("rngtest")) _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("temptab").Range("t1:aa2"), _
CopyToRange:=Sheets("temptab").Range("a1"), Unique:=False
Sheets("temptab").Activate
....


You can see the CriteraRange has 8 fields (column T to AA). If any of the cells (Range("t1:aa2")) left blank, no data will come out; or I have to change the range to, say, Range("t1:t2") if cell t2 has value in it. So I can get data.

I remember it worked before: if 7 out of 8 fields are blank, the data was supposed to come out based on the single field with value in it. But it's not happening.

AdvancedFilter is a pain in the neck. It won't give you error code for you to trace upon. No error, no data, nothing.

Thanks in advance.

 

[SkipVought]

hi,

It would be helpful to 1) post some sample data, 2) post your criteria range.

Can you do what you want to do ON THE SHEET WITHOUT CODE?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Burser]

If your criteria are being driven by formula, then Advanced Filter won't count that criteria as a null criteria.

I've had a play and I don't seem to be able to solve it - but then, I've not tried very hard.

 

[feipezi]

Hi,

Thanks for the quick return.

I don't quite understand what Skip said "ON THE SHEET WITHOUT CODE". Please explain.

In NewCriteria, to avoid AdvancedFilter problems that I mentioned before, I have to "compress" the Criteria range to make sure there is no blanks.

Sub GetNmRng is to set up dynamic Named Ranges based on the filtered output, after dedupping and sorting.

In another word, if AdvancedFilter works the way I want, I can skip NewCriteria.

Here is some data, hopefully it's presentable:

Part of Range("rngtest"):

state ou region district pod tgt_flag account_type acct
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
*********************************
criteria range ($T$1:$AA$2)

state ou region district pod tgt_flag account_type acct
=CA = = =2XFHB:SAN DIEGO SOUTH = = = =



Sub NewCriteria()
Sheets("temptab").Activate
i = 0
For Each c In Range("t1:aa1")
If c.Offset(1).Value <> " " Then
i = i + 1
hdr = c.Value
vlu = c.Offset(1).Value
s_col = Columns("aj").Column
Cells(1, s_col + i).Value = hdr
Cells(2, s_col + i).Value = vlu
End If
Next
If Cells(2, "ak") = "" Then
Cells(2, "ak") = "NJ"
Range("al1:ar1").Clear
End If
End Sub

Sub GetNmRng()
Application.ScreenUpdating = False
On Error Resume Next
Sheets("temptab").Range("A1").CurrentRegion.Clear
ThisWorkbook.Names("dummyrng").Delete
On Error GoTo 0
Call NewCriteria
Sheets("temptab").Activate
Cells(1, "ak").CurrentRegion.Name = "dummyrng"
Range(ThisWorkbook.Names("rngtest")) _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("temptab").Range("dummyrng"), _
CopyToRange:=Sheets("temptab").Range("a1:h1"), Unique:=False
Sheets("temptab").Activate
totrows = Cells(1, 1).CurrentRegion.Rows.Count
For i = 1 To 8
Sheets("temptab").Activate
Cells(1, i).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Cells(1, i), Cells(totrows, i)).RemoveDuplicates Columns:=1, Header:=xlNo
If i < 8 Then Cells(1, i).Value = "All"
Next
With Selection
.Sort Key1:=Cells(.Row, .Column), Order1:=xlAscending, Header:=xlGuess
End With
Cells(1, 8).Value = "All"
On Error Resume Next
ThisWorkbook.Names("acct_PT").Delete
ThisWorkbook.Names("state_PT").Delete
ThisWorkbook.Names("ou_PT").Delete
ThisWorkbook.Names("reg_PT").Delete
ThisWorkbook.Names("dist_PT").Delete
ThisWorkbook.Names("pod_PT").Delete
ThisWorkbook.Names("acctype_PT").Delete
ThisWorkbook.Names("tgt_PT").Delete
On Error GoTo 0
Range(Cells(1, 1), Cells(Cells(1, 1).End(xlDown).Row, 1)).Name = "state_PT"
Range(Cells(1, 2), Cells(Cells(1, 2).End(xlDown).Row, 2)).Name = "ou_PT"
Range(Cells(1, 3), Cells(Cells(1, 3).End(xlDown).Row, 3)).Name = "reg_PT"
Range(Cells(1, 4), Cells(Cells(1, 4).End(xlDown).Row, 4)).Name = "dist_PT"
Range(Cells(1, 5), Cells(Cells(1, 5).End(xlDown).Row, 5)).Name = "pod_PT"
Range(Cells(1, 6), Cells(Cells(1, 6).End(xlDown).Row, 6)).Name = "tgt_PT"
Range(Cells(1, 7), Cells(Cells(1, 7).End(xlDown).Row, 7)).Name = "acctype_PT"
Range(Cells(1, 8), Cells(Cells(1, 8).End(xlDown).Row, 8)).Name = "acct_PT"
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

Can you perform your stated AutoFilter on the sheet, only using Excel native functionality (WITHOUT CODE)?

If you cannot do what you want on the sheet, you cannot do it via code!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[feipezi]

Hi again,

I forgot mentioning something. Please check the following criteria range from another report. I do have blank cells in it but it works all the time.

Thanks in advance.

Those cells are full of formulas but still working.

state ou region district pod tgt_flag account_type acct
=CA = = =2XFHB:SAN DIEGO SOUTH = = = =

The range is $E$11:$M$12.

Here is some data:

tln ff_name keyacct account_type Mkt metric prod_grpdesc qtr1 ltchng stchng sumkeyaccttc per_of_geo mktutil trx4 trx3 trx2
2XPDA:ARECIBO FFNAME Total COMMERCIAL ARB MKT TRx Share DIOVAN+HCT 23.08% -12.81% 348,596 100.00% 26.01% 24.29% 23.51% 23.21%
2XPDA:ARECIBO FFNAME MMM HEALTHCARE/AVETA - MED D MED D ARB MKT TRx Share DIOVAN+HCT 8.36% -39.05% -1.38% 68,550 19.66% 23.83% 9.86% 8.37% 8.68%
2XPDA:ARECIBO FFNAME STATE MANAGED CARE COMMERCIAL ARB MKT TRx Share DIOVAN+HCT 0.67% -1.79% -0.60% 64,880 18.61% 7.01% 0.92% 0.59% 0.63%
2XPDA:ARECIBO FFNAME MCS/MEDICAL CARD SYSTEM (PR) - MED D MED D ARB MKT TRx Share DIOVAN+HCT 29.27% -12.71% -2.51% 44,783 12.85% 26.65% 30.70% 30.06% 29.14%
2XPDA:ARECIBO FFNAME AMERICAN HEALTH (PR) - MED D MED D ARB MKT TRx Share DIOVAN+HCT 39.45% -11.68% -3.26% 28,908 8.29% 25.67% 41.89% 39.52% 39.01%
2XPDA:ARECIBO FFNAME FIRST MEDICAL HEALTH PLAN (PR) COMMERCIAL ARB MKT TRx Share DIOVAN+HCT 27.07% -4.67% -1.55% 24,285 6.97% 41.26% 28.53% 27.95% 27.06%
2XPDA:ARECIBO FFNAME CASH CASH ARB MKT TRx Share DIOVAN+HCT 22.43% -6.92% -2.62% 16,206 4.65% 18.45% 23.99% 20.66% 24.77%
2XPDA:ARECIBO FFNAME FIRST MEDICAL HEALTH PLAN (PR) - MED D MED D ARB MKT TRx Share DIOVAN+HCT 28.70% -6.24% 0.39% 11,054 3.17% 33.22% 26.33% 28.58% 29.41%
2XPDA:ARECIBO FFNAME BCBS TRIPLE-S (PR) COMMERCIAL ARB MKT TRx Share DIOVAN+HCT 25.73% -1.64% -1.32% 9,422 2.70% 44.47% 27.17% 27.52% 26.36%
2XPDA:ARECIBO FFNAME MC21 MEDICARE D - MED D MED D ARB MKT TRx Share DIOVAN+HCT 12.39% -4.82% -1.78% 9,379 2.69% 28.41% 12.99% 13.11% 11.77%
2XPDA:ARECIBO FFNAME MC-21 CORPORATION UNSPEC COMMERCIAL ARB MKT TRx Share DIOVAN+HCT 26.76% -3.52% -1.15% 9,013 2.59% 44.97% 26.95% 28.39% 25.15%
2XPDA:ARECIBO FFNAME MAPFRE LIFE INSURANCE (PR) - MED D MED D ARB MKT TRx Share DIOVAN+HCT 34.56% -7.90% -0.14% 6,923 1.99% 32.33% 37.21% 35.20% 35.05%
2XPDA:ARECIBO FFNAME WALGREENS HLTH INIT UNSPEC COMMERCIAL ARB MKT TRx Share DIOVAN+HCT 21.18% -7.68% -1.14% 5,696 1.63% 35.98% 20.21% 20.09% 20.23%
***************
And the code:


Sub cmdRunQuery_ClickCode()

Application.ScreenUpdating = False
'On Error GoTo Fixit

Dim wsC As Worksheet
Dim wsH As Worksheet
Dim wsE As Worksheet
Dim wsF As Worksheet
Dim wsG As Worksheet

Dim strSelection As String

Set wsC = Worksheets("DataView")
Set wsH = Worksheets("AreaKeyAcctMetrics")
Set wsE = Worksheets("RegionKeyAcctMetrics")
Set wsF = Worksheets("DistrictKeyAcctMetrics")
Set wsG = Worksheets("PodKeyAcctMetrics")
Set wsR = Worksheets("RDAMKeyacctMetrics")

strSelection = Range("D14").Text
ActiveSheet.Range("y19:ag1000").FormatConditions.Delete
If strSelection = "Region" Then

wsE.Range("RegionKeyAcctMetrics") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsC.Range("E11:M12"), _
CopyToRange:=wsC.Range("A17:BA17"), _
Unique:=False
ElseIf strSelection = "OU" Then

wsH.Range("AreaKeyAcctMetrics") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsC.Range("E11:M12"), _
CopyToRange:=wsC.Range("A17:BA17"), _
Unique:=False
ElseIf strSelection = "District" Then

wsF.Range("DistrictKeyAcctMetrics") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsC.Range("E11:M12"), _
CopyToRange:=wsC.Range("A17:BA17"), _
Unique:=False
ElseIf strSelection = "Pod" Then

wsG.Range("PodKeyAcctMetrics") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsC.Range("E11:M12"), _
CopyToRange:=wsC.Range("A17:BA17"), _
Unique:=False
ElseIf strSelection = "RDAM" Then

wsR.Range("RDAMKeyAcctMetrics") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsC.Range("E11:M12"), _
CopyToRange:=wsC.Range("A17:BA17"), _
Unique:=False
Else: Exit Sub
End If
Call FormatGeoTotal
'If Range("I14") = "" Then
' Call FormatGeoTotalAll
'Else: Call FormatGeoTotal
'End If
Application.ScreenUpdating = True
'Exit Sub
Range("C11").Select
'Fixit: MsgBox "Please Make The Selection Smaller"
End Sub

 

[Burser]

You've made life very difficult for us there, buddy.

That paste isn't delimited in any way we can quickly format into Excel (commas, for example).

And please stick code in

tags. Keeps things fixed width and easyer to read.

Can you go back and give is the data in CSV format?

Though all I plan to do is exactly what Skip said - put the data on the sheet and go click the AdvancedFilter button myself and see if it works. If it doesn't work from the Ribbon direct, it's not going to work from code.

I'm not sure how the second criteria block could filter the second data set - it doesn't share any common fields.

 

[SkipVought]

Come on man!!! Do you really want help? Think about us poor slobs that are trying to understand your problem!!!

So you post data for Maine, and right off, your criteria is for California!

And then the LATEST example has these headings...
[tt]
tln ff_name keyacct account_type Mkt metric prod_grpdesc qtr1 ltchng stchng sumkeyaccttc per_of_geo mktutil trx4 trx3 trx2
[/tt]
where bear no relationship to your criteria...
[tt]
state ou region district pod tgt_flag account_type acct
[/tt]

Guess what that tells me???



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[feipezi]

I attached CSV data below but not sure if to be presented as CSV format.

Sorry to give you the wrong range in my previous post. Please focus on the following data now.

Thanks.


criteria range:
state ou region district pod tgt_flag account_type acct
=ME = = =1XAAA:BANGOR = = = =
********************************
sample data:
state ou region district pod tgt_flag account_type acct
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target CASH CASH
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM BCBS WELLPOINT/ANTHEM/WELLCHOICE
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM AETNA INC.
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM MEDCO HLTH SOLUTIONS UNSPEC
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM MEDCO HLTH SOLUTIONS UNSPEC
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM MEDCO HLTH SOLUTIONS UNSPEC
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target COMM MEDCO HLTH SOLUTIONS UNSPEC
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target MailOrder NON MEDCO 3RD PARTY MAIL ORDER
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target MailOrder NON MEDCO 3RD PARTY MAIL ORDER
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target MailOrder NON MEDCO 3RD PARTY MAIL ORDER
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target MailOrder NON MEDCO 3RD PARTY MAIL ORDER
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target MailOrder NON MEDCO 3RD PARTY MAIL ORDER
ME 1X:EAST 1XA:BOSTON 1XAA:MAINE / NH / VT 1XAAA:BANGOR N-Non Pull-Through Target MailOrder NON MEDCO 3RD PARTY MAIL ORDER

 

[SkipVought]

I can see that you really do not care.

1) you did not post COMMA SEPARATED DATA!

2) your criteria does not match your data as your bangor column has no heading!

Please do not take up our time with inconsistent lazy examples.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[feipezi]

It's not "I don't care"; it's because I did not know what I uploaded was not a CSV file.

I saved the file as a CSV file and then cut and paste.

I don't know what made you think the range doesn't match the data.

It is a perfect match to me.

Never mind what I posted.

Thanks anyway.

 

[SkipVought]

A CSV has COMMAS separating the data in each column.

SaveAs your sheet as a .csv text file.

Then OPEN the .csv file IN NOTEPAD, not with Excel!!! COPY from the NOTEPAD.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[feipezi]

Here is the data. I guess that's what you mentioned "comma delimited text data".

The criteria range that's not working:
state,ou,region,district,pod,tgt_flag,account_type,acct
, ,1XA, , , ,COMM,

The range that will work (from the macro NewCriteria below):
region,account_type
1XA,COMM

But I am expecting the non-working range to be working too.

The data:
state,ou,region,district,pod,tgt_flag,account_type,acct,product,Oct-2010,Nov-2010,Dec-2010
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,ATACAND+HCT,7.9,8.8,3.5
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,AVAPRO+AVALIDE,10.6,9.5,14
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,AZOR,1,0,4.3
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,BENICAR+HCT,13.8,12,18.2
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,COZAAR+HYZAAR,6.6,6.1,14.5
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,DIOVAN+HCT,33.6,27.7,27.4
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,EXFORGE+HCT,0,0,0
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,LOSARTAN+HCT,36.7,49.6,35
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,MICARDIS+HCT,8.2,4.8,7.8
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,TEVETEN+HCT,0,1.6,0
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,TRIBENZOR,0,2.6,0
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,TWYNSTA,1.3,0,0
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,CASH,CASH,VALTURNA,0,0,0
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,ATACAND+HCT,46.6,31.1,50
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,AVAPRO+AVALIDE,54,60.2,86.9
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,AZOR,3,0,16.1
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,BENICAR+HCT,141.1,129.1,133.5
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,COZAAR+HYZAAR,7.9,7.8,6.3
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,DIOVAN+HCT,440,454.3,497.2
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,EXFORGE+HCT,15,3.1,8
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,LOSARTAN+HCT,665.6,719.8,728.2
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,MICARDIS+HCT,50.9,40.4,49.3
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,TEVETEN+HCT,0,3.3,0
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,TRIBENZOR,5.4,12.3,12
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,TWYNSTA,3.4,0,0
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,BCBS WELLPOINT/ANTHEM/WELLCHOICE,VALTURNA,8.3,10.6,6.5
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,ATACAND+HCT,3.7,7.6,2.4
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,AVAPRO+AVALIDE,18.5,14,8.3
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,AZOR,1.7,2,1.9
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,BENICAR+HCT,44.8,42.1,38.4
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,COZAAR+HYZAAR,1.1,1.3,1.2
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,DIOVAN+HCT,120.6,118.2,128.3
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,EDARBI,0,0,0
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,EXFORGE+HCT,0,0,1.6
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,LOSARTAN+HCT,129.5,149.2,131.5
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,MICARDIS+HCT,1.1,2.2,5.5
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,TEVETEN+HCT,0,0,0
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,TRIBENZOR,0,0,0
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,AETNA INC.,VALTURNA,0,1.2,0
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,MEDCO HLTH SOLUTIONS UNSPEC,ATACAND+HCT,11.1,5.9,8.3
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,MEDCO HLTH SOLUTIONS UNSPEC,AVAPRO+AVALIDE,13.3,16.5,28.6
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,MEDCO HLTH SOLUTIONS UNSPEC,AZOR,2,0,4.3
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,COMM,MEDCO HLTH SOLUTIONS UNSPEC,BENICAR+HCT,25.2,41.1,25.4
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,MailOrder,NON MEDCO 3RD PARTY MAIL ORDER,ATACAND+HCT,23.4,18.2,18.2
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,MailOrder,NON MEDCO 3RD PARTY MAIL ORDER,AVAPRO+AVALIDE,54.6,62.66,44.2
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,MailOrder,NON MEDCO 3RD PARTY MAIL ORDER,AZOR,7.8,10.4,5.2
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,MailOrder,NON MEDCO 3RD PARTY MAIL ORDER,BENICAR+HCT,93.6,67.6,67.6
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,MailOrder,NON MEDCO 3RD PARTY MAIL ORDER,COZAAR+HYZAAR,7.8,5.2,13
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,MailOrder,NON MEDCO 3RD PARTY MAIL ORDER,DIOVAN+HCT,156,187.46,196.04
ME,1X:EAST,1XA:BOSTON,1XAA:MAINE / NH / VT,1XAAA:BANGOR,N-Non Pull-Through Target,MailOrder,NON MEDCO 3RD PARTY MAIL ORDER,EXFORGE+HCT,7.8,2.6,5.2

Here is the macro I set up to consolidate the non-working range to the working one:


Sub NewCriteria()
Sheets("temptab").Activate
Sheets("temptab").Range("AK1").CurrentRegion.Clear
x = " "
hdr = " "
i = 0
For Each c In Range("t1:aa1")
If c.Offset(1).Value <> " " Then
hdr = c.Value
Cells(1, "ak").Offset(, i) = hdr
x = c.Offset(1).Value
Cells(2, "ak").Offset(, i) = x
i = i + 1
End If
Next
Cells(1, "ak").Activate
End Sub



Thanks in advance.

 

[Gavona]

Interesting. Without code I tried changing the criteria to:
state,ou,region,district,pod,tgt_flag,account_type,acct
, ,1XA[red]*[/red], , , ,COMM,

This worked!

I then reverted to the original criteria.... and it still works...

Gavin

 

[Gavona]

In your criteria range are you sure that the cells are empty? Your CSV format shows a space in between each comma.

I am having trouble repeating my results. Maybe I did something else as well automatically - like delete those space characters.

Gavin

 

[feipezi]

Hi Gavona,

Thanks for checking it out.

No, the space between the commas is not empty. I have formulas in it, like


=IF('Account Trend'!CW$1="All"," ",'Account Trend'!CW$1)


since the fields, except region and account_type, are "All" so you see space between commas.

Again, without consolidation of the criteria range, it will not work. I have no idea why it worked for you, but not for me.

Thanks again.

 

[Gavona]

=IF('Account Trend'!CW$1="All",[red]" "[/red],'Account Trend'!CW$1)
Well that returns a space if the condition is met. That is not an empty cell. So your criteria is looking for a space character in the cells rather than ignoring the criteria condition. It will have a better chance of success if you replaced the red bit with [red]""[/red].

However, in the third post it was suggested that this may not work:

If your criteria are being driven by formula, then Advanced Filter won't count that criteria as a null criteria.

I've had a play and I don't seem to be able to solve it - but then, I've not tried very hard.

If that is correct then I would try replacing the red bit with [red]"*"[/red] ie a wildcard. I have never tried that myself.

If I had your problem this is what I would do to diagnose it:

Write/record a short macro to apply autofilter with a shortcut key. Also put the ShowAll button on one of your toolbars.

Range("alldata").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria").CurrentRegion, _
Unique:=False

1. Correct the formula using [red]""[/red]as I suggested then run the Advancedfilter mmacro.

2. If no success then correct the formula using [red]"*"[/red]then run the Advancedfilter mmacro.

3. If no success revert to [red]""[/red] then convert the criteria range to values (copy, pastespecial, values) and try again

4. If that does not work then clear the contents of each of the criteria cells that you think is blank and try again

Gavin

 

[feipezi]

Gavin,

I don't really know what to say to thank you enough. "...your criteria is looking for a space character in the cells rather than ignoring the criteria condition" pinpointed the culprit. Thanks to your tip and expertise, I think I solved the problem. I did not even go as far as you mentioned. All I did is just change " " to "" in the formula. It worked. No consolidation of the criteria was needed.

Thanks a lot for your help and have a great Thanksgiving.

I'd like to thank the rest of the folks trying to help me out there. Take care.

 

[ItIsHardToProgram]

There is a common recognition here that goes up by a little purple star! Considering how giving people are around here, a few clicks for a purple star, and donations of 1/1000th of what the cost of the expertize would've been is a small thing to do.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.