Datenvergleich zwischen zwei Arbeitsmappen

461
Wilson Liao

Ich versuche, zwei Daten in zwei verschiedenen Arbeitsmappen miteinander zu vergleichen. Dann muss ich die fehlenden Daten zwischen den beiden finden und eine separate neue, aktualisierte Liste erstellen. Ich muss die Daten jedoch nur aktualisieren, wenn ein Datensatz aus der ersten Liste vorhanden ist. Wenn nicht, müssen diese Daten nicht aktualisiert werden.

Z.B

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* ...

Excel zeigt also die Unterschiede auf beiden Seiten der Arbeitsmappe für Max, da in beiden Seiten Unterschiede bestehen und diese in der Arbeitsmappe 1 vorhanden sind. Für Mai zeigt nur die Arbeitsmappe 2 die Unterschiede, da nur die Arbeitsmappe 2 die Unterschiede aufweist (dies jedoch der Fall ist) noch zeigen, seit Mai ist in Arbeitsmappe vorhanden 1). Sam wird nicht hervorgehoben, da in Arbeitsmappe 1 kein Sam vorhanden ist.

Im Grunde verglich ich mich mit Workbook 1 als Quelle. Wenn in Arbeitsmappe 1 kein Datensatz vorhanden ist, aber in Arbeitsmappe 2 vorhanden ist, wird er nicht hervorgehoben.

Gibt es eine Möglichkeit, dies zu tun?

1
Bearbeiten Sie das Endergebnis zum besseren Verständnis. Rajesh S vor 5 Jahren 0
Ich konnte die Änderungen nicht mutig erscheinen lassen, also fügte ich am Ende ein * hinzu. Lass es mich wissen, wenn es noch unklar ist. Wilson Liao vor 5 Jahren 0

3 Antworten auf die Frage

0
robinCTS

Die dazu erforderlichen Schritte sind sehr langwierig und langwierig.

Angenommen, die Daten in Arbeitsmappe 1 befinden sich in den Sheet1folgenden Schritten:

  1. Kopieren Sie die Daten (einschließlich Kopfzeilen) aus der Arbeitsmappe 2
  2. Einfügen in Zelle A1eines leeren Arbeitsblatts in Arbeitsmappe 1 (say Sheet2)
  3. Geben Sie diese Formel in G1→ ein=MATCH(B1,Sheet1!B:B,0)
  4. Kopieren oder einfügen oder füllen Sie die Formel entsprechend aus. Ich bevorzuge diese Tastenfolge:
    • Left
    • Ctrl+Down
    • Right
    • Ctrl+ Shift+Up
    • Ctrl+D
  5. Filterung für Spalte aktivieren ( Ctrl+ Shift+ L)
  6. Filter für #N/Anur (Wenn es keine gibt, ignorieren Sie diesen und den nächsten Schritt.)
  7. Löschen Sie alle #N/AZeilen
  8. Löschen Spalten C, D, F,G
  9. Markieren Sie die Daten
  10. Kopieren Sie die Daten ( außer Kopfzeilen) aus Sheet1Arbeitsmappe 1 (die aktuelle Arbeitsmappe).
  11. Klicken Sie mit der rechten Maustaste in die Zelle A2von Sheet2und wählen Sie ausInsert Copied Cells
  12. Stellen Sie sicher Shift cells down, dass die Presse ausgewählt istOK
  13. Wählen Sie alle Daten aus und entfernen Sie die Duplikate über Data→ Data Tools→ Remove Duplicates. (Stellen Sie sicher, dass nur die Spalte IDmarkiert ist.)
  14. Nach Spalte sortieren ID


Die beste Lösung ist natürlich, diese Schritte zu automatisieren. Dies ist, was der folgende VBA-Code tut.

Kopieren Sie diesen Code in ein Standardmodul in Arbeitsmappe 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,!C,0)" _, "", n_ResultMatchColumn), "", s_SourceSheet), "", 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

Anmerkungen:

Sie können die Konstanten oben ändern, um unterschiedliche Spalten und Dateinamen zu berücksichtigen. Der Code wird automatisch angepasst.

Ich weiß das zu schätzen, aber VBA ist in meinem Fall nicht machbar. Die Daten, Spalten und Dateinamen, die ich vergleiche, ändern sich ständig. Es kann lang und langweilig sein, aber es wird immer noch unendlich schneller als der manuelle Vergleich. Hoffe du kannst mir helfen. Wilson Liao vor 5 Jahren 0
@ WilsonLiao Okie, Dokie. Ich werde meine Antwort aktualisieren. Nur damit Sie wissen, ich * habe * den VBA so eingerichtet, dass Sie die Spalten und Dateinamen oben leicht ändern können und der Code dann mit den neuen Werten funktioniert. Auf diese Weise wird es noch schneller. robinCTS vor 5 Jahren 0
Ah ich sehe. Ich habe 0 Wissen über VBA, also brauche ich Zeit, um damit zu experimentieren. Ich würde immer noch die Nicht-VBA-Methode zu schätzen wissen, da ich sie kurzfristig noch anwenden muss. Vielen Dank! Wilson Liao vor 5 Jahren 0
@ WilsonLiao OK. Habe die manuellen Schritte alles geklärt (hoffe ich ;-)) und hochgeladen. robinCTS vor 5 Jahren 0
Wenn ich möchte, dass die neuen aktualisierten Informationen in der neuen Tabelle hervorgehoben werden, wie mache ich das? Wilson Liao vor 5 Jahren 0
@ WilsonLiao Dang. Ich wollte das herausfinden und hinzufügen. Ich habe es jetzt erledigt. Noch mehr Schritte! Ich werde die Antwort jetzt mit den manuellen Schritten aktualisieren, aber das Code-Update muss bis morgen warten, EDIT: Fertig. In den manuellen Schritten werden jetzt die neuen aktualisierten Informationen hervorgehoben. robinCTS vor 5 Jahren 0
-1
LJ01

