Suchen nach Übereinstimmungen in mehreren Spalten

[Keik940]

Hallo zusammen,

ich brauche dringend Hilfe für mein Excel-Problem. Ich habe eine riesige Tabelle (ca. 60.000 Zeilen), die ich auf Übereinstimmungen prüfe. Die Daten in der Tabelle stammen aus zwei Systemen (System 1 und System 2), die direkt gegenüber gestellt werden sollen. Da die Daten sehr komplex sind, reicht es nicht nur eine Spalte in Vergleich zu setzen.

D.h. folgende Bedingungen muss die Formel erfüllen:

• In Spalte E, Spalte F, Spalte J und Spalte P sollen übereinstimmende Werte gefunden werden. Nur wenn alle Werte in der jeweiligen Spalte gleich sind, ist es eine Übereinstimmung.
  
• Das System in Spalte C muss sich gleichzeitig unterscheiden, d.h. es soll keine Übereinstimmungen nur im gleichen System geben.
  
• Wenn eine Übereinstimmung gefunden wurde, soll in Spalte B bei System 1 der Wert ausgegeben werden, der in Spalte B bei System 2 steht.
  

Diese Übereinstimmungen sollten i.d.R. eindeutig sein (wie beim Memory). Ist dies mal nicht der Fall, soll in Spalte B bei System 1 ein neuer Wert, z.B. „prüfen“ ausgegeben werden.

Hier das Anschauungsbeispiel mit dem Fall einer eindeutigen Übereinstimmung (orange) und mehrfachen Übereinstimmungen von System 1 zu System 2 (blau):

   

Wichtiger für mich wäre auf jeden Fall, dass die Zuordnung der direkten Übereinstimmung funktioniert. Die "prüfen"-Ausgabe ist ein praktischer Zusatz.

Ich hoffe jemand kennt dazu eine gute Lösung. Ich wäre euch echt dankbar!

Viele Grüße,
Keik940

[BoskoBiati]

Hi,

könnte man mit Zählenwenns machen, allerdings bräuchte man dazu eine Mustertabelle.

[Ego]

Hallo Keik...,


bei 60.000 Datensätzen würde ich ein kleines Makro vorschlagen.

Wie wäre es mit einer kleinen Beispieldatei, damit wir nicht jeder die Datei nachbauen müssen aber dennoch das Makro testen könnten?

Und noch einige Fragen:

1. Sind in der Spalte B nur Werte oder auch Formeln?
2. Was soll passieren, wenn in Spalte B bei  einem Eintrag zum "System 1" schon Daten vorhanden sind?
(Am einfachsten wäre es, wenn man vorhandene Einträge in Spalte B für "System 1" einfach löschen könnte, dann könnte man das Makro wiederholt aufrufen.)
3. Kann es vorkommen, dass es es Übereinstimmungen innerhalb "System 2" gibt und wie soll das gekennzeichnet werden?
4. Kann man davon ausgehen, dass zuerst die Einträge für "System 1" stehen und erst darunter die Einträge für "System 2"?

[Phi.VBA]

@Kaik

als ersten Schritt prüft der Code auf Gleichheit der Spalten E usw:

Code:

Sub sPhi()
Dim WSF As WorksheetFunction: Set WSF = Application.WorksheetFunction
Const lr = 21
Dim Ts() As String
ReDim Ts(1 To lr)
Bs = Range("A1:P" & lr)
Sy = Range("C1:C" & lr)
For i = 1 To lr
   Ts(i) = Bs(i, 5) & Bs(i, 6) & Bs(i, 10) & Bs(i, 16)
   If WSF.Match(Ts(i), Ts, 0) <> i Then Debug.Print i, WSF.Match(Ts(i), Ts, 0)
Next i
End Sub

Die Prüfung der Spalte C steht noch aus.

Der Code sollte relativ schnell werden.

[Keik940]

Hallo liebe Excel-Freunde,

vielen Dank schon einmal für die Antworten und Unterstützung!!! Ich habe mal einen Teil der Tabelle kopiert und die Daten in allen unwichtigen Spalten gelöscht, damit ihr ein Muster zum Probieren habt.

@ helmut:

Zu deinen Fragen:

1. In Spalte B sind nur Werte (genau wie in der gesamten Tabelle).
   
