Ir para o conteúdo principal

Compare duas colunas para encontrar duplicatas no Excel (guia completo)


Compare duas colunas para encontrar valores duplicados

Para encontrar valores duplicados entre duas colunas, existem vários métodos disponíveis dependendo de suas necessidades, como destacar duplicatas para uma visão geral ou extraí-las para análise aprofundada. Nesta seção, apresentaremos alguns truques rápidos para realizar esta tarefa no Excel.

Destaque duplicatas em duas colunas com formatação condicional

Destacar duplicatas em duas colunas no Excel é uma maneira eficiente de identificar dados repetidos, especialmente em grandes conjuntos de dados onde a revisão manual é impraticável. Neste caso, a Formatação Condicional é um recurso útil para resolver esta tarefa.

Etapa 1: selecione os dados na coluna da qual deseja destacar as duplicatas

Neste exemplo, selecionarei A2:A10, veja a captura de tela:

Etapa 2: aplicar o recurso Formatação Condicional

  1. Clique Home page > Formatação condicional > Nova regra, veja a captura de tela:
  2. No Nova regra de formatação caixa de diálogo, execute as seguintes operações:
    • 2.1 Selecione Use uma fórmula para determinar quais células formatar do Selecione um tipo de regra caixa de listagem;
    • 2.2 Digite a seguinte fórmula no Formate os valores onde esta fórmula for verdadeira caixa de texto;
      =COUNTIF($B$2:$B$10, A2)>0
    • Note: Na fórmula acima, B2: B10 representa a lista de dados com a qual você deseja comparar, A2 é a primeira célula da coluna de onde você deseja destacar as duplicatas. Esta fórmula verifica se o valor na célula A2 é encontrado em qualquer lugar da coluna B. Modifique as referências das células para se adequar aos seus dados.
    • 2.3 Em seguida, clique Formato botão.
  3. No pop-out formatar células caixa de diálogo, especifique uma cor para destacar os itens duplicados. E clique OK.
  4. Quando ele retorna o Nova regra de formatação caixa de diálogo, clique em OK botão.

Resultado:

Agora, os valores que estão duplicados nas colunas A e B estão destacados na coluna A conforme mostrado a seguir:

Dicas:
  • A regra duplicada em Formatação condicional is não diferencia maiúsculas de minúsculas. Portanto, Apple e apple seriam marcados como duplicados.
  • Se quiser destacar as duplicatas da coluna B, você só precisa selecionar a coluna B primeiro e, em seguida, aplicar a seguinte fórmula em Formatação condicional:
    =COUNTIF($A$2:$A$10, B2)>0

Selecione e destaque duplicatas em duas colunas com uma ferramenta poderosa – Kutools

Às vezes, você pode precisar não apenas destacar, mas também selecionar duplicatas para copiá-las e colá-las em outro local da sua pasta de trabalho. Em tais casos, Kutools for Excel'S Selecionar células iguais e diferentes recurso é uma escolha ideal. Ele pode identificar os valores duplicados ou exclusivos destacando e selecionando as células necessárias.

Note: Se você quiser usar isso Selecionar células iguais e diferentes característica, por favor baixe e instale o Kutools para Excel em primeiro lugar.

Clique Kutools > Selecionar > Selecionar células iguais e diferentes, Na Selecionar células iguais e diferentes caixa de diálogo, execute as seguintes operações:

  1. Selecione os dados de origem e os dados comparados na caixa Encontre valores em e De acordo com o caixas separadamente;
  2. Selecionar Cada fila sob a Baseado em seção;
  3. Escolha Mesmos valores do Encontre seção;
  4. Especifique uma cor de fundo para destacar os valores duplicados sob o Processamento de resultados seção;
  5. Finalmente, clique em OK botão.

Resultado:

Agora, os valores duplicados nas colunas A e B são destacados e selecionados na coluna A, prontos para serem copiados e colados nas células desejadas. Veja a captura de tela:

Dicas:
  • Este recurso suporta distinção entre maiúsculas e minúsculas comparação enquanto marca Maiúsculas e minúsculas caixa de seleção no Selecionar células iguais e diferentes diálogo;
  • Se você deseja selecionar as duplicatas da coluna B, basta trocar as duas colunas selecionadas no Encontre valores em e De acordo com o caixas do Selecionar células iguais e diferentes caixa de diálogo;
  • Para aplicar este recurso, por favor baixe e instale o Kutools para Excel em primeiro lugar.

Encontre e extraia duplicatas em duas colunas com fórmula

Para localizar e extrair duplicatas entre duas colunas, você pode usar a fórmula para localizar e extrair as duplicatas.

