Como encontrar valores duplicados com base na coluna em duas planilhas e verificar se a linha é uma correspondência exata

Question

Tenho duas planilhas Excel: Header2018 e Header2019

ambos contêm os mesmos cabeçalhos de coluna, mas com entradas de registro diferentes. Algumas dessas entradas são iguais e quero descobrir quais.

Quero mesclar todos os registros duplicados (com base na coluna: asset_id) em uma nova planilha e verificar se há alguma diferença entre o restante das colunas.

Exemplo de cabeçalho 2018:

asset_id    Name    Country

2          Trent      CAN
3          Alex       CAN
4          Derek      CAN
5          Louis      CAN
6          Teresa     CAN

Cabeçalho 2019:

asset_id    Name    Country
4          Derek      CAN
5          Louis      USA
1          Kate       CAN
7          Pat        CAN
8          Steven     CAN
9          Tom        CAN

Tabela resultante:

asset_id    Name    Country
4          Derek      CAN
5          Louis      CAN
4          Derek      CAN
5          Louis      USA

Com base na tabela resultante, quero encontrar registros em que asset_id seja o mesmo, mas outra coluna seja diferente.

Desde já, obrigado!

Answer 1

Você pode usar o Power Query para obter o resultado.

1. adicione 2 tabelas ao Power Query-no editor Power Query- vá para Home- Anexar 2 consultas:

2. Selecione a coluna asset_id - vá para Transformar - Selecione Agrupar por - selecione Avançado - Adicionar agregação - Digite um nome para Nova Coluna e selecione Todas as Linhas:

3. Expanda Tabela para e selecione Nome e País - Coluna Contagem de Filtro >2, remova a coluna Contagem - Fechar e carregar:

Answer 2

Gostaria de sugerir um método, precisa de colunas auxiliares e uma fórmula de matriz para extrair linhas comuns de ambas as planilhas:

Folha 1:

• Fórmula na célula A2paraValor Auxiliar:

=COUNTIF(Sheet2!$B$2:$B$7, B2)

---

Folha 2:

• Fórmula na célula A2:

=COUNTIF(Sheet1!$B$2:$B$6, B2)

Observação

• Se ambas as planilhas tiverem vários IDs duplicados (são mais de 2) então, em vez deCONT.SE, é melhor usar esta fórmula para encontrarDuplicatas.

  =IF(ISERROR(MATCH(B2,Sheet2!B$2:B$7,0)),"","Duplicate")

• E emFolha mestracritérios na célula E59seriamDuplicadoem vez de1.

---

Folha mestra:

• Uma fórmula de matriz na célula G60:

  {=IFERROR(INDEX(Sheet1!$B$2:$D$6, SMALL(IF(COUNTIF($E$59, Sheet1!$A$2:$A$6), ROW(Sheet1!$B$2:$D$6)-MIN(ROW(Sheet1!$B$2:$D$6))+1), ROW(A1)), COLUMN(A1)),IFERROR(INDEX(Sheet2!$B$2:$D$7, SMALL(IF(COUNTIF($E$59, Sheet2!$A$2:$A$7), ROW(Sheet2!$B$2:$D$7)-MIN(ROW(Sheet2!$B$2:$D$7))+1), ABS(ROW(A1)-2)), COLUMN(A1)),""))}
  

• Finalize a fórmula comCtrl+Shift+Enter, preencha para a direita e para baixo.

• Na fórmula Array ABS(ROW(A1)-2))é usada para ajustarNúmero da linhaPara .Sheet 2​1

Você pode ajustar as referências de células na Fórmula conforme necessário.