2. In Spalte B bei System 1 sind prinzipiell keine Werte enthalten. Die jetzt dort enthaltenen Werte sind nur als Beispiel, wie das Ergebnis aussehen soll.
   Da die Prüfung der Zeilen ja nacheinander geschieht, ist es nicht auszuschließen, dass am Anfang bei System 1 Daten eingetragen wurden, weil eine Übereinstimmung vorliegt und später eine erneute Übereinstimmung mit der gleichen Zeile zutrifft. In diesem Fall wäre eine Fehlermeldung wie das beschriebene „prüfen“ gut. (Bin da auch gerne für Tipps für geeignete Ausgabewerte offen!)
   
3. Deine Frage trifft einen Punkt den ich nicht richtig bedacht habe. In dem euch beschriebenen Fall kann es auch Übereinstimmungen in System 2 geben.
   Ich weiß nicht mit welcher Logik die Programmierung ablaufen kann.
   • Wenn immer nur eine Zeile aus System 2 mit allen Zeilen aus System 1 auf Übereinstimmung geprüft werden würde, stellt dies aus meiner Sicht kein Problem dar, da alle anderen Zeilen in System 2 nicht berücksichtigt werden.
     
   • Sofern dies nicht so eindeutig geschieht, müsste man zur weiteren Unterscheidung Spalte G hinzunehmen. Wenn in System 2 alle anderen genannten Spalten übereinstimmen, liefert die den Hinweis zur Unterscheidung, da wirklich jeder Wert nur einmal im System 2 vorkommt. In System 1 ist die Spalte G leer.
     
4. Ja, genauso ist es.
   

Ich werde auch ein bisschen versuchen mit dem Code rumzuprobieren. Da ich Neuling auf dem Gebiet bin, kann das etwas dauern und ich wollte ich euch erstmal die Antworten zukommen lassen!

Vielen Dank schon einmal!!!

Keik

[Ego]

Hallo Keik,

in der Anlage einmal ein kleines Programm und hier einige Bemerkungen.

1. Das Programm wird zur Zeit über den Button rechts neben der "so sieht es aus" Liste gestartet.

2. Das Programm erwartet, dass die gesamte Liste (ab Zelle A1 bis zur letzten zu untersuchenden Zeile) den Namen "Liste" hat.

3. Da die "System 1"-Zeilen in Spalte G keine Einträge haben kann das Programm diese Spalte auch nicht zur Unterscheidung hinzuziehen. Ich habe daher eine zusätzliche Ausgabe "mehrfach S2" eingerichtet, falls es zu einem Eintrag "System 1" mehrere passende Einträge "System 2" mit unterschiedlichen Werten in Spalte B gibt. (Kommt in dem Beispiel nicht vor)
Deine Vorgabe "prüfen" habe ich daher in "mehrfach S1" geändert.

4. Ich habe in deiner Liste "so sieht es aus" mehr Duplikate gefunden als du in "so soll es werden" aufgelistet hast. Kannst es ja einmal prüfen.

[Keik940]

Hallo Helmut,

vielen Dank für das Programm. Das sieht wirklich sehr gut aus!
Leider war ich die letzten Wochen krank und konnte mich bisher nicht damit beschäftigen, daher jetzt erst die Antwort.

Ich habe noch kleine Fragen/Anmerkungen zu deinem 3. Punkt:

Die zusätzliche Ausgabe „mehrfach S2“ ist evtl. nicht notwendig. Alle Daten in System 2 werden von mir durchweg in Spalte B durchnummeriert. D.h. selbst wenn es zu Dopplungen innerhalb aller anderen Spalten kommen sollte, sind die Zeilen in System 2 durch die Nummerierung in Spalte B immer nur einmalig vorhanden.

Ich habe jetzt festgestellt, dass es meine weitere Bearbeitung der Daten erleichtern würde, wenn statt der Ausgabe „mehrfach S1“ ein Hinweis auf die Nummer aus Spalte B aus System 2 existiert. Also wenn z.B. 2 Positionen in System 1 mit der Position 723 in System 2 übereinstimmen, als Ausgabewert jeweils „doppelt 723“ zu erzeugen. Ist das möglich?

Zu 4.: Ja du hast Recht. Genau das ist auch der Grund, warum ich das bei der Datenmenge automatisiert brauche und nicht manuell machen möchte. Da entstehen einfach zu viele Fehler ... :)

Viele Grüße,
Keik

[Ego]

Hallo Keik,

zu:

wenn statt der Ausgabe „mehrfach S1“ ein Hinweis auf die Nummer aus Spalte B aus System 2 existiert

Ich habe jetzt bei Wiederholungen innerhalb des System 1 beim ersten Auftreten den Wert aus System 2 und beim wiederholten Auftreten die Zeile des ersten Auftretens hinzugefügt.

zu:

Die zusätzliche Ausgabe „mehrfach S2“ ist evtl. nicht notwendig. Alle Daten in System 2 werden von mir durchweg in Spalte B durchnummeriert. D.h. selbst wenn es zu Dopplungen innerhalb aller anderen Spalten kommen sollte, sind die Zeilen in System 2 durch die Nummerierung in Spalte B immer nur einmalig vorhanden.

gerade wenn im System 2 die Spalte B eindeutig ist, kann das Programm nach den bisherigen Vorgaben nicht entscheiden welcher dieser Werte zugewiesen werden soll, wenn die zu vergleichenden Spalten übereinstimmen.

[Keik940]

Hallo zusammen,

@ Helmut: danke für deine umfassenden und schnellen Lösungen! Ab dem Punkt wäre ich schon lange nicht mehr in der Lage gewesen, das nur ansatzweise zu realisieren …

Ich denke aber bei der letzten Version haben sich ein paar Fehler eingeschlichen bzw. manche Ausgaben sind für den späteren Verwendungszweck nicht unbedingt praktikabel.
 

1. Zeile 18 & 19: Beide Zeilen stimmen mit dem Wert 1628 in System 2 überein. Es würde reichen, wenn dies so wie in Zeile 18 ausgeworfen wird, weil ich dann genau nach dieser Ausgabe filtern kann und somit alle Positionen, die das betreffen erfasse. So muss ich immer viel nach Zeilen suchen und davon habe ich ja in meiner Original-Excel 60.000, was das ganze erschwert.
   
2. Zeile 15 & 16: Wenn ein Wert nur in System 1 mehrfach vorkommt ist der Verweis auf die Zeile gut, aber ich bin mir nicht sicher ob er notwendig ist. In Zeile 45 und 47 ist die zugehörige Zeile nicht so schnell ersichtlich, da sie nicht direkt hintereinander vorkommen. Also entweder sollte dieser Zeilenverweis komplett entfallen und generell nur „mehrfach S1“ ausgegeben werden (da ich diese Positionen sowieso manuell prüfen muss) oder der Zeilenverweis sollte gegenseitig sein, wo aber Schwierigkeiten auftauchen, wenn es drei gleiche Positionen gibt.
   
3. Generell wäre es für das Filtern auch einfacher, wenn zuerst der Wert angegeben wird und dann ob dieser mehrfach in einem System vorkommt oder nicht. Beispiel „1628 mehrfach S1“. So stünde diese Position in der Filter-Liste direkt hinter „1628“ und ich kann mir direkt System 1 und 2 ohne viel suchen auf diesen Wert filtern.
   
4. Zeile 54: Warum wird hier eine Doppelung mit Zeile 53 ausgeworfen? Dort findet sich keine Übereinstimmung.
   
5. Zeile 62 bis 65: Die Ausgaben suggerieren mir, dass alle Zeilen mit Zeile 62 übereinstimmen, da sich sozusagen eine „Übereinstimmungskette“ ergibt. Allerdings gibt es eindeutig Unterschiede in Spalte J – SMK vs. SPF – und in Zelle P65 unterscheidet sich auch die Nummer von den anderen. Wo liegt da der Fehler?
   

 
Ich hoffe das ist jetzt nicht zu viel auf einmal und ich bin dankbar für erneute Tipps und Anregungen!

Viele Grüße,
Keik

[Ego]

Hallo Keik,

zu 4. und 5.
Da habe ich bei der Überarbeitung einen Kopierfehler eingebaut und im Programm die falsche Variable genutzt. (Es wurde immer Zeilennummer vor der aktuellen Zeile ausgewiesen.)

zu 1. und 3.
Ich habe den Vorschlag 3 umgesetzt.

zu 2.
Der Zeilenverweis ist jetzt gegenseitig und bei mehreren gleichen Positionen vollständig.