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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
631
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
631
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
631
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)),""))
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,308
Messages
5,510,523
Members
408,794
Latest member
Eddie74

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top