CODEWITHSUNDEEP
rvbaexcelexcel-formula
Roy1245
Roy1245

Reputation: 507

How to check for each unique value we have a same unique id in excel

I have a excel sheet which looks like:

Col1    Col2
IJ-123  A2B1
IJ-123  A2B1
IJ-456  C2C2
IJ-456  c2c2
IJ-456  D1e2
IJ-789  LJ87
IJ-789  LJ98

I want to add one more column and check (for each Unique Col2 Value) whether the assigned values in Col1 are TRUE or FALSE.

So far i have tried this: =IF(B2=B1,IF(A2=A1,"TRUE","FALSE")) which is working fine but for every first column of new value in Col2 it gives me "FALSE" Result.

Output:

Col1    Col2  Result
IJ-123  A2B1  TRUE
IJ-123  A2B1  TRUE
IJ-456  C2C2  TRUE
IJ-456  c2c2  TRUE
IJ-456  D1e2  FALSE
IJ-789  LJ87  TRUE (Because Col2 count=1 for this value)
IJ-789  LJ98  TRUE (Because Col2 count=1 for this value)

Upvotes: 1

Views: 367

Answers (2)

CallumDA
CallumDA

Reputation: 12113

I think your logic is actually very complicated, and not explained so well in the initial question -- hence the long discussion in the comments. This is the logic as I now understand it:

  1. If there are more than 1 of the value in col2, then check that the corresponding col1 values just for those col2 values are the same
  2. If there is only one of the col2 values then check that the col1 is unique but only against the col1 values of multiple-occurring col2 values

Here is the formula:

=IF(COUNTIF($B$2:$B$8,B2)=1,SUMPRODUCT(--(($A$2:$A$8=A2)*(COUNTIF($B$2:$B$8,$B$2:$B$8))>1))=0,COUNTIFS($B$2:$B$8,B2,$A$2:$A$8,"<>"&A2)=0)

Which returns the following, as required:

enter image description here

Upvotes: 3

Karl Kristjansson
Karl Kristjansson

Reputation: 328

You are missing the false for "b2=b1".

This should fix it:

=IF(B2=B1,IF(A2=A1,"TRUE","FALSE"),"FALSE")

To simplify, instead of a double if you could use the AND function.

If(AND(B2=B1,A2=A1),"True","False")

Upvotes: 0

Related Questions