Dados de comparação cruzada entre 2 pastas de trabalho

Question

Estou tentando comparar dois dados em duas pastas de trabalho diferentes. Depois, preciso encontrar os dados ausentes entre os dois e criar uma lista nova e atualizada. No entanto, só preciso atualizar os dados se houver um registro da primeira lista. Caso contrário, não há necessidade de atualizar esses dados.

E.G

Workbook 1
ID            Name      Code
...
futa-123456   Max       0a
futa-123458   Max       0a   
futb-ms8793   John      SX
futg-809153   May       gH
...

Workbook 2
ID            Name      Contact Address Code Flag
...
futa-123456   Max       ...       ...    0a   Y
futa-123457   Max       ...       ...    0a   Y
futb-XY5543   Sam       ...       ...    SX   N
futg-809153   May       ...       ...    gH   Y
futg-809154   May       ...       ...    gH   Y
... 

Final Result
Separate new sheet (doesn't natter which workbook)
ID            Name      Code
...
futa-123456   Max       0a
futa-123457   Max       0a* 
futa-123458   Max       0a   
futb-ms8793   John      SX
futg-809153   May       gH
futg-809154   May       gH*
...

Portanto, o excel mostrará as diferenças em ambos os lados da pasta de trabalho para o Max porque há diferenças entre os dois lados e estão presentes na pasta de trabalho 1. Para maio, somente a pasta de trabalho 2 mostrará as diferenças, pois somente a pasta de trabalho 2 tem as diferenças (mas ainda vai mostrar desde maio está presente na pasta de trabalho 1). Sam não será destacado porque a pasta de trabalho 1 não possui Sam.

Basicamente, estou cruzando a comparação com a pasta de trabalho 1 como a fonte. Se não houver um registro na pasta de trabalho 1, mas estiver presente na pasta de trabalho 2, ele não será destacado.

Existe uma maneira de fazer isso?

    

Answer 1

As etapas necessárias para fazer isso manualmente são bem longas e tediosas.

Supondo que os dados na pasta de trabalho 1 estão em Sheet1 , siga estas etapas:

1. Copie os dados (incluindo cabeçalhos) da pasta de trabalho 2
2. Cole na célula A1 de uma planilha vazia na pasta de trabalho 1 (digamos, Sheet2 )
3. Insira esta fórmula em G1 → =MATCH(B1,Sheet1!B:B,0)
4. Copie e cole ou preencha a fórmula corretamente. Eu prefiro esta seqüência de teclas:
   • À esquerda
   • Ctrl + Abaixo
   • Direito
   • Ctrl + Deslocamento + Acima
   • Ctrl + D
5. Ative a filtragem para a coluna ( Ctrl + Deslocamento + L )
6. Filtre por #N/A apenas. (Se não houver nenhum, ignore este passo e o próximo).
7. Excluir todas as #N/A linhas
8. Excluir colunas C , D , F , G
9. Realce os dados
10. Copie os dados ( excluindo cabeçalhos) de Sheet1 da pasta de trabalho 1 (a pasta de trabalho atual)
11. Clique com o botão direito do mouse na célula A2 de Sheet2 e selecione Insert Copied Cells
12. Verifique se Shift cells down está selecionado, a imprensa OK
13. Selecione todos os dados e remova as duplicatas por meio de Data → Data Tools → Remove Duplicates . (Certifique-se de que somente a coluna ID esteja marcada.)
14. Classificar pela coluna ID

A melhor solução, claro, é automatizar essas etapas. Isso é o que o seguinte código VBA faz.

Copie e cole este código em um módulo padrão na pasta de trabalho 1:

'============================================================================================
' Module     : a standard module in Workbook 1
' Version    : 0.1.1
' Part       : 1 of 1
' References : N/A
' Source     : https://superuser.com/a/1331855/763880
'============================================================================================
Option Explicit

Public Sub CrossCompareSheets()

  Const s_CompareToWorkbook  As String = "Workbook 2.xlsx"
  Const s_CompareToSheet     As String = "Sheet1"
  Const s_CompareToTopLeft   As String = "A1"
  Const s_CompareToExtraCols As String = "C,D,F"
  Const s_SourceSheet        As String = "Sheet1"
  Const s_SourceTopLeft      As String = "A1"
  Const n_SourceMatchColumn  As Long = 2
  Const s_ResultSheet        As String = "Sheet2"
  Const s_ResultTopLeft      As String = "A1"
  Const n_ResultMatchColumn  As Long = 2
  Const n_ResultUniqueColumn As Long = 1
  Const n_ResultSortColumn   As Long = 1

  Dim wkstCompareTo  As Worksheet: Set wkstCompareTo = Workbooks(s_CompareToWorkbook).Worksheets(s_CompareToSheet)
  Dim rngCompareTo As Range: Set rngCompareTo = wkstCompareTo.Range(s_CompareToTopLeft).CurrentRegion
  Dim wkstSource  As Worksheet: Set wkstSource = ActiveWorkbook.Worksheets(s_SourceSheet)
  Dim rngSource As Range: Set rngSource = wkstSource.Range(s_SourceTopLeft).CurrentRegion
  Dim wkstResult  As Worksheet: Set wkstResult = ActiveWorkbook.Worksheets(s_ResultSheet)
  Dim rngResult As Range
  Dim celResultTopLeft As Range: Set celResultTopLeft = wkstResult.Range(s_ResultTopLeft)

  wkstResult.UsedRange.Clear
  rngCompareTo.Copy Destination:=wkstResult.Range(s_ResultTopLeft)
  Set rngResult = celResultTopLeft.CurrentRegion
  With rngResult.Resize(ColumnSize:=1).Offset(ColumnOffset:=rngResult.Columns.Count)
    .FormulaR1C1 = Replace(Replace(Replace( _
      "=MATCH(RC{a},{Sheet}!C{b},0)" _
      , "{a}", n_ResultMatchColumn), "{Sheet}", s_SourceSheet), "{b}", n_SourceMatchColumn)
    .Copy
    .PasteSpecial xlPasteValues
  End With
  Set rngResult = celResultTopLeft.CurrentRegion
  rngResult.AutoFilter Field:=rngResult.Columns.Count, Criteria1:="#N/A"
  rngResult.Offset(RowOffset:=1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
  rngResult.AutoFilter
  Dim colsToBeDeleted As Range
  Set colsToBeDeleted = rngResult.Resize(ColumnSize:=1).Offset(ColumnOffset:=rngResult.Columns.Count - 1).EntireColumn
  Dim varColumn As Variant
  For Each varColumn In Split(s_CompareToExtraCols, ",")
    Set colsToBeDeleted = Union(colsToBeDeleted, wkstResult.Range(varColumn & ":" & varColumn))
  Next varColumn
  colsToBeDeleted.Delete
  Set rngResult = celResultTopLeft.CurrentRegion
  rngSource.Offset(RowOffset:=1).Copy Destination:=celResultTopLeft.Offset(RowOffset:=rngResult.Rows.Count)
  Set rngResult = celResultTopLeft.CurrentRegion
  rngResult.RemoveDuplicates Columns:=n_ResultUniqueColumn, Header:=xlYes
  Set rngResult = celResultTopLeft.CurrentRegion
  With wkstResult.Sort
    .SortFields.Clear
    .SortFields.Add Key:=rngResult.Columns(n_ResultSortColumn)
    .SetRange rngResult
    .Header = xlYes
    .Apply
  End With

End Sub

Notas:

Você pode alterar as constantes na parte superior para atender diferentes colunas e nomes de arquivos, e o código será ajustado automaticamente.

    

Answer 2

Eu gostaria de sugerir o método mais fácil de comparar e criar uma lista única.

Siga estas etapas:

• Copiar dados de ambas as pastas de trabalho em uma folha em branco.
• Selecionar dados inteiros.
• Na guia Página inicial, clique em Classificar & Filtrar.
• Classifique os dados em ordem crescente no ID.
• Mais uma vez, selecione dados inteiros.
• Ir para a guia "Dados" & clique em Remover Duplicar.
• Selecione a coluna "ID" para encontrar & remova duplicados.

Você encontra seus dados como mostrado abaixo:

ID           Name      Code
futa-123456  Max        0a
futa-123457  Max        0a
futa-123458  Max        0a
futb-ms8793  John       SX
futb-xy5543  Sam        Sx
futg-890153  May        gH
futg-890154  May        GH

N.B.

• O método mostrado acima é adequado para bancos de dados pequenos.
• Você pode registrar todo o processo como Macro para trabalhar mais rápido.

Answer 3

EDIT releia a pergunta e atualize meu entendimento sobre o problema ...

Segunda edição onde me misturei entre a planilha 1 e 2

Para encontrar os dados que precisam ir em uma nova planilha porque ela existe apenas na planilha 1 ...

=match(a1, sheet2!a:a,0). 

Filtro para #na.

Para atualizar dados na planilha 1, adicione uma coluna à direita dos dados um pouco como esta;

=Iferror(Index(sheet2!d:d,match(a1, sheet2!a:a,0)),D1)

Copie isso para baixo em uma coluna sobressalente na planilha 1, copie e cole como valores da coluna D1 original.

Outro piont; se os IDs não forem consistentes entre as folhas (não podem ser usados para correspondência), crie um nome de concatenação 'coluna de ajuda' & código, ou o que quer que possa ser usado para comparar as duas folhas corretamente.

(Se os IDs não puderem ser usados para correspondência, eu também começaria a fazer muitas perguntas sobre os dados, mas isso está fora do escopo da pergunta)