Excel Advanced Filter with Multiple Criteria

Marhar

New Member
Joined
Dec 31, 2013
Messages
3
Hi there

I want to filter data in two steps. First, I want to extract certain data based on specific criteria and paste this on a separate sheet. Then, I also want to paste the data not specified by the criteria (original data excluding the values filtered in the first step) on another sheet.

I managed to do the first step but I don't know how to specify my criteria to get all the data except those filtered in the first step.

I have the following example:

Cost Center

<tbody>
</tbody>
Cost element name

<tbody>
</tbody>
1
Wages - Basic

<tbody>
</tbody>
2
Cleaning

<tbody>
</tbody>
1
Wages - Overtime

<tbody>
</tbody>
3
Wages - Basic

<tbody>
</tbody>
2
Wages - Basic

<tbody>
</tbody>
4
Wages - Overtime

<tbody>
</tbody>
4
Maintenance

<tbody>
</tbody>
3
Wages - Overtime

<tbody>
</tbody>
1
Cleaning

<tbody>
</tbody>
3
Maintenance

<tbody>
</tbody>

<tbody>
</tbody>

First, I extracted Cost Centres 1 and 3 with Cost Elements starting with "Wages". The criteria is in A1:B3 and the results underneath.

Cost Center

<tbody>
</tbody>
Cost element name

<tbody>
</tbody>
1Wages*
3Wages*
Cost Center

<tbody>
</tbody>
Cost element name

<tbody>
</tbody>
1
Wages - Basic

<tbody>
</tbody>
1
Wages - Overtime

<tbody>
</tbody>
3
Wages - Basic

<tbody>
</tbody>
3
Wages - Overtime

<tbody>
</tbody>

<tbody>
</tbody>

For the next step, I now want all the data except the values above, e.g.

Cost CenterCost element name
1
Cleaning

<tbody>
</tbody>
2
Cleaning

<tbody>
</tbody>
2
Wages - Overtime

<tbody>
</tbody>
3
Maintenance

<tbody>
</tbody>
4
Wages - Overtime

<tbody>
</tbody>
4
Maintenance

<tbody>
</tbody>

<tbody>
</tbody>

but filtering with <>1, <>3 and <>Wages* only gives me the following:

Cost CenterCost element name
2
Cleaning

<tbody>
</tbody>
4
Maintenance

<tbody>
</tbody>
1
Cleaning

<tbody>
</tbody>
3
Maintenance

<tbody>
</tbody>

<tbody>
</tbody>

...therefore excluding all Cost Elements starting with "Wages". But for the second step I still want to see "Wages" that are not in Cost Centre 1 or 3, for instance, I don't want it to throw out Cost Centres 2 and 4 with "Wages*".

Is there a way to specify that the filter should show everything but the values in the criteria? Otherwise, how should I set up the criteria to get the desired results?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi and Welcome to MrExcel,

Another way to specify criteria for Advanced Filter is to have one or more cells with formula that evaluate to True when applied to rows of the data source.

Using the layout shown below enter the formula shown in Cell D2, leave D1 blank and specify D1:D2 as the Criteria Range.
Excel Workbook
ABCD
1Cost CenterCost element name
21Wages - BasicFALSE
32Cleaning
41Wages - Overtime
53Wages - Basic
62Wages - Basic
74Wages - Overtime
84Maintenance
93Wages - Overtime
101Cleaning
113Maintenance
Sheet
 
Upvote 0
Hi Marhar
Welcome to the board

Is there a way to specify that the filter should show everything but the values in the criteria? Otherwise, how should I set up the criteria to get the desired results?

Jerry already posted a solution using a formula which I also think it's the easiest solution. I would negate it, though.

=OR(AND(A2<>1,A2<>3),LEFT(B2,5)<>"Wages")


Just out of curiosity, you could have a solution similar to yours:



