DigDB           Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes? (more ...)

---

Features | Testimonial | Download ^{free 15-day trial} | Purchase | FAQ | Contact

Filter Excel Table (List) - Multiple Criteria, Nested Conditions

Practice file - filter-demo.xls (16k)

1. Click into any cell within your table area.
2. Invoke 'DigDB->Complex Filter->Filter...'.

   

3. The table area will be automatically selected to include all the continuous data area around the current cell. The auto-selected range is the table to filter.

   

4. Set filter criteria:
   • Use the dropdown boxes to select column name and match type ( '=', 'like', '<', '>', 'Blank' ...)
   • Use the input box to enter the value to match.
     • You can type the value into the input box directly
     • Or, click into the input box, then go to the sheet, and click the cell(s) to match, then the cell address will show in the input box. (use ctrl+click for multiple cells)
   • If you have multiple criteria, like 'A or B and C ...', use 'Add Criteria' button to add criteria rows.

   

   Click 'Add Criteria' to add another criteria. Your criteria is now: black or roan in 'color', or have a star in 'body_mark'

   

5. Click 'OK' to filter, get result. Matched is shown, unmatched will be hidden beneath the '+' sign. You can click the '+' to expand and show the unmatched rows. You can also click the small '2' to expand, and click '1' to retract.

   

6. Expanded to show the unmatched rows.

   

7. Extract filter result. Make sure the unmatched are retracted, click 'DigDB->Complex Filter->Extract Result->Copy to New Sheet', the matched (visible) rows will be copied to a new sheet.

   

   You can click 'DigDB->Clear Filter' to get rid of the filter and the '+', '1', '2' signs to go back the simple list.

8. Result in a new sheet. The sheet will be automatically created.

   

More complex filter criteria:

• filter chestnut horses between age 1 and 5

  This is a series of 'and' conditions. Click 'Add Criteria' button to add more criteria.

  

• filter 5-yr old chestnuts or 3-yr old blacks

  This is more complex. The criteria is (A and B) or(C and D). This filter can be done in 2 steps.

  Step 1: Complex Filter->Filter... A and B
  Step 2: Complex Filter->Add to Result... C and D

  

  

  

• filter horses whose age > 4 or < 2 with a star or are Piebald

  This is opposite to the above. The criteria is (A or B) and(C or D). This filter can be done in 2 steps.

  Step 1: Complex Filter->Filter... A or B
  Step 2: Complex Filter->Filter within Result... C or D

  

  

  

• filter chestnut horses that do NOT have star or strip

  Use 'Complex Filter->Exclude...'. This filter can be done in 2 steps.

  Step 1: Complex Filter->Filter... A
  Step 2: Complex Filter->Exclude... C or D

  

  

  

• filter horses whose names appear in another list

  Suppose besides the original table 'horses', we have another table 'my horses', how to filter the 'horses' table so that all horses are 'my horses'? Or similiarly, how to exclude from the 'horses' table so that none are 'my horses'? You can set the criteria like this:

  Go to 'horses' table, invoke 'Complex Filter->Filter' or 'Complex Filter->Exclude'

  

  Click into the input box, then you can click the sheet tab 'MyHorses' to go to where 'my horses' table is.

  

  In 'my horses' table, you can just click and drag to select the range of horse names directly. You can also use ctrl+select to select multiple areas. The selected range will show up as address in the input box.

  

  Click 'OK' to filter.

Need more?

• Browse the complete list of DigDB's powerful features listed on the front page.
• If you see errors or things not working, check out our error reference page.

---

Features | Testimonial | Download | Purchase | FAQ | Contact

Copyright © 1999-2007 All rights reserved

DigDB           Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes?

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user

Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.