# Find unique values between two columns

#### horizonflame

##### Board Regular
Hi All

I am comparing two columns to find unique values. I have the below but I need help to stop blank cells returning a yes value.

=IF(ISNA(VLOOKUP(E3,\$B\$3:\$B\$7000,1,FALSE)),"Yes","NO")
I would also like to count the unique values but avoid an array formula. I have the below but not yet working:

=SUM(1/COUNTIF(\$E\$3:\$E\$7000, \$E\$3:\$E\$7000))+SUM(IF(COUNTIF(\$E\$3:\$E\$7000, \$B\$3:\$B\$7000)=0, 1/COUNTIF(\$B\$3:\$B\$7000, \$B\$3:\$B\$7000), 0))
Many thanks for any help

Last edited:

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### DanteAmor

##### Well-known Member
I hope something like this helps you:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:53.23px;" /><col style="width:21.86px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >DATA</td><td >UNIQUE</td><td > </td><td >DATA</td><td >UNIQUE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >data3</td><td >no</td><td > </td><td >data3</td><td >no</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >data4</td><td >yes</td><td > </td><td >data5</td><td >no</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >data5</td><td >no</td><td > </td><td >data7</td><td >no</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >data6</td><td >yes</td><td > </td><td >data9</td><td >yes</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >data7</td><td >no</td><td > </td><td >data11</td><td >yes</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >data8</td><td >yes</td><td > </td><td >data13</td><td >yes</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C3</td><td >=IF(COUNTIF(\$B\$3:\$B\$8,B3)+COUNTIF(\$E\$3:\$E\$8,B3)=1,"yes","no")</td></tr><tr><td >F3</td><td >=IF(COUNTIF(\$B\$3:\$B\$8,E3)+COUNTIF(\$E\$3:\$E\$8,E3)=1,"yes","no")</td></tr></table></td></tr></table>

#### horizonflame

##### Board Regular
@DanteAmor thank you very much! Just the overall count formula now to go

Last edited:

1,102,206
Messages
5,485,348
Members
407,497
Latest member
astaha

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