Forum Discussion

ERBAE200's avatar
ERBAE200
Copper Contributor
Jul 30, 2022

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 one result is allowed to be in B.
How can I mark them all red?

 

I also tried to create a function, which is looking for the same number in B, 5 rows above and 5 below.

 

We are talking about 12000 rows and my functions mostly went to an error.

 

Thank you so much in advance.

 

Greetings

excel
Formulas and Functions
  • DLSMIC's avatar
    DLSMIC
    Copper Contributor
    Jul 30, 2022

    ERBAE200,

     

    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))

     

     

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jul 30, 2022

    ERBAE200 

    Sub red()

Dim i As Long
Dim entryinA As Long
Dim entriesinB As Long

Range("A:B").Interior.ColorIndex = -4142

For i = 1 To 12000

If Cells(i, 1).Value = Cells(i + 1, 1).Value Then

entryinA = entryinA + 1

Else

entriesinB = Application.WorksheetFunction.CountA(Range(Cells(i - entryinA, 2), _
Cells(i, 2)))

If entriesinB > 1 Then

Range(Cells(i - entryinA, 1), Cells(i, 2)).Interior.ColorIndex = 3

Else
End If

entryinA = 0

End If

Next i

End Sub

    Maybe with these lines of code. In the attached file you can click the button in cell E2 to run the code.

Resources