# Formula to identify unique values from two columns

#### Lucasc10

##### New Member
Hello everyone,

I am struggling to come up with a formula to find every unique value between two different columns. I would want it to identify a unique number only if it is unique between the two columns i.e if "5417" is in once on column A and once on Column B I am fine with that. That would make it a duplicate.

The ultimate goal is to identify these so I can delete them out and see the same numbers essentially in both columns. I can't do conditional formatting and filter because my data is 60,000+ rows long and it just won't work.

I am perfectly fine with some sort of helper column. Thanks!!!! ### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### JLGWhiz

##### Well-known Member
To eliminate the unique values in column A this in cell B2 and copy down.
Code:
``=IF(COUNTIF(C:C,A2)=1,A2,"")``
Then in cell D2 put this and copy down to eliminate unique values in column C.
Code:
``=IF(COUNTIF(A:A,C2)=1,C2,"")``

#### Lucasc10

##### New Member
Hi,

I would actually like it to be the ones that are unique when both columns are combined together. Thanks!!

#### JLGWhiz

##### Well-known Member
The ultimate goal is to identify these so I can delete them out and see the same numbers essentially in both columns
Guess I misunderstood this statement. So if you change the 1 to 0 in both formulas, it would then list the items unique to each column.
Code:
``=IF(COUNTIF(C:C,A2)=0,A2,"")``

#### Lucasc10

##### New Member
Guess I misunderstood this statement. So if you change the 1 to 0 in both formulas, it would then list the items unique to each column.
Code:
``=IF(COUNTIF(C:C,A2)=0,A2,"")``
Thank you!

#### Fluff

##### MrExcel MVP, Moderator
Cross posted Identify Unique Values from 2 colums

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

• Lucasc10