List Comparison

[SiH23]

I would be most grateful if someone could help.

I have two lists residing in Column A and Column B respectively. Both lists
contain 4 figure numbers. Column A contains the 'complete list' and Column B
only contains 'some' of the entries from Column A.

I would like Column A compared to Column B and any numbers that are not in
Column B but are in Column A put in Column C.

Many thanks in advance.

 

[Eduardo]

hi,
in column C enter and then copy formula down

=IF(ISERROR(MATCH($A$1:$A$6,$B$1:$B$6,0)),$A$1:$A$6,"")

Change range to fit your needs

 

[Per Jessen]

Hi

Put this in C1 and copy down as required (expand the range if needed).

=IF(COUNTIF($B$1:$B$100,"=" & A1)=0,A1,"")

The value of column A will show in column C (same row as in column A) if it
is not in column B.

Hopes this helps.
....
Per

 

[Max]

Assume source data as described are in row2 down
In C2: =IF(A2="","",IF(COUNTIF(B:B,A2),"",ROW()))
In D2: =IF(ROWS($1:1)>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROWS($1:1))))
Copy C22 down to the last row of data in col A. Hide away/minimize col C.
Col D will return the required results all neatly packed at the top. Success?
hit the YES below