Highline Excel 2013 Class Video 37: Excel Advanced Filter Extract Data 26 Examples Basic - Advanced
Download workbook: http://people.highline.edu/mgirvin/Al...
This is the Highline Community College Class, Spreadsheet Construction taught by Michael Girvin: Basic To Advanced Excel.
Topics in this video:
1. Advanced Filter: Cross Between Filter and Database Functions (00:34 min)
2. Advanced Filter 3 Areas: (01:31 min)
Criteria Area
Data Set
Extract Area: Be sure to keep area below CLEAR!!!! (Example at 19:33 min and 24:14 min and 27:42 min)
3. Advanced Filter is in Sort & Filter group on the Data Ribbon Tab (02:13 min)
4. Advanced Filter Keyboard: Alt, A, Q or Alt, D, F, A (02:23 min)
5. Advanced Filter dialog box: (02:53 min)
Action: In Place or Copy To Another Location
List: Proper Data Set
Copy to location
Unique Records
6. Extract with 1 condition (02:53 min)
7. Extract with AND criteria: Same Row (04:02 min)
8. Extract with OR Criteria: Different Rows (04:50 min)
9. Defined Names Created by Advanced Filter For Criteria Area and Extract Area (05:16 min and 19:10 min)
10. AND and OR Criteria (05:46 min)
11. Don't Need all fields (06:23 min)
12. Between Criteria for numbers: Repeat Field Names (07:03 min)
13. Between Criteria for Dates: Repeat Field Names (07:43 min)
14. Extract to Different Sheet: Start on sheet you want to extract data to (08:17 min)
15. Extract to Different Workbook: Start in workbook you want to extract data to (09:10 min)
16. Unique List (11:14 min)
17. Unique List with Criteria (David Method) (12:33 min)
18. Unique Records (14:38 min)
19. Which to use: Filter or Advanced Filter? (15:43 min)
20. Filter with 4 criteria (AND and OR Criteria) (16:11 min)
21. Advanced Filter with 4 criteria (AND and OR Criteria) (17:42 min)
22. Run Advanced Filter Multiple Times (18:57 min)
23. Advanced Filter Replaces Data Below the Extract Area (19:33 min and 24:14 min and 27:42 min)
24. Helper Column with Logical Formula to Extract every 5TH record (20:35 min)
Extract every nth record
25. Advanced Filter Formula with Logical Formula to Extract every 5TH record (24:49 min)
Formula and empty cell above used as Criteria for Advanced Filter (25:41 min)
26. Advanced Filter Formula to Extract Items In List 2 That Are NOT In List 1 (28:32 min)
Extract items not in list. Compare two lists. Compare two lists extract records not in List
Excel Extract Unique List with Criteria David Method