Copie e cole a seguinte fórmula em uma célula em branco onde deseja colocar o resultado e arraste a alça de preenchimento para baixo na coluna para aplicar esta fórmula a outras células.

=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)

Note: Na fórmula acima, A2 é a primeira célula da coluna de onde você deseja encontrar as duplicatas; B2: B10 representa a lista de dados com a qual você deseja comparar.

Resultado:

Como você pode ver, se os dados da Coluna A existirem na Coluna B, o valor será exibido; caso contrário, as células ficarão em branco.

Dicas: Esta fórmula é não diferencia maiúsculas de minúsculas.

Selecione duplicatas em duas colunas com código VBA

Esta seção irá guiá-lo pelas etapas para criar um código VBA que identifica e seleciona valores duplicados entre duas colunas.

Passo 1: Abra o editor do módulo VBA e copie o código

  1. Press Alt + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
  2. Na janela aberta, clique em inserção > Módulo para criar um novo módulo em branco.
  3. Em seguida, copie e cole o código abaixo no módulo em branco.
    Código VBA: Encontre e selecione os valores duplicados entre duas colunas
    Sub Compare()
    'Update by Extendoffice
    Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range
    xTitleId = "KutoolsforExcel"
    On Error Resume Next
    Set Range1 = Application.Selection
    Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8)
    Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8)
    Application.ScreenUpdating = False
    For Each Rng1 In Range1
        xValue = Rng1.Value
        For Each Rng2 In Range2
            If xValue = Rng2.Value Then
                If outRng Is Nothing Then
                    Set outRng = Rng1
                Else
                    Set outRng = Application.Union(outRng, Rng1)
                End If
            End If
        Next
    Next
    outRng.Select
    Application.ScreenUpdating = True
    End Sub
    

Etapa 2: execute este código VBA

  1. Depois de colar este código, pressione F5 chave para executar este código. Na primeira caixa de prompt, selecione a lista de dados da qual deseja selecionar duplicatas. E então, clique OK.
  2. Na segunda caixa de prompt, selecione a lista de dados com a qual deseja comparar e clique em OK, veja a captura de tela:

Resultado:

Agora, os valores duplicados das Colunas A e B são selecionados na Coluna A, permitindo preencher as células com cores ou copiá-las e colá-las conforme necessário.

Dicas:
  • Este código VBA é maiúsculas e Minúsculas;
  • Se você deseja selecionar as duplicatas da coluna B, basta trocar as duas colunas selecionadas ao selecionar o intervalo de dados.

Compare duas colunas para correspondências linha por linha

No Excel, muitas vezes é necessário comparar duas colunas linha por linha para verificar correspondências, o que ajuda em tarefas como verificação de registros ou análise de tendências de dados. O Excel possui diferentes maneiras de fazer isso, desde fórmulas fáceis até recursos especiais, para que você possa escolher a melhor para as necessidades dos seus dados. Vamos dar uma olhada em alguns métodos simples para realizar esse trabalho de maneira eficaz.

Compare duas colunas na mesma linha com fórmulas

As fórmulas do Excel oferecem uma abordagem simples, porém poderosa, para comparação de dados entre colunas. Veja como você pode usá-los. Supondo que você tenha dados na Coluna A e na Coluna B, para verificar se os dados nas duas colunas correspondem, as seguintes fórmulas podem ajudá-lo:

Dicas: Essas fórmulas são versáteis, aplicáveis ​​não apenas a texto, mas também a números, datas e horas.
Usando o operador Igual a (=):

Por favor, insira ou copie a seguinte fórmula, pressione Entrar e arraste a alça de preenchimento para baixo para obter todos os resultados. Ele retornará TRUE se os valores na mesma linha das Colunas A e B forem idênticos e FALSE se não forem. Veja a captura de tela:

=A2=B2

Função SE:

Se você quiser tornar a comparação mais informativa, você pode usar o Função IF para exibir mensagens personalizadas.

Por favor insira ou copie a fórmula abaixo, pressione Entrar e arraste a alça de preenchimento para baixo para obter todos os resultados. Ele retornará Match quando os valores forem iguais e No Match quando forem diferentes. Veja a captura de tela:

=IF(A2=B2, "Match", "No Match")
Dicas: Você pode alterar "Match", "No Match" para outras expressões conforme necessário.

Função EXATA:

Se você precisar de uma comparação com distinção entre maiúsculas e minúsculas, o Função EXATA é o caminho a seguir.

Por favor, insira ou copie a seguinte fórmula, pressione Entrar e arraste a alça de preenchimento para baixo para obter todos os resultados. Ele retornará Match quando os valores corresponderem exatamente e No Match quando forem diferentes. Veja a captura de tela:

