Can advanced filter use include and exclude criteria simulatenously?

[lmoseley7]

I'm trying to use an advanced filter to gather lines where the department number equals my criteria, but exclude lines with a specific account number. For example, say my data has lines where department equals 1500000001 but I don't want lines with that department where the account equals 585270. I've been able to filter for the departments and then filter those results to remove the unwanted accounts, but I was wondering if there was a way to do both at once?

 

[JRS1010]

Yes, use the Custom Filter. First criteria will be equals 1500000001 and the second criteria will be does not equal 585270. Make sure you select the 'And' operator.

 

[lmoseley7]

I'm using an advanced filter, not autofilter. Is there a custom filter option for advanced filter that I'm not familiar with? I should mention that I am pulling information from one worksheet into another using the advanced filter, almost like a query of the data.

 

[Joe4]

See here: Excel 2007: Advanced Filter Using Multiple Criteria - KeyStone Learning Systems

 

[lmoseley7]

I was able to figure out the problem. When I initially tried to combine inclusion and exclusion criteria to my advanced filter, I had the formula for eliminating the account numbers on the same row as the first department that I wanted to include. It occurred to me that when criteria is on the same row, Excel reads this as an and statement, so it was only eliminating lines with account numbers that also had the first department code. By moving the first department code to the row under where the exclusion formula was placed, it would analyze these conditions exclusive of each other.

The formula I'm using to eliminate accounts is:
=ISNA(MATCH('Advanced Filter'!G2,NON_SA,0))

In this formula, 'Advanced Filter' is the worksheet where the results of the advanced filter will be placed. G2 is the first cell where account numbers will be held. NON_SA is the range where the account numbers I want to eliminate are entered.

An example of the criteria range is as follows:

CRITERIA____ DeptID ___________Period
TRUE ___________________________1
___________1101000000 __________1
___________1501100000 __________1
___________1502010000 __________1
___________1504020000 __________1

Joe4, I'm sure your link would probably tell me what I figured out. I'll look it over just in case there's anything new that I can glean from it.

 

[lmoseley7]

I spoke too soon. The next time I went to use the advanced filter as setup above I no longer received the result that I was looking for. I should mention that I'm running these filters through VBA code, so I'm not manually creating them. After considering what was wrong, it ocurred to me that maybe I needed the criteria formula with the department IDs so I replicated the criteria formula on every row. The results were close but some data was not pulled in. After determining which lines were missing, there seemed to be no obvious reason for the exclusion. All data was from period 1, at least one of the three accounts excluded ocurred in other lines that did get selected and the departments on the excluded lines were duplicates of departments that were selected. I found that when toggling between two macros, one that pulled lines based on departments first and then filtering those results for account and the other that attempted to apply both criteria at once, the results of the filter did not always reflect the macro that had run. It's like the criteria being used was saved in the filter and it wasn't actually updating. This is consistent with what I've seen when manually applying advanced filters.

Another odd thing I noticed was this. When trying to determine what lines were returned by the advanced filter, I tried to filter the results to see what accounts/departments were selected and which ones were missing. Before even applying the filter I noticed that the total of the lines changed, just by simply hitting the filter button on the ribbon. Excel 2013 is going crazy. There can be no explanation for this.