zwei Tabellen sortieren und vergleichen

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
InputBox MsgBox
Bild

Betrifft: zwei Tabellen sortieren und vergleichen
von: Shahed
Geschrieben am: 07.05.2015 09:37:09

Hallo zusammen,
kann jemand mir sagen, wie ich anhand VBA einen Code schreiben kann, dass:
2 Arbeitsmappen (Tabelle1 und Tabelle2) zuerst die Spalten B, C, M alphabetisch sortiert werden. Danach inhaltlich miteinandern verglichen werden.
Das Ergebniss kann gerne entweder per MsgBox oder in der Tabelle3 zurückgegeben werden.
Spaltenanzahl und Zeilenanzahl sind aber in der Regel gleich.
Die Tabellen haben jeweils mehr als 500.000 Datensätze und extrem GROß.
Ich bedanke euch alle recht herzlich!
beste Grüße
Shahed

Bild

Betrifft: AW: zwei Tabellen sortieren und vergleichen
von: JoWE
Geschrieben am: 07.05.2015 11:37:07
Hallo Shahed,
Du könntest die Tabellen per Makro in eine "Master-Arbeitsmappe" kopieren und dort eindeutig benennen (kannst Du aufzeichnen, falls wirklich notwendig incl. Sortierung, und dann nachbearbeiten). Für den Vergleich (auch ohne vorherige Sortierung) gibt es diverse Möglichkeiten. Eine davon findest Du hier:
http://www.digital-inn.de/threads/vba-tabellen-vergleichen-einmal-anders-excel-als-isam-datenquelle-datenbank.31323/
Sieht kompliziert aus, ist es aber gar nicht.
Vermutlich gibt's noch andere und vielleicht bessere Ideen, daher lasse ich die Frage offen
Gruß
Jochen

Bild

Betrifft: AW: zwei Tabellen sortieren und vergleichen
von: fcs
Geschrieben am: 07.05.2015 12:13:51
Hallo Shahed,
was soll denn verglichen werden? Spalten B, C und M oder alle andern Spalten, wenn B, C und M in beiden Tabellen identisch sind?
Sind die Spalten B, C und M Schlüsselfelder, die zusammen jeden Datensatz eindeutig kennzeichnen?
Kann es auch neue Datensätzen geben, die nur in Tabelle 2 vorkommen?
Kann es auch gelöschte Datensätze geben, die nur in tabelle 1 vorkommen?
Wie sollen diese im Ergebnisblatt ausgegeben werden?
Wie viele Datenspalten gibt es insgesamt in den Tabellen?
Davon hängt ab, ob man die Daten komplett in Datenarrays abarbeiten kann, oeder ob man Päckchenweise arbeiten muss, denn eine direkter Vergleich der Zellinhalte wird bei der Zeilenzahl sehr zeitaufwendig.
Gruß
Franz

Bild

Betrifft: AW: zwei Tabellen sortieren und vergleichen
von: Shahed
Geschrieben am: 07.05.2015 13:02:41
Dank für die Rückmeldung,
also ich hab 2 Excel Tabelle jeweils 500000 Zeilen und 15 Spalten.
ich kopiere die Inhalte auf 2 Arbeitsmappen (Tabelle1 und Tabelle2)
um die Inhalte abgleichen zu können, muss ich zuerst die zwei Tabellen sortieren. so habe ich jetzt einigemaße gleichheitige Datensätze. ( die Sortierung kann in den Spalten B, C, M von der beide Tabellen stattfinden)
danach müssen die zwei Tabelle (alle Spalten) miteinander vergliechen werden (Inhaltlich), ob die Identisch sind oder es Abweichungen gibt.
A1(Tabelle1)=A1(Tabelle2) ? B1(Tabelle1)=B1(Tabelle2) usw.... bis AU1
A2(Tabelle1)=A2(Tabelle2) ? B2(Tabelle1)=B2(Tabelle2)
.
.
.
USW... (bis zum Ende der Tabelle)