=IF(EXACT(A2,B2), "Match", "No match")   
Dicas: Você pode alterar "Match", "No Match" para outras expressões conforme necessário.


Selecione e destaque correspondências na mesma linha com uma ferramenta útil - Kutools

Se você precisar selecionar e sombrear as correspondências entre duas colunas linha por linha, em vez de obter o resultado em uma coluna separada, o recurso Comparar células do Kutools para Excel será uma excelente opção. Ele permite que você selecione e aplique rapidamente uma cor de preenchimento às células que correspondem ou diferem em valores dentro de cada linha.

Note: Se você quiser usar isso Comparar células característica, por favor baixe e instale o Kutools para Excel em primeiro lugar.

Clique Kutools > Comparar células, Na Comparar células caixa de diálogo, execute as seguintes operações:

  1. Selecione os dados das duas colunas na Encontre valores em e De acordo com o caixas separadamente;
  2. Selecionar Mesmas células sob a Encontre seção;
  3. Especifique uma cor de fundo para destacar as correspondências sob o Processamento de resultados seção;
  4. Finalmente, clique em OK botão.

Resultado:

Agora, as correspondências da mesma linha são destacadas e selecionadas na coluna A, disponibilizando-as para você copiar e colar nas células desejadas. Veja a captura de tela:

Dicas:
  • Este recurso suporta distinção entre maiúsculas e minúsculas comparação se você verificar o Maiúsculas e minúsculas opção no Comparar células caixa de diálogo;
  • Se você quiser selecionar as correspondências da coluna B, basta trocar as duas colunas selecionadas na Encontre valores em e De acordo com o caixas do Comparar células caixa de diálogo;
  • Para aplicar este recurso, por favor baixe e instale o Kutools para Excel em primeiro lugar.

Compare duas colunas e destaque as correspondências na mesma linha

Comparar duas colunas e destacar correspondências na mesma linha pode ser feito com eficiência usando a Formatação Condicional no Excel. Aqui está um guia para identificar e destacar correspondências de linha:

Etapa 1: selecione o intervalo de dados

Selecione o intervalo de dados em que deseja destacar as correspondências de linha.

Etapa 2: aplicar o recurso Formatação Condicional

  1. Clique Home page > Formatação condicional > Nova regra. No Nova regra de formatação caixa de diálogo, execute as seguintes operações:
    • 2.1 Selecione Use uma fórmula para determinar quais células formatar do Selecione um tipo de regra caixa de listagem;
    • 2.2 Digite a fórmula abaixo no Formate os valores onde esta fórmula for verdadeira caixa de texto;
      =$B2=$A2
    • 2.3 Em seguida, clique Formato botão.
  2. No pop-out formatar células caixa de diálogo, especifique uma cor para destacar os itens duplicados. E clique OK.
  3. Quando ele retorna o Nova regra de formatação caixa de diálogo, clique em OK botão.

Resultado:

Agora, os valores correspondentes na mesma linha são destacados de uma só vez, veja a captura de tela:

Dicas:
  • A fórmula na formatação condicional é não diferencia maiúsculas de minúsculas.
  • Se você pretende destacar células com valores diferentes, aplique a seguinte fórmula:
    =$B2<>$A2

Compare duas colunas e extraia dados correspondentes

Quando você está lidando com dois conjuntos de dados no Excel e precisa encontrar itens comuns de uma lista na outra, as fórmulas de pesquisa são a solução ideal para recuperar essas correspondências.

No Excel, se você tiver uma lista de frutas na coluna A e seus números de vendas na coluna B, agora deseja combiná-los com uma seleção de frutas na coluna D para encontrar suas vendas correspondentes. Como você poderia retornar os valores relativos da coluna B no Excel?

Aplique qualquer uma das fórmulas a seguir necessárias e, em seguida, arraste a alça de preenchimento para baixo para aplicar esta fórmula às demais células.

  • Todas as versões do Excel:
    =VLOOKUP(D2, $A$2:$B$6, 2, FALSE)
  • Excel 365 e Excel 2021:
    =XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)

Resultado:

Todos os valores correspondentes serão exibidos se uma correspondência for encontrada, caso contrário o erro #N/A será retornado, veja a captura de tela:

