Formula to identify unique values from two columns

Lucasc10

New Member
Joined
Jan 5, 2020
Messages
12
Office Version
2016, 2013
Platform
Windows
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!!!!

1582637018200.png
 

Some videos you may like

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
Joined
Feb 7, 2012
Messages
11,937
Office Version
2013
Platform
Windows
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
Joined
Jan 5, 2020
Messages
12
Office Version
2016, 2013
Platform
Windows
Hi,

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

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,937
Office Version
2013
Platform
Windows
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
Joined
Jan 5, 2020
Messages
12
Office Version
2016, 2013
Platform
Windows
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
Joined
Jun 12, 2014
Messages
41,280
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,190
Messages
5,473,034
Members
406,843
Latest member
David_Welland

This Week's Hot Topics

Top