EDIT-Frage erneut lesen und mit meinem Verständnis des Problems aktualisiert ...

Zweite Bearbeitung, bei der ich zwischen Blatt 1 und 2 gemischt wurde

So finden Sie die Daten, die in einem neuen Arbeitsblatt gespeichert werden müssen, da sie nur in Arbeitsblatt 1 vorhanden sind ...

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

Nach #na filtern

Um Daten in Blatt 1 zu aktualisieren, fügen Sie eine Spalte rechts von den Daten ein.

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

Kopieren Sie dies in eine Ersatzspalte in Blatt 1, kopieren Sie sie und fügen Sie sie als Werte der ursprünglichen D1-Spalte ein.

Ein anderer piont; Wenn die IDs zwischen den Tabellen nicht konsistent sind (kann nicht zum Abgleich verwendet werden), erstellen Sie eine 'Helper-Spalte', die den Namen und Code verkettet, oder was auch immer verwendet werden kann, um die beiden Tabellen richtig zu vergleichen.

(Wenn IDs nicht für Übereinstimmungen verwendet werden können, würde ich auch eine Menge Fragen zu den Daten stellen, was jedoch außerhalb des Rahmens der Frage liegt.)

Dies schließt Daten ein, die nicht in der ursprünglichen Liste enthalten sind. Ich aktualisiere aktuelle Informationen nur in Arbeitsmappe 1 und ignoriere alle anderen Informationen. IE Sam wird nicht hervorgehoben, obwohl er nicht in der Liste der Arbeitsmappe 1 enthalten ist. Die fehlenden Daten von Max werden jedoch so hervorgehoben, wie er in der Liste der Arbeitsmappe 1 steht. Gibt es eine Möglichkeit, diese herauszufiltern? Wilson Liao vor 5 Jahren 0
@ WilsonLiao geschnipst! robinCTS vor 5 Jahren 0
Leider zählt Ihre Interpretation des Problems nicht - nur die OPs ;-) Ihre erste Formel ist ein vielversprechender Anfang - sie schließt alle Duplikate aus. Wenn jedoch die zweite Formel richtig funktioniert (und dazu gehört auch die Änderung der ersten), kann sie nicht garantieren, dass alle fehlenden Daten korrekt erfasst werden, da nur die erste erfasst wird. Manchmal greift es weniger und manchmal mehr als einmal dieselben Daten. Das einzige Mal ist es korrekt, wenn in Workbook 2 genau ein "fehlender" Name vorhanden ist, der genau einem Namen in Workbook 1 entspricht. robinCTS vor 5 Jahren 0
@robin meine Interpretation wurde erwähnt, weil ich nicht sicher war, ob ich es richtig gemacht hatte, es ist ein bisschen schwer zu korrigieren, um ein falsch interpretiertes Problem zu lösen ... was scheinbar passiert ist :-D Und deine nächsten Punkte sind ausgezeichnet - ich mischte mich Blatt 1 und 2. LJ01 vor 5 Jahren 0
-1
Rajesh S

Ich möchte die einfachste Methode zum Vergleichen und Erstellen einer eindeutigen Liste vorschlagen.

Folge diesen Schritten:

  • Kopieren Sie Daten aus beiden Arbeitsmappen in ein leeres Blatt.
  • Wählen Sie Ganze Daten aus.
  • Klicken Sie auf der Registerkarte "Start" auf Sortieren und Filtern.
  • Sortieren Sie die Daten in aufsteigender Reihenfolge nach ID.
  • Wählen Sie erneut die gesamten Daten aus.
  • Gehen Sie zur Registerkarte "Daten" und klicken Sie auf "Duplikat entfernen".
  • Wählen Sie die Spalte ID aus, um Duplikate zu suchen und zu entfernen.

Sie finden Ihre Daten wie folgt:

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

NB

  • Die oben gezeigte Methode eignet sich für kleine Datenbanken.
  • Sie können den gesamten Prozess als Makro aufnehmen, um schneller zu arbeiten.
Es tut mir leid, aber diese Lösung funktioniert nicht. Es schließt "Sam" in das Ergebnis ein, den das OP ganz klar ausgesprochen hat, ist auszuschließen. robinCTS vor 5 Jahren 0
Da es sich bei SAM um einen eindeutigen Datensatz in Blatt 2 handelt, erscheint er nach dem Vergleich beider Blätter für eindeutige Datensätze logisch. Da OP einfach Werte kopiert und eingefügt hat, hat es OP verpasst, es in die Liste aufzunehmen. Rajesh S vor 5 Jahren 0

Verwandte Probleme