Unique List From 2 Columns

[Legacy 436357]

Hello,

I have a full list of 5 digit numbers in Column B and an incomplete list of 5 digit numbers in Column A. How can I have a unique list in Column C?

These will be the missing 5 digit numbers.

Can this be done with formulas?


Thank you very much for your help.

 

[Richard_at_Excelpers]

...if you have column headings, you may need to use...
=IF(COUNTIF(B:B,A=0,A2,"")

Basically, this will only show values in Column C if they don't appear in Col C.

 

[kvsrinivasamurthy]

A	B	C	D
32	11		11
45	12		34
12	34		51
	51
	51
	32

<tbody>
</tbody>

Column A data A2:A5
Column B data B2:B7
ARRAY Formula in D2 then copied down

=IFERROR(INDEX($B:$B,SMALL(IF((COUNTIF($D$1:$D1,$B$2:$B$7)=0)*COUNTIF($A$2:$A$4,$B$2:$B$7)=0),ROW($B$2:$B$7),""),ROWS($D$2:$D2))),"")

How ARRAY formula is entered


Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.

 

[kvsrinivasamurthy]

Formula given in previous post does not work properly.
Here is the revised formula.

=IFERROR(INDEX($B:$B,SMALL(IF((COUNTIF($D$1:$D1,$B$2:$B$7)=0)*COUNTIF($A$2:$A$4,$B$2:$B$7)=0),ROW($B$2:$B$7),""),1)),"")

 

[kvsrinivasamurthy]

If numbers in both list are required.

A	B	C	D
32	11		11
45	12		34
12	34		51
	51		45
	51
	32

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

ARRAY formula in D2

=IFERROR(INDEX($B:$B,SMALL(IF((COUNTIF($D$1:$D1,$B$2:$B$7)=0)*(COUNTIF($A$2:$A$4,$B$2:$B$7)=0),ROW($B$2:$B$7),""),1)),IFERROR(INDEX($A:$A,SMALL(IF((COUNTIF($D$1:$D1,$A$2:$A$4)=0)*(COUNTIF($B$2:$B$7,$A$2:$A$4)=0),ROW($A$2:$A$4),""),1)),""))