Formula to identify unique values from two columns

[Lucasc10]

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

 

[JLGWhiz]

To eliminate the unique values in column A this in cell B2 and copy down.

=IF(COUNTIF(C:C,A2)=1,A2,"")

Then in cell D2 put this and copy down to eliminate unique values in column C.

=IF(COUNTIF(A:A,C2)=1,C2,"")

 

[Lucasc10]

Hi,

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

 

[JLGWhiz]

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.

=IF(COUNTIF(C:C,A2)=0,A2,"")

 

[Lucasc10]

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.

=IF(COUNTIF(C:C,A2)=0,A2,"")

Thank you!

 

[Fluff]

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.

 

[JLGWhiz]

Thank you!

You're welcome and note the warning from @Fluff on crossposting.
Regards, JLG