Excel Tool

Order Online
Our company contact information There you can find frequently asked questions and answers

Home | Products |Purchase | FAQ | Contact Us | Useful Resources

Filter data that based on the criteria range

Using Advanced Filter Wizard,you can easily use complex criteria to filter data. If you want to filter date based on two or more multiple criteria rang, you can use Microsoft Advanced Filter to achieve this effect, but it is tedious and time consuming, this software can let you easily accomplish it.
We will filer data based on Rang 'Name' Or 'Stock' < 5 : So that the result would look similar to this:


Tool location: Add-Ins →Excel Tool Box → Advanced Filter: Shortcuts: (Alt + X + X + F )

The sample table: filter.xlsx

Filer data based on Rang 'Name' (in the sheet2 ) Or 'Stock' < 5:

 

Before you start Advanced Filter Wizard, select a cell in your table and the whole table will be selected automatically.

Open the Advanced Filter pane by clicking on: Add-Ins →Excel Tool Box →Advanced Filter: or pressing the Shortcuts: (Alt + X + X + F ).

1: Click on the down arrow and select column 'Name' from the drop-down list.

2: Click the condition boxes and select the criterion that you need.

3:Click button to select the criteria range (in the sheet2 ).

4: Select the criteria range (in the sheet2 ). you can also select any other range.

5: Click 'Add Criteria >' button to Add another condition:

6: Please Choose OR or AND relationship as you need, then Choose 'or' radio button .

7: Click on the down arrow and select column 'Stock' from the drop-down list.

8: Click on the down arrow and select the condition '< ' from the drop-down list.

9: Enter the condition that you want to use: '5'.

10: Click 'OK' button.

Results:

Reamrk:

 

Click the '+' button to expand and show the unmatched rows.

Click the '-' button to collapse and show the matched rows.

Click the '1' button to collapse and show the matched rows.

Click the '2' button to expand and show the unmatched rows.

 

How to Extract the filter result:

Make sure the unmatched are collapsed, then Click Add-Ins →Excel Tool Box → Copy Visible Rows to New Sheet: Shortcuts: (Alt + X + X + V )

 

Results: The matched (visible) rows will be copied to a new worksheet.

How to clear filter: Click Add-Ins →Excel Tool Box → Clear Filter: Shortcuts: (Alt + X + X + C )

How to invert filter: Click Add-Ins →Excel Tool Box → Invert Filter: Shortcuts: (Alt + X + X + I )


Wildcard, Blank examples: filter by other.xlsx

 

Wildcard Filter examples:

Filter the rows where 'Product' is like 'A*B':

( * is the wildcard, use * to represent any series character, 'A*B' matches 'ATB', 'ATTB', 'ATK b', 'a tt b'... )

'AB*' matches 'ABC', 'ABCD', 'ABC DD'...

'AB *' matches 'AB C', 'AB CD', 'AB C DD'... , do not match 'ABC', 'ABO', 'ABO OO'.

'A*B *' matches 'ACB D', 'ACCB DD' ,'ACCCB DDS'..., do not match 'ACBDD', 'ACCBO'.

 

Filter the rows where 'Product' is like 'A?B':

( ? is the wildcard, use ? to represent any single character, 'A?B' matches 'ATB', 'AoB', 'Aeb', 'acb', 'acb M'... )

'AB ?' matches 'AB C', 'AB O' ,'AB K', 'AB KD' ..., do not match 'ABD', 'ABOO'.

'? AB' matches 'X AB', 'T AB' ,'C AB'..., do not match 'XAB', 'XX AB'.

 

Filter Blank examples:

Filter data based on column Product = blanks


Complex filtering examples: complex filter.xlsx

(A and B) or (C and D)

(A or B) and (C or D)

(A or B) not in (C or D)

Condition A, B, C, D can be single value can also be a criteria range.


Filter the rows where 'Product' is like 'A*B':

