How do I search if column A values do not exist in column B?

The function you are looking for is PROCV

With the formula I put below, you are scanning the $ A $ 1: $ A $ 5 range for the content that will be in the cell marked in the first parameter (in this case it is B1 ). If it does not find the value, it returns " no ".

Place this formula in the result column and drag down to auto complete in the lines below.

=SE.ERRO(PROCV(B1;$A$1:$A$5;1;0);"não")

The result should be:

  [   A   ] [   B   ] [ RESULTADO ]
1  valor1     valor1    =SEERRO(PROCV(B1;$A$1:$A$5;1;0);"não")
2  valor2     valor2     valor2
3  valor2     valor3     não
4  valor4     valor4     valor4
5  valor5     valor6     não

Se need more details, access the documentation that is very well explained

The countif function can also be used, but as I know only in VBA, it follows the code. That can be converted to Excel Function or be created as UDF.

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets(1)
rLastA = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
rLastB = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

For i = 1 To rLastA

    Set c = ws.Cells(i, 1)
    Set rng = ws.Range(ws.Cells(1, 2), ws.Cells(rLastB, 2))

    x = Application.WorksheetFunction.CountIf(rng, c)

    If x < 1 Then Valores = Valores & vbNewLine & c
Next i
MsgBox Valores

Can be performed with functions by

Count if the value of one cell in column A is equal to that of all in column B; and then if the counter value is 0 (no match), then returns the cell value name of column A if there is no match

Other ways to do it with VBA is by using .Find , Loop com condicional , Loop de Autofiltro and Scripting.Dictionary , and if the spreadsheet is too large the last two are more efficient.

There are several ways to do what you asked for, one of which would be to highlight the cell for a quick view. My suggestion is to use conditional formatting as follows:

FollowtheFormulabelow:

=SE(E($A1<>"";ÉERROS(PROCV($A1;B:B;1;FALSO)));VERDADEIRO;FALSO)

This is one of the ways to do it, but like everything else in Excel, there are always several ways to solve the same problem, it's up to everyone's taste.

Here is an answer with something similar for reference:

Excel Cell Verification and Filling

Try this:

Code Snippet

Sub excluirvalores()
x = 1
'O loop While corre de celula em celula até a ultima celula preenchida
While Cells(x, 1).Value <> ""
y = x + 1
While Cells(y, 1).Value <> ""
'Verifica se o valor é o mesmo
If Cells(y, 1).Value = Cells(x, 1).Value Then
'exclui a celula se o valor for o mesmo
Cells(y, 1).Delete Shift:=xlUp
End If
y = y + 1
Wend
x = x + 1
Wend
End Sub

Note: The Cells (x, 1) statement refers to cell A1 if the value of x is 1, if the value of x is 2, it will refer to cell A2.

   Para Fazer Referencia à celula B1 mude a Instrução de Cells(x,1) para Cells(x,2) e assim por diante.

Hope it helps

Hugs