Eu tenho duas listas em uma planilha. Ambas as listas consistem em duas colunas. Uma coluna contém os números de série do computador e a outra coluna é a pessoa a quem o computador da primeira coluna está atribuído.
As listas foram criadas por duas pessoas diferentes e preciso compará-las e encontrar discrepâncias. Aqui está um exemplo deste problema: 
.
Neste exemplo, eu precisaria ter certeza de que a CPU1 foi atribuída à Pessoa A no conjunto de dados A e no conjunto de dados B. Se houver uma discrepância, o conjunto de dados B deverá ter precedência. No entanto, se houver uma pessoa presente no conjunto de dados A que não esteja presente no conjunto de dados B, essa pessoa deverá permanecer na lista.
Responder1
Portanto, você precisa verificar a Lista A em relação à Lista B, alterando para corresponder à Lista B quando necessário e usando os dados da Lista A quando não. E provavelmente você gostaria de saber sobre os problemas que ocorrem.
Em relação à primeira parte, você precisa de uma nova coluna na Lista A, melhor inserida entre as duas colunas existentes para que imediatamente se torne parte de qualquer intervalo ou outra referência em qualquer lugar da planilha, então a coluna empurrada para a direita é cortada e inserida antes dela em vez de ficar atrás dele, você volta ao material original com uma nova coluna à direita, mas a nova coluna faz parte de qualquer referência ao material da Lista A.
Para o segundo, há pelo menos dois eventos que se suspeita que seriam de seu interesse: 1) A Lista A tem dados que não correspondem à Lista B. O resultado na coluna é a Lista B "correta", mas há uma incompatibilidade e 2) Depois de fazer a verificação, o resultado mostrado é uma duplicata de outros resultados mostrados. Em outras palavras, você mostra pelo menos duas pessoas atribuídas a uma CPU específica e, a menos que a Lista B contenha duplicatas, isso ocorre porque a Lista B indica uma pessoa e a Lista A mostra uma ou mais outras como tendo essa CPU. Somente o resultado da Lista B Pessoa pode estar correto, então os outros representam problemas.
Para fazer o trabalho de verificação, insira a nova coluna e mova a coluna que foi colocada de volta no lugar. Direi que seus dados são atualmente a Lista A são as colunas A e B, a coluna preta é a coluna C e a Lista B são as colunas D e E. No final, você terá uma nova coluna C empurrando todas as outras uma coluna para o certo, então a Lista B agora é composta pelas colunas E e F. A linha do cabeçalho é a linha 1 da planilha e vou fingir que os dados são as linhas 2:11.
Como a Lista B substitui qualquer diferença com a Lista A, a fórmula pesquisará cada pessoa na Lista A na Lista B e retornará o que a Lista B tiver, se houver uma entrada para essa pessoa, e retornará o que a Lista A tiver se não houver nenhuma entrada na Lista B. :
XLOOKUP($B2,$F$2:$F$5,$E$2:$E$5,$A2,0,1)
Usei o bonito, novo, XLOOKUP()mas se não tiver, você precisará de uma Index/Matchsolução, pois terá que "olhar para a esquerda" na Lista B.
Depois os problemas. Você tem pelo menos duas opções sobre como lidar com eles. Provavelmente outros, como você, poderiam escrever uma macro para fazer isso, mas pensarei apenas em fórmulas. Uma maneira é complicar isso XLOOKUP()com testes para o problema. A maioria das pessoas parece crescer alto e seguir nessa direção. Mas existe uma maneira muito mais fácil, que permite muitos ajustes, se desejar, embora você só precise do básico aqui. É usar a Formatação Condicional ("CF") para fazer o teste, em vez de transformar a fórmula acima em um monstro de 20 linhas que você realmente não entende, mesmo quando acabou de terminar.
Com CF você só precisa escolher a ordem em que lista as regras para que sejam testadas em uma ordem que funcione corretamente. (A maioria das pessoas calcula os testes primeiro. Se você escolher a ordem primeiro, terá que elaborar testes que se encaixem nessa ordem. Geralmente é muito mais fácil seguir o outro caminho, é o que as pessoas fazem.)
Portanto, se houver mais de uma pessoa listando uma CPU específica agora, isso é chamado de "duplicados" e o CF tem uma regra interna para isso. Fácil de testar primeiro, então configure isso. Já vou falar do formato a ser aplicado, porque tem um "truque" nisso. Em seguida, faça uma segunda regra que use esta fórmula para seu teste:
=XLOOKUP($B2,$F$2:$F$5,$E$2:$E$5,$A2,0,1)<>$A2
Observe que esta nova regra se insere antes da regra de duplicatas. Tudo bem, isso não funcionará se você passar para a segunda regra verificada. MARQUE a caixa "Parar se for verdade".
Agora o CF verificará se o resultado da fórmula corresponde aos dados originais da Lista A para a Pessoa. Se isso acontecer, ele aplicará o formato aqui e parará. Caso contrário, passa para a segunda regra.
A segunda regra verifica se há duplicação e aplica o formato para isso, se houver.
Então, o "truque"... você usará uma possibilidade de formatação pouco frequente para colocar as mensagens de erro nos novos resultados da Lista A (em vez de ter o pesadelo de encaixá-las na fórmula da célula, o pesadelo do trabalho e o pesadelo de sempre tentando entender tudo novamente para atualizá-lo ou corrigi-lo).
A formatação numérica normal permite definir a formatação para até quatro TIPOS de dados em uma célula: números positivos, números negativos, zeros e texto. Em qualquer um deles, você pode colocar strings de texto (plural) no formato. Na verdade, um formato não pode ser nada além de uma string de texto. Para ambas as regras de CF, você usará este fato. Para a primeira regra, use o seguinte, exatamente como você vê:
;;;"Lista A incompatível"
e para a segunda regra, use o seguinte:
;;;">1 atribuído"
Para fazer com que eles se destaquem melhor (são textos mais longos que o texto "CPU", mas...), você pode formatar o texto com uma cor diferente, pelo menos, talvez um belo vermelho.
E se você só precisa da lista sem problemas anotados, basta fazer a fórmula no início e lembrar desse material de CF para outro dia, quando quiser uma fórmula simples e direta em suas células e todos os testes de erros e envio de mensagens apropriadas em regras simples em CF, em vez de tornar as fórmulas celulares um pesadelo.