7. Select column 'Product' and Click on the down arrow and select 'Like(* is wildcard)' from the drop-down list.

(If you select '=' , will exact match 'A*B')

Enter the condition 'A*B' , then click 'OK' button.

Results: Filter the rows where 'Product' is like 'A*B'.


Filter the rows where 'Product' is like 'A?B':

Select column 'Product' , condition 'Like(* is wildcard)' , then Enter the condition 'A?B' .

Results: Filter the rows where 'Product' is like 'A?B'.


Filter data based on column Product = blanks

1. Select column 'Stock' and Click on the down arrow and select 'Blank' from the drop-down list.

2. Click 'OK' button.

Results: Filter data based on column Product = blanks:


(A and B) or (C and D)

If you want to filter data based on the following criteria:

 

 1: A > '3' and B = 'MT'

 2: C < '50' and D = 'US'

And between these two criteria (1,2) the relationship is or : (A > '3' and B = 'MT') or (C < '50' and D = 'US')

1. Select column 'A' , condition '> ' , then Enter the condition '3' .

2. Click 'Add Criteria >' button.

3. Choose 'and' radio button.

4. Select column 'B' , condition '= ' , then Enter the condition 'MT' .

5. Click 'OK' button.

Results:

6. Once again open the Advanced Filter pane ( Add-Ins →Excel Tool Box →Advanced Filter)

7. Click on the down arrow and select 'Add to Result: Add to currently visible rows any row that based on the criteria range' from the drop-down list.

8. Select column 'C' , condition '< ' , then Enter the condition '50' .

9. Click 'Add Criteria >' button.

10. Choose 'and' radio button.

11. Select column 'D' , condition '= ' , then Enter the condition 'US' .

12. Click 'OK' button.

Results: (A > '3' and B = 'MT') or (C < '50' and D = 'US')


(A or B) and (C or D)

If you want to filter data based on the following criteria:

1: A > '3' or B = 'MT'

2: C < '50' or D = 'US'

And between these two criteria (1,2) the relationship is and.

(A > '3' or B = 'MT') and (C < '50' or D = 'US')

 

Open the Advanced Filter pane ( Add-Ins →Excel Tool Box →Advanced Filter)

1. Select column 'A' , condition '> ' , then Enter the condition '3' .

2. Click 'Add Criteria >' button.

3. Choose 'or' radio button.

4. Select column 'B' , condition '= ' , then Enter the condition 'MT' .

 

5. Click 'OK' button.

6. Once again open the Advanced Filter pane ( Add-Ins →Excel Tool Box →Advanced Filter)

7. Click on the down arrow and select 'Filter within Result: Only show currently visible rows that based on the criteria range' from the drop-down list.

Operation same as 1 to 5 steps.

Results: (A > '3' or B = 'MT') and (C < '50' or D = 'US')


(A or B) not in D

If you want to filter data based on the following criteria:

1: A > '3' or B = 'MT'

2: D = {'US' ,'CA'}.

And between these two criteria (1,2) the relationship is not in.

Filter the rows(A > '3' or B = 'MT') that do NOT have  'US' or 'CA'

(A > '3' or B = 'MT') not in D = { 'US' ,'CA' }

Open the Advanced Filter pane ( Add-Ins →Excel Tool Box →Advanced Filter)

Operation same as above steps:

6. Once again open the Advanced Filter pane ( Add-Ins →Excel Tool Box →Advanced Filter)

7. Click on the down arrow and select 'Exclude from Result: Hide rows that based on the criteria range' from the drop-down list.

8. Select column 'D' , condition '= ' , then Enter the condition '3' .

9.Click button to select the criteria range ( 'US' ,'CA' ).

10. Select the criteria range. you can also select any other range.

5. Click 'OK' button.

Results: (A > '3' or B = 'MT') not in D = { 'US' ,'CA' }


Another method of achieving

Copyright © 2001-2014 Excel-Tool All Rights Reserved.

Copyright Excel-Tool All rights reserved