Identificar Valores Duplicados Com Base Em Duas Ou Mais Colunas Excel

Uma grande dificuldade enfrentada ao gerenciar planilhas é como criar uma regra para formatar, por exemplo, uma linha com base em dois ou mais valores presentes em colunas diferentes.

Considere uma planilha com as colunas A e B com valores como abaixo:


Agora, pense na possibilidade de você querer que, ao digitar, por exemplo, “4500908563” na coluna A e “00060” na coluna B, a linha 6 e 8 fossem preenchidas com uma formatação diferente, visando mostrar que a linha em questão é igual aos valores que você acabou de digitar, ou seja, que um “produto” com número de série “4500908563” e padrão “00060” já consta na planilha. Como fazer isso?

Primeiro, na coluna C, crie uma regra ou use alguma fórmula que concatene os valores da coluna A e B. Na planilha da imagem abaixo, foi usada a fórmula “=A1 & B1” (sem aspas) na célula C1:



Depois de inserida a fórmula, o resultado será um número que é a combinação das células A e B. Repita o procedimento para as células seguintes da coluna C até a linha 7, alterando, claro, o número das linhas.

Em seguida, na célula D1, digite a seguinte fórmula “=CONT.SE($C$1:$C$8;C1)”. Essa fórmula vai procurar na coluna C algum valor igual a C1, tanto quantos ele encontrar, incrementará. Copiando a fórmula para as outras linhas da coluna D, temos algo igual ao mostrado na imagem:



Próximo passo é usar formatação condicional para identificar quando os valores repetidos forem digitados.

Selecione o intervalo de A1 até D8, vá na guia Início, na parte onde estão os estilos, clique em formatação condicional, como mostra a figura,



depois clique em "Nova Regra". Na janela que vai aparecer clique em “Usar uma fórmula para determinar quais células devem ser formatadas” e digite no espaço onde o título é “Formatar valores em que esta fórmula é verdadeira”: "=$D1>1" (sem as aspas). Veja como deve ficar na imagem abaixo:



No exemplo acima, foi colocado preenchimento de cor vermelha. Para fazer isso, basta ir em "Formatar", na mesma janela acima presente e depois ir na aba "Preenchimento".

Após inserida essa regra de formatação, para testar se está funcionando corretamente, digite na célula A8 o valor 4500908563 que está presente em todas as células de A1 até A7. Como esperado, não acontecerá nada, afinal, o objetivo é apenas formatar a linha quando os valores da coluna A e da coluna B, unidos, repetem-se em duas ou mais linhas. Agora, na célula B8, digite 00100. Após pressionar Enter, ou sair da edição da célula de alguma forma, o resultado será como o da imagem abaixo:



Se tudo ocorreu bem, a linha 3 e a 8 foram formatadas de vermelho, mostrando que os valores que você digitou na linha 8 já existem em outra linha. Fácil não?

Para deixar a planilha ainda mais personalizada, devemos ocultar as colunas C e D, afinal, elas são o grande “macete” dessa formatação. Para ocultar as colunas, segure SHIFT e clique nas duas colunas, elas serão selecionadas. Em seguida, clique com o botão direito para aparecer o menu auxiliar, feito isso, clique em “ocultar”. Feito! As colunas foram ocultas e o resultado final é o da figura abaixo:


Nome do Autor

Sobre o autor

Flávio Santos é o editor-chefe do Tecla Ti. Vice campeão de todas as Américas em Tecnologia da Informação pela Worldskills Americas. Especialista Microsoft Office, programador VBA, C, JAVA, PHP e desenvolvedor de aplicações. Leia Mais sobre o autor >>>

12 comentários:

Eae grande Flávio.
Então, gostaria que postasse um tutorial ai sobre a importação de dados para o access daquela questão que estavamos conversando, lembra?

Se puder coloca ai.

Abraço!

Dúvida respondida e resolvida pessoalmente, certo kayo?

Att., Flávio S.

Fala Flávio!!

Cara trabalho aqui na contabilidade da prefeitura, preciso achar no caso 2 valores iguais mas em colunas diferentes, no excel tem alguma fórmula que eu possa usar??

Flavio,

Tenho um problema. Precisamos fazer uma comparação entre duas colunas, são 4 ao todo. Nomes e CPFs. Problema é que um dos CPFs chega sem pontos e traço.

Grato.

Bira

Caro Bira,

Na página de colaboradores, que fica no topo do site está disponível meu email. Basta você entrar em contato comigo e tentarei ajudar você da melhor forma possível. Abs!

Flavio,

Por que nao usar a formatação condicional de identificação de duplicados, diretamente na coluna dos valores concatenados. Parabéns pela postagem!

Grande Flavio, me diz ai Falvio, sabe se tem a possibilidade de compara entradas duplicadas entre duas ou mais planilhas?

Cara! Genial!
Me ajudou muito. Obrigada!

Cara deixei a Formula condicional da seguinte maneira "=E($H3>1;$H3<1000000)" pois na minha planilha ficava aparecendo resultados com valores acima de 1000000 que também ficavam marcados em vermelho! Mas dei um jeitinho e ficou perfeito! Agora só marca resultados duplicados com resultados de 1 a 1000000. Acho que os resultados com valores acima de 1000000 eram células em branco seria isso?

Boa noite, estou tentando vários caminhos, mas ate agora nada. Por gentileza, será que vc pode me ajudar... tenho uma planiha enorme e 1 coluna com mais de 14 mil nomes, e preciso verificar quais são duplicados, não quero excluir, apenas idenifica-los. também alimento esta planilha diariamente, faço a consulta de nome por nome usando crt + L. Tem alguma maneira de automatizar este processo.

use a opção, Formatar Células/Especial/CIC e ela irá padronizar a célula para o formato do CPF.

Postar um comentário