How to search for values in column A that do not exist in column B?

Question

For example, I have a spreadsheet:

  [   A   ] [   B   ]
1  valor1     valor1
2  valor2     valor2
3  valor3     valor3
4  valor4     valor4
5  valor5     valor6

You can see that in column B, there is no valor5, what I wanted is to find the values that have in column A but are not in B. I have already searched formulas, etc, but I can not do it. Would anyone have any idea how to do that?

Answer 1

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

1. Select column "A" and click and Conditional Formatting > New Rule:

2. Select option 1 (from the image), insert the formula below in 2 and format the cell in 3 as desired:

Below is the Formula:

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

This is one of the ways to do, but as in everything in Excel, there are always several ways to solve the same problem, it is up to the taste of each.

Here is an answer with something similar for reference:

Checking and Filling Excel Cells

Answer 2

The function you seek is the PROCV

With the formula I put below, you are scanning the range $A$1:$A$5 in search of the content that will be in the cell marked in the first parameter (which in this case is the B1). If you do not find the value, it will return "nay".

Place this formula in the result column and drag it down to auto complete on 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

If you need more details, access the documentation that very well explained

Answer 3

The countif function can also be used, but as I know perform only in VBA, 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 the logic of

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

Other ways to perform 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.

Answer 4

Try it like 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 instruction Cells(x,1) refers to cell A1 if the value of x is 1, if the value of x is 2 it refers to cell A2.

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

I hope it helps

Hugs