Excel Magic Trick 1226: Compare 2 Lists, Extract Items In List 2 That are NOT in List 1 (6 Examples)
Download File:
http://people.highline.edu/mgirvin/excelisfun
.htm
Learn how to Compare 2 Lists,
Extract Items In
List 2 That are NOT in List
1:
1. (00:12)
Problem Description and
Intro To
Topic
2
. (01:57)
Example 1:
Method 1:
MATCH function Helper Column, Sort Z to A,
Copy and Paste
3. (04:15) Example 2:
Method 2: Advanced Filter and Criteria Area with
Empty Field Name and
Logical Formula Criteria
4. (07:59) Example 3: Method 3:
Automat Process with
Excel 2010 or later
Array Formula. See the Functions: IF, ROWS,
ROW, MATCH,
ISNA,
INDEX, AGGREGATE, SUMPRODUCT. This Array Formula does not require
Ctrl +
Shift +
Enter.
5. (15:29) Why you should NOT use IFERROR on
Data Extract Array Formulas
6. (17:17) Example 4: Method 3: Automat Process with
Excel 2007 or before Array Formula. See the Functions: IF, ROWS, ROW, MATCH, ISNA, INDEX,
SMALL, SUMPRODUCT. This Array Formula
DOES require Ctrl + Shift + Enter.
7. (18:25) Example 5: Method 2: Extract E-mails in List 1 that are NOT in List 2.
8. (19:35)
Caveat about Advanced
Filter: Be sure to NOT have data below Extract Area (because it will be deleted when you run
Advance d Filter.
9. (20:24) Example 6: How to Extract Items that are in BOTH lists: Learn how to slight change the formula for all three methods. Use ISNUMBER rather than ISNA.