Spaltenanzahl und Zielenanzahl in beiden tabellen sind gleich.
das Ergebnis sollte mir sagen, ob es irgendeine Abweichung gibt, wenn ja, wo.
Das Ergebnis kann sogar, die abweichende Zelle farbig markieren oder wie auch immer.
Danke sehr im voraus!
Beste Grüße
Shahed

Bild

Betrifft: AW: zwei Tabellen sortieren und vergleichen
von: fcs
Geschrieben am: 07.05.2015 14:33:12
Hallo Shahed,
hier ein Makro mit 1:1 Zellen-Vergleich.
Ich hab es mit 25 Datenspalten und 500000 Zeilen getestet.
Das Ergebnis der Abweichungen wird in einem Tabellenblatt ausgegeben.
Ich hatte jetzt nur wenige Abweichungen eingebaut. Makrolaufzeit ca. 10 Sekunden (mittel-schneller Rechner.
Bei wesentlich mehr Datenspalten gibt es Arbeitsspeicherprobleme, dann muss der Vergleich in Zeilenblöcken gemacht werden.
Gruß
Franz

Sub Tabellenvergleich()
  Dim wkb_1 As Workbook, wkb_2 As Workbook
  Dim wks_1 As Worksheet, wks_2 As Worksheet
  Dim wks_Ergebnis As Worksheet
  Dim arrData1, arrData2, arrDataE()
  Dim Zeile_L As Long, Spalte_L As Long
  Dim Zeile As Long, Spalte As Long
  Dim ZeileE As Long
  
  Set wkb_1 = ActiveWorkbook   'Arbeitsmappe mit 1. Tabelle ggf. anpassen
  Set wkb_2 = ActiveWorkbook   'Arbeitsmappe mit 2. Tabelle ggf. anpassen
  
  Set wks_1 = wkb_1.Worksheets(1) '1. Tabelle - Indexnummer ggf. anpassen
  
  Set wks_2 = wkb_2.Worksheets(2) '2. Tabelle - Indexnummer ggf. anpassen
  
  Application.ScreenUpdating = False
  
  With wks_1
  'Zellfarbe zurücksetzen
    .UsedRange.Interior.ColorIndex = xlColorIndexNone
  'Daten sortieren
    Zeile_L = .UsedRange.Row + .UsedRange.Rows.Count - 1
    Spalte_L = .Cells(1, .Columns.Count).End(xlToLeft).Column
    With .Range(.Cells(1, 1), .Cells(Zeile_L, Spalte_L))
      .Sort key1:=.Range("B1"), order1:=xlAscending, _
         key2:=.Range("C1"), order2:=xlAscending, _
         key3:=.Range("M1"), order3:=xlAscending, Header:=xlYes
    End With
    
    'Daten in Array einlesen
    arrData1 = .Range(.Cells(1, 1), .Cells(Zeile_L, Spalte_L))
  End With
  
  With wks_2
  'Zellfarbe zurücksetzen
    .UsedRange.Interior.ColorIndex = xlColorIndexNone
  'Daten sortieren
    Zeile_L = .UsedRange.Row + .UsedRange.Rows.Count - 1
    Spalte_L = .Cells(1, .Columns.Count).End(xlToLeft).Column
    With .Range(.Cells(1, 1), .Cells(Zeile_L, Spalte_L))
      .Sort key1:=.Range("B1"), order1:=xlAscending, _
         key2:=.Range("C1"), order2:=xlAscending, _
         key3:=.Range("M1"), order3:=xlAscending, Header:=xlYes
    End With
    'Daten in Array einlesen
    arrData2 = .Range(.Cells(1, 1), .Cells(Zeile_L, Spalte_L))
  End With
  
  'Ergebnisarra mit vielen Spalten vordimensionieren
  
  'Spaltentitel ins Ergebnisarray schreiben
  ZeileE = 1
  ReDim arrDataE(1 To 4, 1 To ZeileE)
  arrDataE(1, ZeileE) = "Zeile"
  arrDataE(2, ZeileE) = "Spalte"
  arrDataE(3, ZeileE) = wks_1.Name
  arrDataE(4, ZeileE) = wks_2.Name
  
  'Alt-Daten abarbeiten
  For Zeile = 1 To UBound(arrData1, 1)
      For Spalte = LBound(arrData1, 2) To UBound(arrData1, 2)
        If arrData1(Zeile, Spalte) <> arrData2(Zeile, Spalte) Then
          ZeileE = ZeileE + 1
          ReDim Preserve arrDataE(1 To 4, 1 To ZeileE)
          arrDataE(1, ZeileE) = Zeile
          arrDataE(2, ZeileE) = Spalte
          arrDataE(3, ZeileE) = arrData1(Zeile, Spalte)
          arrDataE(4, ZeileE) = arrData2(Zeile, Spalte)
          wks_1.Cells(Zeile, Spalte).Interior.ColorIndex = 6
          wks_2.Cells(Zeile, Spalte).Interior.ColorIndex = 6
        End If
      Next
  Next Zeile
  
  If ZeileE > 1 Then
    Application.Workbooks.Add template:=xlWBATWorksheet
    Set wks_Ergebnis = ActiveWorkbook.Worksheets(1)
    With wks_Ergebnis
      .Cells(1, 1).Resize(ZeileE, 4) = Application.WorksheetFunction.Transpose(arrDataE)
    End With
  Else
    MsgBox "alle Datensätze in den Tabellen sind identisch"
  End If
  Erase arrData1, arrData2, arrDataE
  
  Application.ScreenUpdating = True
End Sub


Bild

Betrifft: AW: zwei Tabellen sortieren und vergleichen
von: Shahed
Geschrieben am: 07.05.2015 14:57:23
Genial , besten Dank Franz,
wie kann ich individuel bestimmen, welche Spalte am Anfang sortiert werden müssen. wenn weitere Spalten außer Spalten B, C, M sortiert werden müssten???
Besten herzlichen dank!!!!

Bild

Betrifft: AW: zwei Tabellen sortieren und vergleichen
von: fcs
Geschrieben am: 08.05.2015 01:28:59
Hallo Shahed,
ich hab das Makro jetzt so erweitert, dass in einer Input-Box weitere Spalten angegeben werden können, nach denen sortiert werden soll.
Gruß
Franz

Sub Tabellenvergleich()
  Dim wkb_1 As Workbook, wkb_2 As Workbook
  Dim wks_1 As Worksheet, wks_2 As Worksheet
  Dim wks_Ergebnis As Worksheet
  Dim arrData1, arrData2, arrDataE()
  Dim Zeile_L As Long, Spalte_L As Long
  Dim Zeile As Long, Spalte As Long
  Dim ZeileE As Long
  Dim strSort As String, varSplit, intSort As Integer
  
  
  Set wkb_1 = ActiveWorkbook   'Arbeitsmappe mit 1. Tabelle ggf. anpassen
  Set wkb_2 = ActiveWorkbook   'Arbeitsmappe mit 2. Tabelle ggf. anpassen
  
  Set wks_1 = wkb_1.Worksheets(1) '1. Tabelle - Indexnummer ggf. anpassen
  
  Set wks_2 = wkb_2.Worksheets(2) '2. Tabelle - Indexnummer ggf. anpassen
  
  strSort = InputBox("zusätzlich zu sortierende Spalte(n) außer ""B-C-M""" & vbLf _
    & "(Spalten jeweils duch Semicolon trennen, z.B.: K;L)" & vbLf _
    & "(Bei Abbrechen wird nur nach ""B-C-M"" sortiert)", "Sortierreihenfolge", "")
  
  Application.ScreenUpdating = False
  With wks_1
    Application.StatusBar = "Tabelle """ & .Name & """ wird sortiert und eingelesen"
  'Zellfarbe zurücksetzen
    .UsedRange.Interior.ColorIndex = xlColorIndexNone
  'Daten sortieren
    Zeile_L = .UsedRange.Row + .UsedRange.Rows.Count - 1
    Spalte_L = .Cells(1, .Columns.Count).End(xlToLeft).Column
    With .Range(.Cells(1, 1), .Cells(Zeile_L, Spalte_L))
      If strSort <> "" Then
        varSplit = Split(strSort, ";")
        For intSort = UBound(varSplit) To LBound(varSplit) Step -1
          .Sort key1:=.Range(Trim(varSplit(intSort)) & "1"), order1:=xlAscending, Header:=xlYes
        Next
      End If
      .Sort key1:=.Range("B1"), order1:=xlAscending, _
         key2:=.Range("C1"), order2:=xlAscending, _
         key3:=.Range("M1"), order3:=xlAscending, Header:=xlYes
    End With
    
    'Daten in Array einlesen
    arrData1 = .Range(.Cells(1, 1), .Cells(Zeile_L, Spalte_L))
  End With
  
  With wks_2
    Application.StatusBar = "Tabelle """ & .Name & """ wird sortiert und eingelesen"
  'Zellfarbe zurücksetzen
    .UsedRange.Interior.ColorIndex = xlColorIndexNone
  'Daten sortieren
    Zeile_L = .UsedRange.Row + .UsedRange.Rows.Count - 1
    Spalte_L = .Cells(1, .Columns.Count).End(xlToLeft).Column
    With .Range(.Cells(1, 1), .Cells(Zeile_L, Spalte_L))
      If strSort <> "" Then
        varSplit = Split(strSort, ";")
        For intSort = UBound(varSplit) To LBound(varSplit) Step -1
          .Sort key1:=.Range(Trim(varSplit(intSort)) & "1"), order1:=xlAscending, Header:=xlYes
        Next
      End If
      .Sort key1:=.Range("B1"), order1:=xlAscending, _
         key2:=.Range("C1"), order2:=xlAscending, _
         key3:=.Range("M1"), order3:=xlAscending, Header:=xlYes
    End With
    'Daten in Array einlesen
    arrData2 = .Range(.Cells(1, 1), .Cells(Zeile_L, Spalte_L))
  End With
  
  
  Application.StatusBar = "Daten vergleichen und Auswertungstabelle erstellen"
  'Spaltentitel ins Ergebnisarray schreiben
  'Ergebnisarray mit dimensionieren
  ZeileE = 1
  ReDim arrDataE(1 To 4, 1 To ZeileE)
  arrDataE(1, ZeileE) = "Zeile"
  arrDataE(2, ZeileE) = "Spalte"
  arrDataE(3, ZeileE) = wks_1.Name
  arrDataE(4, ZeileE) = wks_2.Name
  
  'Alt-Daten abarbeiten
  For Zeile = 1 To UBound(arrData1, 1)
      For Spalte = LBound(arrData1, 2) To UBound(arrData1, 2)
        If arrData1(Zeile, Spalte) <> arrData2(Zeile, Spalte) Then
          ZeileE = ZeileE + 1
          ReDim Preserve arrDataE(1 To 4, 1 To ZeileE)
          arrDataE(1, ZeileE) = Zeile
          arrDataE(2, ZeileE) = Spalte
          arrDataE(3, ZeileE) = arrData1(Zeile, Spalte)
          arrDataE(4, ZeileE) = arrData2(Zeile, Spalte)
          wks_1.Cells(Zeile, Spalte).Interior.ColorIndex = 6
          wks_2.Cells(Zeile, Spalte).Interior.ColorIndex = 6
        End If
      Next
  Next Zeile
  
  If ZeileE > 1 Then
    Application.Workbooks.Add template:=xlWBATWorksheet
    Set wks_Ergebnis = ActiveWorkbook.Worksheets(1)
    With wks_Ergebnis
      .Cells(1, 1).Resize(ZeileE, 4) = Application.WorksheetFunction.Transpose(arrDataE)
    End With
  Else
    MsgBox "alle Datensätze in den Tabellen sind identisch"
  End If
  Erase arrData1, arrData2, arrDataE
  Application.StatusBar = False
  Application.ScreenUpdating = True
  'MsgBox "Fertig"
End Sub


Bild

Betrifft: AW: zwei Tabellen sortieren und vergleichen
von: Shahed
Geschrieben am: 08.05.2015 09:44:31
Hervoragend Prima..
Herzlichen Besten Dank. es funktioniert SUPER :-))))))

 Bild

Beiträge aus den Excel-Beispielen zum Thema "zwei Tabellen sortieren und vergleichen"