I need to do an analysis of a very big set of data. In column1 I have the reference number. I want to find all rows with the same value in column1. Then I want to search column2 to see if there is more than one value for that specific value in column1.
I have an example here:
So for all the "1"’s in column1 they all have the same value in column2 ("a"). Therefore it should return "TRUE". However all the rows with "2" have different values in column2 and it should return FALSE.
I hope this makes any sense.
>Solution :
Try below formula.
=COUNTA(UNIQUE(FILTER($B$2:$B$19,$A$2:$A$19=D2)))=1
If you do not have FILTER() and UNIQUE() then try this one for non 365 version of excel.
=SUMPRODUCT(($A$2:$A$19=D2)/(COUNTIFS($A$2:$A$19,$A$2:$A$19,$B$2:$B$19,$B$2:$B$19)))=1
