Como comparar duas listas no Excel e achar as diferenças

Comparar duas listas é uma tarefa que aparece em todo escritório: a lista de funcionários do RH contra a do sistema, os pagamentos contra os comprovantes, o cadastro de clientes de dois sistemas. Fazer isso olhando linha por linha é lento e cheio de erros, principalmente quando as listas têm milhares de registros.

O Excel resolve em minutos, se você usar uma chave de comparação e as funções certas. Veja o método e os cuidados que evitam falsas diferenças.

Passo 1: defina a chave

A chave é a coluna que identifica o registro: CPF, matrícula, número de nota, código do produto. Duas linhas «são o mesmo registro» quando têm a mesma chave. Se não houver uma chave única, junte colunas: =A2&"|"&B2.

Coloque cada lista numa aba (Lista A e Lista B), com a chave na coluna A e os valores a comparar nas colunas seguintes.

Passo 2: limpe a chave

A maior fonte de diferenças falsas é a sujeira nos dados: espaços extras no começo ou no fim, diferença entre maiúsculas e minúsculas, acentos. «Silva » e «SILVA» são iguais para você e diferentes para o Excel (nas fórmulas de busca, a diferença de maiúsculas é ignorada, mas os espaços não).

Crie uma coluna de chave limpa:

=MAIÚSCULA(ARRUMAR(A2))

ARRUMAR remove espaços extras e MAIÚSCULA padroniza as letras. Para tirar acentos, use funções SUBSTITUIR encadeadas ou faça a limpeza antes de colar os dados.

Passo 3: quem existe na outra lista

Na Lista A, uma coluna de verificação:

=SE(CONT.SE('Lista B'!$A:$A;A2)>0;"Existe na B";"Só na A")

Repita na Lista B, olhando a A. Com isso, cada linha ganha o status «Existe na outra lista» ou «Só aqui». O CONT.SE é mais leve que PROCV para essa pergunta.

Passo 4: onde os valores diferem

Para as chaves que existem nas duas listas, compare o valor:

=SE(CONT.SE('Lista B'!$A:$A;A2)=0;"Só na A";SE(PROCV(A2;'Lista B'!$A:$C;2;FALSO)=B2;"Igual";"Valor diferente"))

Exemplo: pagamentos de fornecedores.

O caso da NF-1002 é típico: dois algarismos trocados na digitação, que só a comparação por chave revela.

Passo 5: encontre duplicatas

Chaves repetidas distorcem a conferência, porque o PROCV só vê a primeira ocorrência. Marque-as:

=SE(CONT.SE($A$2:$A$5000;A2)>1;"Duplicada";"")

Resolva as duplicatas antes de tirar conclusões.

Passo 6: resuma

Conte quantos registros estão em cada situação: =CONT.SE(D:D;"Só na A"). Um resumo com total, coincidências, só na A, só na B, divergências e duplicatas é o que você leva para a reunião ou anexa ao relatório.

Se preferir não usar fórmulas, a formatação condicional também ajuda: Página Inicial > Formatação Condicional > Realçar Regras das Células > Valores Duplicados, selecionando as duas listas juntas.

Erros comuns

• Comparar números armazenados como texto. «00123» e 123 não são iguais. Padronize o formato antes.
• Ignorar espaços invisíveis. Colados de sistemas, eles são comuns. Use ARRUMAR.
• Usar uma chave que não é única. Um nome de pessoa pode se repetir; prefira um documento ou código.
• Esperar que o Excel entenda nomes parecidos. «Silva J. P.» e «Silva João Pedro» não são iguais. A comparação por chave é exata.

Modelo gratuito: Conferência de Duas Listas

Para não escrever as fórmulas toda vez, há um modelo gratuito. Você cola as listas nas abas «Lista A» e «Lista B», com chave e dois valores adicionais, até 5000 linhas em cada. A chave é limpa automaticamente (espaços extras, maiúsculas e minúsculas, e a letra É tratada como E).

Cada linha recebe um resultado: «Existe na outra lista», «Só aqui» ou «Existe, mas os valores diferem», com marcação de chaves repetidas. A aba «Resumo» traz o total, as coincidências, só na A, só na B, as divergências e as duplicatas, e as abas «Só na A», «Só na B» e «Divergências» listam os registros com o número da linha. Sem macros nem vínculos externos, no Excel 2007 ou superior e no LibreOffice, em português do Brasil. Há uma seção pensada para órgãos públicos e grandes organizações.

Perguntas frequentes

Posso comparar listas de abas ou arquivos diferentes?

Sim. Basta copiar as duas listas para o mesmo arquivo; fórmulas com vínculos entre arquivos quebram com facilidade.

O que fazer quando a chave se repete?

Resolva as duplicatas primeiro ou use uma chave composta. A comparação usa a primeira ocorrência, o que pode esconder diferenças.

A comparação distingue maiúsculas de minúsculas?

As fórmulas de busca do Excel as ignoram. No modelo, a chave é padronizada antes de comparar.

Funciona com PROCX e FILTRO?

Em versões recentes do Excel, sim, e simplificam as fórmulas. O método com PROCV e CONT.SE funciona em todas as versões.