Finding / marking errors with formula depending on two rows

[ERBAE200]

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

[#2]

@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.

[#3]

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