Como Encontrar Dados Repetidos em 3 Colunas com Formatação Condicional

A Formatação Condicional é algo simples e mágico!

Neste post (clique aqui para acessar) explico como funciona a Formatação Condicional para uma linha e neste outro post  (clique aqui para acessar) mostro como fazer a Formatação Condicional para várias linhas. Porém, todas as Formatações Condicionais que já expliquei, têm como base a referência para uma variável somente.

 

Mas chegou até mim um questionamento: como formatar condicionalmente as linhas em que dados de 3 colunas diferentes se repetem?

Para ter a planilha pronta, baixe o arquivo inserindo seu e-mail na caixa abaixo e clicando em “Baixar Agora”.

Para ilustrar melhor, temos a tela abaixo e a intenção é que se os dados nas colunas “Valor”, “Situação” e “Tipo” juntas, se repetirem em outra linha, sem importar a data, ela precisa ficar destacada. Na imagem, vemos que as linhas 12 e 16 possuem essa característica. É preciso mostrar no relatório que essa característica está presente e lembrando que é provável que outras linhas estejam com dados repetidos.

Para chegar ao resultado, vou inserir duas colunas e vamos trabalhar com elas: Concatenação e Conte.

Os nomes das novas colunas não importa, na realidade, pois ocultarei as duas no final. Escolhi os nomes pelas funções que iremos utilizar.

IMPORTANTE: Faço esse tutorial sem formatar o intervalo como tabela para que todos possam ver e entender as fórmulas utilizadas.

Concatenação

Vou utilizar a função Concatenar e unir os dados 3 colunas que possuem os dados relevantes:

=CONCATENAR(B10;C10;D10)

Após confirmar e expandir a função para as outras células, a tabela ficará mais ou menos assim:

Agora temos uma coluna com resultados únicos, ou seja, os dados de 3 colunas foram concatenados em 1 e dessa forma podemos verificar os repetidos.

Digite seu e-mail abaixo, clique em Enviar e seja informado de todas as novidades!

Contar

Para isso, vamos utilizar a função CONT.SE e verificar se o valor que está na mesma linha da função é repetido e quantas vezes isso acontece. Fica assim:

=CONT.SE($E$10:$E$34;E10)

Onde E10:E34 é o intervalo de verificação e E10 é o critério. Trocando em miúdos é contar quantas vezes o que está em E10 se repete no intervalo E10 até e34. Note que E10:E34 está como absoluto.

Após expandir a função para todas as células, temos o resultado abaixo:

Note que algumas se repetem até 3 vezes.

E agora chegou a hora de formatar as linhas.

Formatar Condicionalmente

Selecione todo o intervalo da tabela, exceto o título. Vá até Formatação Condicional > Nova regra e escolha “Usar uma fórmula para determinar quais células devem ser formatadas”.

ATENÇÃO: Não selecione o título, ou vai dar errado.

No campo “Formatar valores em que essa fórmula é verdadeira” coloque a seguinte fórmula:

=$F10:$F34>1

Para fazer isso você pode selecionar as linhas da coluna “Conte”. Lembre sempre de retirar o símbolo de cifrão da linha e só deixar na coluna. Se não retirar o $ da linha, a formatação não vai funcionar.

Agora clique em “Formatar” e formate da forma que for melhor. Eu vou deixar a linha laranja quando tiver números acima de 1, ou seja, quando os dados estiverem presentes mais de uma vez.

E o que a fórmula vai fazer? Vai informar à formatação condicional que é para aplicar a formatação escolhida quando existir algum número maior que 1.

Agora, para que a tabela fique amigável, vou ocultar as colunas desnecessárias, que são as E e F.

Pronto. Agora sempre que o Valor, Situação e Tipo se repetirem, a linha dos locais onde elas estão ficará laranja.

Até a próxima.