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]

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 64"] A[/TD]
[TD="class: xl65, width: 64"] B[/TD]
[TD="class: xl65, width: 64"] C[/TD]
[TD="class: xl65, width: 64"] D[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD="align: right"]51[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]51[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]51[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]32[/TD]
[TD="class: xl66, align: right"]11[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66, align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]45[/TD]
[TD="class: xl66, align: right"]12[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66, align: right"]34[/TD]
[/TR]
[TR]
[TD="class: xl66, align: right"]12[/TD]
[TD="class: xl66, align: right"]34[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66, align: right"]51[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66, align: right"]51[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66, align: right"]45[/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66, align: right"]51[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66, align: right"]32[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]




[/TD]
[/TR]
</tbody>[/TABLE]
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)),""))