Células de formatação condicional com valores diferentes para linhas com o mesmo valor na coluna específica

Question

Eu preciso formatar condicionalmente células com valores diferentes entre as mesmas colunas, se o valor delas para uma coluna específica for o mesmo.

Cenário

Minha planilha é um banco de dados de contatos, onde cada linha é um registro de contato e cada coluna contém um tipo de dado (sobrenome, rua 1, rua 2, cidade, estado, etc.) para cada registro de contato. >

A planilha é uma junção das informações de contato mantidas por diferentes escritórios em nossa empresa. Combinamos todas as informações de contato na mesma fonte para que possamos concordar com quaisquer discrepâncias e manter todos os nossos contatos no mesmo lugar. daqui para frente.

Alguns contatos têm apenas um registro - por exemplo, quando apenas um de nossos escritórios tinha dados para esse contato, por isso não pode discordar dos dados que temos de qualquer outro escritório; outros contatos têm vários registros (ou seja, pode haver apenas uma linha para Fred Johnson, mas quatro linhas para Jane Smith). Cada contato precisa ter as mesmas informações de contato exatas antes de podermos enviá-las para um banco de dados na nuvem ou criar duplicatas.

Passamos muito tempo tentando combinar dados entre os registros da mesma pessoa e agora precisamos descobrir onde as discrepâncias restantes existem.

Eu concatenei os campos LastName e FirstName para que as linhas com o mesmo valor nesse campo pudessem ser comparadas uma com a outra. O objetivo é destacar as células com dados diferentes na mesma coluna quando comparadas com outros registros com os mesmos. mesmo valor na coluna LastFirst (se houver).

Como posso fazer isso?

Até agora, estou ciente de que a função Find Special pode destacar células quando comparada a uma linha ou coluna designada, mas estou precisando que as comparações sejam feitas em relação a linhas que tenham o mesmo valor para uma determinada coluna.

Eu tenho acesso ao Excel 2010 e 2013 para executar essa função.

Obrigado!

    

Answer 1

Pode haver soluções mais simples, mas você não deve precisar concatenar os nomes e sobrenomes para criar uma coluna "FullName".

Pré-visualização da nossa saída

(as células brancas à direita mostram qual saída nossa fórmula produzirá para direcionar a formatação condicional):

Aquiestánossafórmula

=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)-COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))

Lembre-se de ajustar para se adequar ao número de linhas que você tem em seu conjunto de dados e à posição de suas colunas de primeiro e último nome, se elas não estiverem em A & B.

Eu também assumi que seus primeiros dados sem nome iniciam na célula C2 - ou seja, este é o bit mais à esquerda no topo dos dados que você deseja verificar se há incompatibilidades. Edite os "C" nesta fórmula, se precisar.

Aplicando a formatação condicional

Copie a fórmula acima (depois de fazer as alterações necessárias) e, em seguida, clique na célula mais à esquerda que deseja verificar se há incompatibilidades (mencionarei "C2" aqui). Agora, selecione o botão Conditional Formatting da faixa de opções e escolha New Rule .

SelecioneUseaformulatodeterminewhichcellstoformate,emseguida,colesuafórmulanacaixadetexto(certifique-sedequenãoháespaços-sevocêtivercopiadodecima,precisaráretiraralgunsdeles).AgoravocêpodeclicaremFormatparadefinircomodesejadestacarasincompatibilidades.Eufuiparaumpreenchimentovermelho(berrante!).CliqueemOKatévoltaràplanilha.

Se você não tem nenhuma incompatibilidade na célula C2, pode parecer que nada aconteceu, mas isso é porque ainda precisamos aplicar a regra a todo o seu conjunto de dados. Mantendo o C2 ainda selecionado, clique em Manage Rules no menu da faixa Conditional Formatting .

Agoravocêpodeselecionarointervalointeiroquevocêdesejaverificarseháincompatibilidades.NacaixaAppliesto,cliqueearrasteparaselecionartudooquevocêdesejacomparar(ousevocêtemmuitaslinhas,paravelocidadebastadigitarareferênciadecélula=$C$2:$Z$999)

Clique em OK e pronto!

Como funciona

Esta fórmula usa COUNTIFS() para contar quantas linhas existem para essa pessoa:

=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)

Em seguida, calculamos quantas linhas têm o nome dessa pessoa E o valor na coluna que você está verificando. Se todas as linhas forem idênticas, esse número deverá corresponder exatamente ao primeiro COUNTIFS() .

COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))

Se subtrairmos o último do primeiro, e todas as linhas corresponderem, a fórmula gerará 0 e não fará nenhuma formatação condicional. No entanto, se alguma coisa for diferente, a saída será 1 ou superior, acionando a formatação condicional.

Nota

Eu tive que enrolar o critério COUNTIFS() final em uma declaração IF() para lidar com espaços em branco - COUNTIFS não gosta muito de espaços em branco (parece não ter certeza se deve contá-los como 0 ou "". Estranho).

Download de arquivo

Este documento de exemplo também está disponível para download .