Dicas:
  • Com as fórmulas acima, se houver frutas na Coluna D que não correspondam na Coluna A, elas retornarão um erro. Para facilitar a compreensão desses erros, você pode agrupar sua fórmula com a função IFERROR:
    • Todas as versões do Excel:
      =IFERROR(VLOOKUP(D2,$A$2:$B$10,2,FALSE), "No match found")
    • Excel 365 e Excel 2021:
      =IFERROR(XLOOKUP(D2, $A$2:$A$10, $B$2:$B$10),"No match found")
  • Para aqueles interessados ​​em fórmulas de pesquisa avançadas, Kutools for Excel fornece um conjunto impressionante de fórmulas de pesquisa avançadas que levam a função VLOOKUP tradicional a novos patamares, proporcionando precisão e eficiência incomparáveis ​​em suas tarefas de gerenciamento de dados.

    Kutools for Excel possui uma coleção de mais de 300 ferramentas convenientes projetadas para aumentar sua produtividade. Experimente todo o poder com uma avaliação gratuita de 30 dias e eleve suas planilhas hoje mesmo! Get it Now!

Artigos relacionados:

  • Encontre e destaque linhas duplicadas em um intervalo
  • Às vezes, pode haver alguns registros duplicados no intervalo de dados de uma planilha e agora você deseja localizar ou destacar as linhas duplicadas no intervalo, conforme as capturas de tela a seguir mostradas. Claro que você pode encontrá-los um após um, verificando as linhas. Mas essa não é uma boa escolha se houver centenas de linhas. Aqui, vou falar sobre algumas maneiras úteis para você lidar com essa tarefa.
  • Destaque valores duplicados em cores diferentes
  • No Excel, podemos destacar facilmente os valores duplicados em uma coluna com uma cor usando a Formatação Condicional, mas, às vezes, precisamos destacar os valores duplicados em cores diferentes para reconhecer as duplicatas de forma rápida e fácil, conforme a captura de tela a seguir. Como você resolveria essa tarefa no Excel?
  • Encontre, destaque, filtre, conte, exclua duplicatas no Excel
  • No Excel, dados duplicados ocorrem repetidamente quando registramos dados manualmente, copiamos dados de outras fontes ou por outros motivos. Às vezes, as duplicatas são necessárias e úteis. No entanto, às vezes, os valores duplicados levam a erros ou mal-entendidos. Aqui, este artigo apresentará métodos para identificar, destacar, filtrar, contar, excluir duplicatas por fórmulas, regras de formatação condicional, suplementos de terceiros etc. no Excel.
  • Remova duplicatas e substitua por células em branco
  • Normalmente, quando você aplica o comando Remover Duplicatas no Excel, ele remove todas as linhas duplicadas. Mas, às vezes, você deseja que as células em branco substituam os valores duplicados; nessa situação, o comando Remover Duplicado não funcionará. Este artigo irá guiá-lo para remover duplicatas e substituí-las por células em branco no Excel.
Comments (48)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I'm trying to count duplicates from two columns (Column A is the name Column B is the address) how could I do it?
This comment was minimized by the moderator on the site
Hi, Committed, if you want to count total number of duplicates in two columns, firstly, use a formula to count the duplicates in first column:=COUNTIF(A2:A7, A2), A2:A7 is the range of the first column, A2 is the first data except header of the first column. Then use the same formula (change reference) to count the duplicates in second column. Finaly, use SUM function to get the total number of duplicates in two columns.Here is a tutorial which list almost all scenarios about comparing columns, if you are interested in this, you can visit:https://www.extendoffice.com/documents/excel/6392-excel-compare-two-columns.html</div>;
This comment was minimized by the moderator on the site
BUT I WANT DUPLICATE VALUE BEFORE ANY SPACING, IN COLUMN B WE FOUND DUPLICATE VALUES THAT'S GREAT BUT THERE IS SPACE I WANT THOSE VALUES WITHOUT SPACES, HOW CAN I?
This comment was minimized by the moderator on the site
Hi, just remove the spaces by using the Go to special function to find the space cell, then remove them by clicking Delete key after finding the duplicate values.
This comment was minimized by the moderator on the site
Здравствуйте. Макрос приведенный здесь выделяет дубликаты ячеек, но при попытке редактирования какой-либо ячейки выделение снимается сразу со всех дубликатов, как сделать что бы этого не происходило?
This comment was minimized by the moderator on the site
so lovely yeah!
This comment was minimized by the moderator on the site
I LOVE THIS SITE!
This comment was minimized by the moderator on the site
Thank You!
This comment was minimized by the moderator on the site
This shit don't work... just getting

outRng.Select object variable or with block variable not set??
This comment was minimized by the moderator on the site
great demo guys,keep rocking
This comment was minimized by the moderator on the site
Thank You Very Much Guys
This comment was minimized by the moderator on the site
that was really great.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations