Microsoft Excel

Herbers Excel/VBA-Archiv

Zwei Tabellen je Zelle vergleichen + markieren | Herbers Excel-Forum


Betrifft: Zwei Tabellen je Zelle vergleichen + markieren von: Constantin
Geschrieben am: 11.01.2010 14:19:47

Hallo,

ich möchte zwei Tabellen (Tabelle1 und Tabelle2) in einer Arbeitsmappe (ab Zeile 2) miteinander vergleichen und die Unterschiede durch farbliche Markierung der jeweiligen Zelle in Tabelle1 sichtbar machen.

Ich versuche, die angedachte Logik einigermaßen präzise zu beschreiben und hoffe, dass es nicht zu langatmig ist:

Die Ausgangsdateien sind formatierungsmäßig identisch und besitzen 40 Spalten mit verschiedenen Zellformaten (numerisch, alphanumerisch, Datum), besitzen jedoch eine u. U. unterschiedliche Anzahl an Zeilen (insgesamt ca. 2000 Zeilen). In Spalte 3 steht ein Schlüssel (6-stellige Nummer), der für die Suche herangezogen werden soll. Alle weiteren Spalten (ab Spalte 4 bis 40) sind Zusatzinformationen, die nun verglichen werden sollen (Spalte 1 und 2 sind nicht relevant). Es sollen nun alle Zeilen von Tabelle1 abgearbeitet werden und dabei jeder Spalteninhalt mit der gleichen Spalte von Tabelle2 verglichen werden, sofern der Satz mit der gleichen Nummer gefunden werden kann. Unterscheiden sich beide Zellen in irgendeiner Weise, soll die Zelle von Tabelle1 gelb markiert und mit einem "x" in Spalte 41 versehen werden. Die aus Tabelle2 verglichene Zelle erhält in Spalte 41 ein "e" für erledigt. Wird die Zeile in Tabelle2 jedoch nicht gefunden, soll die ganze Zeile aus Tabelle1 grün markiert werden und ebenfalls ein "x" gesetzt werden.

Nun kann es sein, dass in Tabelle2 Sätze enthalten sind, die in Tabelle1 fehlen bzw. bei diesem Durchlauf nicht angesprochen wurden (also in Tabelle2 noch kein "e" in Spalte 41 haben). Diese Zeilen sind dann zu kopieren und an Tabelle1 anzuhängen. Alle angehängten Sätze sollen rot markiert werden. In Tabelle2 wird wieder ein "e" gesetzt, in Tabelle1 ein "x".

Auf der Suche nach einer Lösung bin ich auf nachfolgendes Programm gestoßen, welches vielleicht verwendet werden könnte. Leider ist mir nicht ganz klar, wie sich der zeilenweise Vergleich zusätzlich in einen zellbezogenen Vergleich erweitern ließe.

Wer hätte eine Idee, wie wie man das angehen könnte?

Vielen Dank im voraus für eure Hilfe.

Grüße, Constantin

Option Explicit
Option Base 1




Sub tt()
Dim wsA As Worksheet, wsB As Worksheet, gefunden As Boolean, zeiA As Long, zeiB As Long
Dim SatzA() As String, SatzB() As String, n As Long, nn As Long
Set wsA = Worksheets("Tabelle1")
Set wsB = Worksheets("Tabelle2")
With wsA
    .UsedRange.Interior.ColorIndex = xlNone
    ReDim SatzA(.Range("A65536").End(xlUp).Row)
    For zeiA = 1 To .Range("A65536").End(xlUp).Row
         SatzA(zeiA) = Zusammen(.Cells(zeiA, 1))
    Next zeiA
    .UsedRange.Interior.ColorIndex = xlNone
    ReDim SatzB(wsB.Range("A65536").End(xlUp).Row)
    For zeiB = 1 To wsB.Range("A65536").End(xlUp).Row
         SatzB(zeiB) = Zusammen(wsB.Cells(zeiB, 1))
    Next zeiB
    For n = 1 To UBound(SatzA)
        gefunden = False
        For nn = 1 To UBound(SatzB)
            If SatzA(n) = SatzB(nn) Then
                gefunden = True
                Exit For
            End If
        Next nn
        If gefunden = False Then .Range(.Cells(n, 1), .Cells(n, 6)).Interior.ColorIndex = 6 ' _
gelb
    Next n
    For n = 1 To UBound(SatzB)
        gefunden = False
        For nn = 1 To UBound(SatzA)
            If SatzB(n) = SatzA(nn) Then
                gefunden = True
                Exit For
            End If
        Next nn
        If gefunden = False Then
            wsB.Range(wsB.Cells(n, 1), wsB.Cells(n, 6)).Copy Destination:=.Cells(zeiA, 1)
            .Range(.Cells(zeiA, 1), .Cells(zeiA, 6)).Interior.ColorIndex = 10 'grün
            zeiA = zeiA + 1
        End If
    Next n
End With
End Sub


Function Zusammen(Zelle As Range) As String
Dim s As Integer, Spalten As Integer
Spalten = 6 'Spaltenanzahl
For s = 0 To Spalten - 1
Zusammen = Zusammen & Zelle.Offset(0, s)
Next s
End Function

' geschrieben von Reinhard

  

Betrifft: AW: Zwei Tabellen je Zelle vergleichen + markieren von: Daniel
Geschrieben am: 12.01.2010 02:09:30

Hi

bei deiner Könnensstufe würde ich da erstmal VBA-Frei vorgehen:
1. die ID-Nummern aus beiden Tabellen in eine neue Tabelle kopieren
2. über die Funktion DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN eben jenes tun (ab 2007 auf diese weise, in den älteren Versionen geht das über den Spezialfilter)
3. über die SVERWEIS-Funktion die entsprechenden Daten aus beiden Tabellen einlesen, gleiche Daten aus beiden Tabellen sollten jetzt direkt nebeneinander stehen.
4. fehlende Datensätze erkennst du am NV-Fehler der Sverweisfunktion, die übrigen Daten kannst du jetzt direkt miteinander vergleichen, und Abweichungen per Formel oder per Bedingter Formatierung anzeigen lassen.

Gruß, Daniel


  

Betrifft: AW: Lösung über SVerweis von: Constantin
Geschrieben am: 12.01.2010 10:50:49

Hallo Daniel,

sogesehen muß ich Dir recht geben. In beiden Tabellen dürften 90-95 % der Zeilen identisch sein. Es kann jedoch sein, dass z.B. eine Identnummer in Tabelle2 gefunden wird, dort aber die Spalte18 Status den Wert "A" enthält, wohingegen beim Ausgangssatz von Tabelle1 der Status (ebenfalls in Spalte 18) den Wert "B" haben kann und markiert werden müsste. Beim Löschen von Duplikaten müsste man daher (nahezu) die ganze Zeile einbeziehen (Spalte 4-40), da ein Unterschied in jeder dieser Spalten auftreten könnte.

Nur bei unterschiedlicher Zeilenanzahl (jede Identnummer ist immer eineindeutig) ließen sich anhand dieser Nummer Duplikate erkennen.

Mir wäre schon geholfen, eine Routine zu kennen, die die Spalteninhalte (von Spalte 4-40) vergleicht, wenn ein Satz mit der gleichen Identnummer in Tabelle2 gefunden wurde.

Vielleicht komme ich dann schrittweise weiter.

Danke erstmal für Deine Rückmeldung.

Gruß, Constantin


  

Betrifft: AW: Lösung über SVerweis von: Daniel
Geschrieben am: 12.01.2010 21:43:28

Hi

genau das meine ich ja,
du musst erstmal eine Liste erstellen, die ALLE Identnummern aus beiden Listen enthält (aber nur 1x, nicht doppelt).
dann lädst du die Werte aus Tabelle1 in die Spalte 4-40 und die Werte aus Tabelle2 in die Spalten 44-80
oder, wenn das für dich übersichtlicher ist, dann die Werte aus Tabelle1 in die Spalten 4, 6, 8, 10 usw und die Werte aus Tabelle2 in die Spalten 5, 7, 9, 11,...
Dann sollten gleiche Werte in der gleichen Zeile oder sogar nebeneinander stehen (im 2. Fall) und du kannst per einfacher Vergleichsformel die Abweichungen markieren.

Gruß, Daniel


  

Betrifft: AW: Lösung über SVerweis von: Constantin
Geschrieben am: 12.01.2010 23:18:54

... verstanden. Das scheint mir für einmalige oder gelegentliche Vergleiche ein guter, praktikabler Ansatz zu sein. Ich fürchte, dass bei regelmäßigen Auswertungen, die ich damit bewerkstelligen will, es vielleicht als Dauerlösung nicht so optimal sein könnte. Immerhin, wenn es anders nicht (rechtzeitig) klappen sollte, kann ich auf diese Alternative zurückgreifen.

Vielen Dank.

Gruß, Constantin


Beiträge aus den Excel-Beispielen zum Thema "Zwei Tabellen je Zelle vergleichen + markieren"