Unique List From 2 Columns

Joined
Jul 1, 2019
Messages
2
...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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
628
A B C D
321111
451234
123451
51
51
32

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

Code:
=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.
 
Last edited:

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
628
Formula given in previous post does not work properly.
Here is the revised formula.

Code:
=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

Well-known Member
Joined
Nov 6, 2013
Messages
628
If numbers in both list are required.

ABCD
3211 11
4512 34
1234 51
51 45
51
32





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

Code:
=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)),""))
 

Watch MrExcel Video

Forum statistics

Threads
1,101,995
Messages
5,484,082
Members
407,428
Latest member
keongymonopoly

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top