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

Answer:

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:

How to find common values from two lists
How to remove common values between two columns
Filter common values between two ranges using array formula in excel
Filter values existing in range 1 but not in range 2 using array formula in excel
Highlight common values in two lists using conditional formatting in excel

Download excel sample file for this tutorial.

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

Functions in this article

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