Forum Discussion
Finding / marking errors with formula depending on two rows
Hello, I have attached an example file to understand my issue. I want to use a formula who is able to detect if a number in row A has more than one number in row B. Per each number in A only...
you can count the number of values in col B for the value in A with the following formula:
=COUNT(FILTER($B:$B,$A:$A=$A1))
Put the formula in C1 and copy it down.
To mark all the erroneous rows red create a conditional formatting rule with the following formula:
=IF(NOT(ISBLANK($A1)),COUNT(FILTER($B:$B,$A:$A=$A1))<>1)
(Create the rule in cell A1 and then change the range ("Manage rules…") to $A:$B)
Alternatively, you can list all the erroneous values of A:
=LET(Avalues,FILTER(UNIQUE($A:$A),UNIQUE($A:$A)<>""),numberofBvalues,MAP(Avalues,LAMBDA(a,COUNT(FILTER($B:$B,($A:$A=a))))),FILTER(Avalues,numberofBvalues<>1))