Forum Discussion

Amy1349's avatar
Amy1349
Copper Contributor
Nov 04, 2022

Find unique values from two columns

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. 

 

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

     

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    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's avatar
      Amy1349
      Copper Contributor

      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





       

       

       

      • Patrick2788's avatar
        Patrick2788
        Silver Contributor
        The UNIQUE formula above would not return these. Are you looking to pull straight unique values from these?

Resources