<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >F</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >G</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >H</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >I</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Cost Center</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Cost Center</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Cost element name</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "><>3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "><>Wages*</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "><>1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "><>Wages*</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "><>1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; "><>3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=6 style="background:#9CF; padding-left:1em" > [Book1]Sheet8</td></tr></table>
 
Upvote 0
Thanks for the feedback guys.

Your solutions seem to work on this simple example with only two cost centres but how should I go to work if I have a total of 95 different cost centres (not just 4 as in the example) and where 55 of those are the cost centres I don't want to extract (like cost centre's 1 and 3 used in the example)?

Using an OR function is not effective then because you have 55 arguments instead of only two as shown in the example. Do you have any advice on this?
 
Upvote 0
Thanks for the feedback guys.

Your solutions seem to work on this simple example with only two cost centres but how should I go to work if I have a total of 95 different cost centres (not just 4 as in the example) and where 55 of those are the cost centres I don't want to extract (like cost centre's 1 and 3 used in the example)?

Using an OR function is not effective then because you have 55 arguments instead of only two as shown in the example. Do you have any advice on this?

One option would be to list the cost centres you want to extract in a range, then use a match formula to test whether the cost center is in that list.
Using your first example, if you made place 1,3 in a range that you name "CostCentres", you can use this formula to extract your first list...

=AND(ISNUMBER(MATCH(A2,CostCentres,0)),LEFT(B2,5)="Wages")

Then this formula to extract all items not in the first filter result set ...
=NOT(AND(ISNUMBER(MATCH(A2,CostCentres,0)),LEFT(B2,5)="Wages"))

Or using the negated version that PGC recommended....
=OR(ISNA(MATCH(A2,CostCentres,0)),LEFT(B2,5)<>"Wages")

PGC, My rational for the formula I first suggested was to try to keep things simple if the OP is doing these extractions in pairs
=ExtractCriteriaFormula
=Not(ExtractCriteriaFormula)

I can see how the negated form you suggested is cleaner by itself; however do you think there's some benefit to using the form I suggested if the criteria formulas will be made in pairs?
 
Last edited:
Upvote 0
Hi Jerry

I sincerely think that in this case it's the way you look at the condition. "must be equal to one of these values" is equivalent to "cannot be different from all these values".

I agree completely with you that keeping things simple in term of the rational is more important than having as smaller formula. Whatever feels the more natural approach that's the one the OP should choose.
 
Upvote 0
Jerry,

I tried those formulas you suggested but it didn't give the correct values. However, your suggestions did open up some opportunities for me to use within the Advanced Filter so I used your formulas as a guide and tested all the different parts with my data until I got the results I wanted.

In the end, this is the formulas I used:

First Extraction:
=AND((ISNUMBER(MATCH(A2,CostCentres,0)))=TRUE,(LEFT(D2,5)="Wages")=TRUE)

Second Extraction (for all the values not extracted with the above filter):
=NOT(AND((ISNUMBER(MATCH(A2,CostCentres,0)))=TRUE,(LEFT(D2,5)="Wages")=TRUE))
 
Upvote 0
The formulas should yield the exact same results. I notice the column containing the Cost Element Names changed from B to D and perhaps you got different results while making that transition.

When developing a criteria formula, you can copy the formulas down in adjacent columns to test if they will yield the desired results.
If you do so with the two formulas (one that worked, one that didn't), it could help identify the problem.
In my test, they yield the same True/False results.
Excel Workbook
ABCDEFG
1Cost CenterField2Field3Cost element name
21Wages - BasicFALSEFALSE
32CleaningTRUETRUE
41Wages - OvertimeFALSEFALSE
53Wages - BasicFALSEFALSE
62Wages - BasicTRUETRUE
74Wages - OvertimeTRUETRUE
84MaintenanceTRUETRUE
93Wages - OvertimeFALSEFALSE
101CleaningTRUETRUE
113MaintenanceTRUETRUE
Sheet
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top