Forum Discussion
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.
| 161215 | 15 |
| 161215 | 15 |
| 161215 | 15 |
| 161215 | 25 |
| 161215 | 15 |
| 161215 | 15 |
| 161215 | 15 |
| 215318 | 18.73 |
| 215318 | 18.73 |
| 798441 | 15 |
| 798441 | 15 |
| 798441 | 25 |
| 798441 | 15 |
Thank you in advance for any assistance.
4 Replies
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))>0You 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.
Let's say your data is in A1:B13. You can use UNIQUE to return rows only appearing once.
=UNIQUE(A1:B13,,1)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?
