# Unique List From 2 Columns

#### Richard_at_Excelpers

##### New Member
...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.

### 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
 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

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
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
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

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)),""))``

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...