Find unique values from two columns

Copper Contributor

This is my first time posting so bear with me. I need to be able to find only the cells that have a unique number in that set.

For example, in the columns below I would only need to identify the entries in red. 

16121515
16121515
16121515
16121525
16121515
16121515
16121515
21531818.73
21531818.73
79844115
79844115
79844125
79844115

 

Thank you in advance for any assistance. 

 

4 Replies

@Amy1349 

Let's say your data is in A1:B13. You can use UNIQUE to return rows only appearing once.

 

=UNIQUE(A1:B13,,1)

 

@Amy1349 

You've already received a solution if you can apply the UNIQUE function. If you don't have Office365 or Excel 2021 you can try this:

=SUMPRODUCT(($A$1:$A$17=$A1)*(COUNTIFS($A$1:$A$17,$A1,$B$1:$B$17,$B1)=1))>0

You can apply this rule if you want to conditionally format the values.

=IF(SUMPRODUCT(($A$1:$A$17=A1)*(COUNTIFS($A$1:$A$17,A1,$B$1:$B$17,B1)=1))>0,"True","")

This formula returns "true" in column D if there is a unique number in the set.

unique number in set.JPG 

@Patrick2788 Thank you for the quick response. That worked beautifully except I forgot to add in my post that some of the entries will have a duplicate of one of the numbers like the example below. Sorry I thought I had enough of the range in the original post. 

 


161252 13.1
161252 13.1
161252 13.1
161252 13.1
161252 13.1
161252 15.03
161252 15.03





 

 

 

The UNIQUE formula above would not return these. Are you looking to pull straight unique values from these?