### Question:

Hi,
I have seen a lot of examples related to unique list ... mais not found what I need.
my problem is the following :
I have 2 lists of items in col A and B
not necessarely sorted
ex :

list1  list2
A       D
E       A
D       G
Z       Z
F       I
C

I need to make a list of items that have dispareared when I compare List2 to list1 and, a list of items thta have appeared in List2 compared to list1
The result expected are :
1. disapearing (in sorted order if possible)
E
F
2. appearing (in sorted order if possible)

C
G
I

Values created in column D have no duplicates. They are sorted from A to Z.

### Array Formula in D2:

=INDEX(\$A\$2:\$A\$6, MATCH(SMALL(IF((COUNTIF(\$D\$1:D1, \$A\$2:\$A\$6)+COUNTIF(\$B\$2:\$B\$7, \$A\$2:\$A\$6))=0, COUNTIF(\$A\$2:\$A\$6, "<"&\$A\$2:\$A\$6)), 1), COUNTIF(\$A\$2:\$A\$6, "<"&\$A\$2:\$A\$6), 0)) + CTRL + SHIFT + ENTER.

Copy cell D2 and paste it down as far as needed.

### Array Formula in D9:

=INDEX(\$B\$2:\$B\$7, MATCH(SMALL(IF((COUNTIF(\$D\$8:D8, \$B\$2:\$B\$7)+COUNTIF(\$A\$2:\$A\$6, \$B\$2:\$B\$7))=0, COUNTIF(\$B\$2:\$B\$7, "<"&\$B\$2:\$B\$7)), 1), COUNTIF(\$B\$2:\$B\$7, "<"&\$B\$2:\$B\$7), 0)) + CTRL + SHIFT + ENTER.

Copy cell D9 and paste it down as far as needed.

### Blog posts you may find interesting:

Compare and filter values in two lists.xls
(Excel 97-2003 Workbook *.xls)

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array, k)
Returns the k-th smallest number in this data set.

ROW(reference)
Returns the row number of a reference

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIF(range;criteria)
Counts the number of cells within a range that meet the given condition