Spalten vergleichen – fehlende Werte finden

Spalten unterschiedlicher Tabellen vergleichen, fehlende Werte auflisten und ergänzen

Regelmäßig und immer wieder wird Foren nach der Möglichkeit gefragt, Spalten in verschiedenen Tabellen oder Tabellenblättern (teilweise auch in unterschiedlichen Dateien) daraufhin zu vergleichen, welche Daten in einer zu vergleichenden Liste/Tabelle fehlen. In dieser Datei sind einige Kundennamen in der Tabelle1 Spalte A aufgeführt, in dem gleichen Tabellenblatt auch in Spalte D und auch noch zusätzlich im Arbeitsblatt Tabelle2 Spalte A sind jene Kundennamen aufgeführt, die gegen die erstgenannten Kunden (die in Spalte A) abgeglichen werden sollen.

Mit Hilfsspalte

Die wohl am meisten genutzte Möglichkeit besteht darin, eine Hilfsspalte einzufügen bzw. zu nutzen und dort per Formel zu berechnen, ob der jeweilige Kundenname in der zu vergleichenden Liste enthalten ist oder nicht. Idealerweise werden Sie die Spalte A mit der Tastenkombination StrgT oder StrgL zu einer „Intelligenten Tabelle“ machen. Achten Sie bitte darauf, dass im Dialogfenster das Häkchen bei Tabelle hat Überschriften gesetzt ist. Anschließend an das OK werden Sie in B2 diese Formel eintragen:
=ZÄHLENWENN($D$2:$D$48; A2)
… und wegen der Funktionalität der Intelligenten Tabelle wird diese Formel nun automatisch bis zur letzten Zeile nach unten kopiert. Sie vergleichen also die beiden Listen dieses Arbeitsblatts. Das Ergebnis ist in diesem Fall entweder 1 oder 0. Bei ihren eigenen Dateien kann es durchaus vorkommen, dass auch höhere Zahlen als die 1 angezeigt werden. Das ist immer dann der Fall, wenn in der zu vergleichenden Tabelle der entsprechende Name bzw. Wert mehrfach vorkommt. Jeder Kundenname mit einer 1 (bzw. einem höheren Wert) ist auch in der 2. Liste vertreten, eine 0 bedeutet, dass dieser Name in der Vergleichsliste fehlt. – Zur Kontrolle können Sie beispielsweise die Summe in Spalte B bilden und sie werden feststellen, dass als Ergebnis 46 herauskommt. Das aber sollte sie irritieren, denn es fehlen doch nur 3 Namen in der zu vergleichenden Spalte. Zugegeben, der Fehler ist nicht leicht zu finden. Filtern Sie doch erst einmal die Spalte B nach dem Wert 0. Dann bleiben die 4 fehlenden Namen übrig:

Die gefilterten Namen

Die gefilterten Namen

Die können Sie jetzt aufschreiben oder aber in die Zwischenablage kopieren, den Filter wieder aufheben und den Inhalt der Zwischenablage dann beispielsweise in F2 einfügen. Dort stehen dann die 4 Namen direkt untereinander. Den ersten und die beiden letzten Namen werden sie unschwer in Spalte D finden, der zweite Name ist hier auch vorhanden, scheint aber identisch mit dem aus der 1. Spalte zu sein.

Zugegeben, ich habe Ihnen hier eine richtig „nette“ Gemeinheit eingebaut. Ich gebe Ihnen einen kleinen Tipp: Schreiben Sie in G2 diese Formel: =GROSS(F2) und kopieren Sie diese bis zur Zeile 5 nach unten. Fällt Ihnen jetzt auf, warum sie der Meinung waren, die beiden Namen seien identisch? - In dieser Muster-Liste sind ja nur 50 Kunden-Namen enthalten. Bei Tabellen mit tausenden von Zeilen wird Ihnen die Suche durch Augenschein gewiss rasch lästig. Hier empfehle ich Ihnen, nach Teilen des entsprechenden Namens zu suchen und dann zu vergleichen.

In jedem Fall haben sie ihr Ziel erreicht. Sie sehen entweder auf dem Papier oder in ihrem Arbeitsblatt eine Liste der Namen, die in der zweiten Liste fehlen. Dass Sie bei der Gelegenheit den Vornamen der Frau Hauck auch noch anpassen werden, versteht sich von alleine. 😎

Prinzipiell werden sie mit einer Vergleichsliste in einem anderen Tabellenblatt oder in einer anderen Arbeitsmappe gleichermaßen vorgehen. Da ist der direkte visuelle Vergleich dann zwar nicht ganz leicht, aber dafür ist die erste Seite vielleicht etwas übersichtlicher.

Möchten Sie den Vergleich auch von der zweiten Liste zur ersten machen, dann ist das Vorgehen identisch. Eventuell werden sie die ungefilterten Ergebnisse auch in die jeweils andere Arbeitsmappe kopieren, um in jedem Workbook den Überblick zu haben. – Im Anschluss ist es dann gewiss kein Problem, die fehlenden Daten an das Ende der entsprechenden Liste zu kopieren.

▲ nach oben …

Vergleich mittels Power Query

Wenn sich die Liste mit den Ursprungsdaten ständig verändert / vergrößert, dann ist es vielleicht müßig, immer wieder diesen Vorgang durchzuführen. Vielleicht ist es auch nicht ganz so schön, eine Hilfsspalte in eine bestehende Tabelle einzufügen. Hier bietet sich das Power Query an, welches seit Version 2016 fest in Excel integriert ist. Beim ersten Mal ist der Aufwand vielleicht etwas höher, spätestens aber dann, wenn sie nach einer Änderung in den Quelldaten die Auswertung erneut durchführen wollen, werden sie den Vorteil von PQ zu schätzen wissen.  💡 

Nicht in Tabelle_(2)

Um eine „klare Linie“ zu schaffen, schließen Sie diese Mappe ohne sie zu speichern oder speichern Sie das Workbook unter einem anderen Namen, damit das Original für weitere Übungen unverändert bleibt. Öffnen Sie nun bitte wiederum diese Datei. Es ist die unveränderte Ausgangslage der eben verwendeten Arbeitsmappe. Sie haben hier also die gleichen Voraussetzungen, wie zu Beginn der ersten Lösung mit der ZÄHLENWENN-Funktion. Markieren Sie hier eine beliebige Zelle in den Daten der Spalte A, aktivieren die Power Query-Funktionalität und wählen dann den Punkt Aus Tabelle bzw. Von Tabelle. Achten Sie auch hier darauf, dass bei dem vorgeschlagenen Bereich für die Daten das Kästchen Tabelle hat Überschriften mit einem Häkchen versehen ist.

Nach einem OK öffnet sich der Abfrage-Editor und die Namen der Kunden sind alle in der einzigen Spalte vorhanden. Geben Sie im rechten Seitenfenster der Tabelle einen „sprechenden“ Namen, beispielsweise qry_Kunden 1. Ich verwende das Präfix qry_ immer zur Kenntlichmachung eine Abfrage (Query), für Tabellen verwende ich immer tbl_ als Präfix. Anschließend ein Klick auf das Symbol Schließen & laden. Umgehend wird in einem getrennten Tabellenblatt eine neue Liste mit den Daten der Abfrage erstellt. Dass diese Tabelle identisch mit den Daten der ursprünglichen Tabelle sind, ist hier nicht relevant. Wichtig ist nur der Bezug zur Abfrage. – Wenn Sie mehr Erfahrung mit PQ gesammelt haben, werden Sie solch eine Abfrage vielleicht nur als Verknüpfung herstellen, ohne sie in einer physischen Tabelle zu speichern.

Wechseln sie wieder zum Blatt Tabelle1 und erstellen sie auf gleichem Wege eine neue Abfrage mit den Daten der Spalte D. Geben Sie in dieser Abfrage im Editor den Namen qry_Kunden 2. In den meisten Fällen ist links des Editors derzeit kein Seitenfenster zu sehen, nur ein schmaler Balken mit der Bezeichnung Abfragen und einem nach rechts zeigenden Pfeil >. Klicken Sie entweder auf den Text oder den Pfeil und es wird sich auch links ein Seitenfenster öffnen, wo sie beide existierenden Abfragen sehen:

Beide Abfragen im linken Seitenfenster

Beide Abfragen im linken Seitenfenster

Klicken Sie dort auf die Abfrage qry_Kunden 1 und diese wird zur aktuellen Abfrage im Query Editor. Wählen Sie nun im Register Start den Punkt Kombinieren und im Dropdown-Menü die Auswahl Abfragen zusammenführen. Das Ganze stellt sich nun so dar:

Der erste Schritt beim Zusammenführen

Der erste Schritt beim Zusammenführen

Sie erkennen in der oberen Hälfte die ersten Namen der Kunden aus der Quelltabelle. In dem Textfeld darunter klicken Sie rechts auf den Dropdown-Pfeil und wählen durch Klick die zweite Abfrage qry_Kunden 2. Nun markieren Sie im oberen sowie im unteren Teil die jeweils einzige Spalte. Unterhalb dieser beiden Fenster können Sie nun noch die Join-Art definieren. Wählen Sie hier den Vorletzten Punkt Linker Anti Join (Zeilen nur in erster):

Der zweite Schritt beim Zusammenführen

Der zweite Schritt beim Zusammenführen

… und bestätigen Sie nun mit der Schaltfläche OK. Umgehend werden Ihnen in der Abfrage die 4 in Tabelle_2 fehlenden Kundennamen aufgelistet:

Die gefilterten Unterschiede der zweiten Spalte

Die gefilterten Unterschiede der zweiten Spalte

Nochmals zur Klarstellung: Linker Anti-Join (Zeilen nur in erster) bedeutet, dass all jene Zeilen (Namen) aufgezeigt werden, die nur in der ersten (sprich oben angezeigten) Datei vorhanden sind und in der zweiten (unteren) fehlen.

Die zweite Spalte der Ergebnis-Abfrage mit dem -Symbol brauchen Sie in diesem Fall nicht, löschen Sie diese einfach. Schließen & laden und diese Tabelle wird auch in ein neues Tabellenblatt geschrieben. In einem weiteren Arbeitsblatt wird auch automatisch diese zweite Abfrage geschrieben. Das Arbeitsblatt mit den 46 Namen können Sie auch löschen, es wird -im Gegensatz zu den 4 gefilterten Namen- nicht mehr gebraucht. Was vielleicht noch sinnvoll oder hilfreich wäre: Passen Sie die Überschrift der Liste mit den in Tabelle_2 fehlenden Kunden so an, dass klar ersichtlich ist, was diese Namen darstellen. Und das machen Sie idealerweise in dem Query-Editor, denn sonst wird bei der nächsten Aktualisierung wieder die Überschrift aus dem Editor in die erstellte Tabelle übernommen, was Sie ja gewiss nicht wollen.

Aus Trainings-Gründen sollten Sie auch noch einmal auf die Daten im zweiten Arbeitsblatt zugreifen und diese zum Vergleich verwenden. Sie sollen also all jene Namen herausfinden, die zwar in Tabelle_2 nicht jedoch in Tabelle_1 aufgeführt sind. – Wenn Sie mögen, üben Sie noch ein wenig. In jedem Fall werden Sie diese Mappe vor der kommenden Übung schließen. Ob Sie speichern, bleibt Ihnen überlassen.

▲ nach oben …

Gegenseitiger Abgleich

Nun ist es ja nicht immer so, dass in einer Liste alle Namen vorhanden sind und in einer weiteren Aufstellung nur die fehlenden aufgezeigt werden sollen. Es gibt durchaus auch Situationen, wo die zweite Liste Daten enthält, welche in die erste übertragen werden müssen. Anders ausgedrückt: Die Tabelle_1 soll um all jene Namen ergänzt werden, die ausschließlich in der Tabelle_2 vorhanden sind. Naturgemäß ist es meist extrem schwierig, in großen Datenbeständen einen Datenabgleich in angemessener Zeit vorzunehmen.

In der Realität hat ja jeder Kunde auch eine Kundennummer, welche ein eindeutiges Merkmal darstellt. In diesen Dateien sind (nach dem entpacken) die gleichen Kunden (und einige wenige Ergänzungen) mit ihren Kundennummern erfasst. Sie merken vielleicht schon an der Wortwahl, dass es sich hier um zwei Arbeitsmappen handelt; damit haben Sie Gelegenheit, auch diese Situation einmal zu trainieren. Als weitere Erschwernis kommt dazu, dass auch Unterschiede gefunden werden sollen, wenn die Groß- / Kleinschreibung unterschiedlich ist. Also: Hugo von Hurtig ist nicht gleich Hugo Von Hurtig und wird entsprechend auch nicht als gleicher Datensatz, sondern als Unterschied gefunden werden (solange die Kundennummer nicht identisch ist und zum Vergleich genutzt wird). Und genau das ist ein weiteres schlagendes Argument für Power Query.

Da in diesem Fall eine geöffnete Tabelle mit einer Liste verglichen werden soll, die nicht ein Teil der aktuellen Mappe ist, wird ein anderer Weg zur Integration der Daten in den Query Editor beschritten. Aber step bei step, von Beginn an. Die oben aufgeführte/verlinkte Datei ist ja vom Typ *.zip, sie muss also von Ihnen erst einmal entpackt werden. Sie haben jetzt 2 Files zur Verfügung:
Spalten_vergleichen_Liste_ 2a.xlsx und Spalten_vergleichen_Liste_ 2b.xlsx

Öffnen Sie zu Beginn die erstgenannte Datei (2a). Erstellen Sie mittels der Power Query Funktionalität eine Abfrage mit diesen Daten. Natürlich Bearbeiten und danach speichern Sie gleich die Query, ohne Änderungen vorzunehmen per Schließen & laden.

Um die Daten der zweiten, aus dieser Sicht der externen Datei zu erlangen, wechseln Sie erst einmal wieder zum Arbeitsblatt Tabelle1. Mittels Power Query wählen in Versionen 2010/13 direkt den Punkt Aus Datei, beginnend mit Version 2016 im Menü Daten den Punkt Neue Abfrage und dort Aus Datei. Im nächsten Schritt wählen Sie Excel-Datei bzw. Aus Arbeitsmappe. Die folgenden Schritte sind selbsterklärend; Sie suchen und markieren die entsprechende Datei (2b) im File-Dialog, klicken auf Importieren und wählen natürlich das Blatt Tabelle1 und im Navigator laden Sie diese Auswahl über die Bearbeiten – Schaltfläche. Umgehend wird auch von diesen Daten eine weitere Abfrage erstellt. Diese Query ist nun auch in der Mappe, von wo aus die zweite Datei importiert worden ist.

Erweitern Sie nun das linke Seitenfenster so, dass die beiden Abfragen dort sichtbar sind. Zunächst sollten Sie die beiden Namen der Abfragen ändern. Dazu können Sie auch im linken Seitenfenster auf einen Eintrag klicken, F2 und dann den jeweils neuen Namen schreiben. Mein Vorschlag für die beiden Bezeichner: qry_Kundendaten (1) und entsprechend qry_Kundendaten (2). Aktivieren Sie nun die erste der beiden Abfragen durch einen Klick in linken Seitenfenster auf diesen Eintrag.

Wenn Sie die höhere Flexibilität bewahren wollen, wählen Sie nun im Menü Start | Abfrage | Verwalten den Punkt Duplizieren. Dadurch wird eine Kopie der aktuellen Abfrage erstellt. Hieraus soll ja der Extrakt jener Datensätze erstellt werden, die zwar in der externen Datei nicht jedoch in der Haupt-Datei (Kundendaten (1)) vorhanden oder vielleicht anders geschrieben sind. Darum benennen Sie diese duplizierte Abfrage in qry_Kundendaten (1)_err um. „err“ steht übrigens für error, Fehler.

Die folgenden Schritte kennen Sie schon weitestgehend: Im Register Start das Symbol Kombinieren, im Untermenü den Punkt Abfragen zusammenführen. Im neuen Dialog wählen Sie als zweite Abfrage qry_Kundendaten (2). Obwohl die beiden Überschriften zu den Kundennamen unterschiedlich sind, markieren Sie in beiden Abfragen die rechte der beiden Spalten, also die mit den Kundennamen. Es ist wahrscheinlich, dass jetzt eine Sicherheitsabfrage durch Power Query erfolgt:

Power Query nimmt es mit der Datensicherheit recht genau …

Power Query nimmt es mit der Datensicherheit recht genau …

Lesen Sie sich die gerne aufmerksam durch, ich habe mich für diese Übung dafür entschieden, die Option Öffentlich zu wählen und dann zu speichern. Situationsbedingt werde ich mich bei geschäftlichen Daten gewiss zu einer adäquaten Einstellung entscheiden.

Beim Join-Art wählen Sie hier Rechter Anti-Join, denn es sollen ja all jene Zeilen als Ergebnis der Abfrage gelistet werden, die nur in der zweiten (unteren) Liste enthalten sind und nicht in der oberen. Anschließend OK. Ich muss gestehen, dass mich dieses Ergebnis doch ziemlich überrascht hat:

Nicht gut, es sollten mehrere Zeilen sein …

Nicht gut, es sollten mehrere Zeilen sein …

Da ich selber die beiden Tabellen erstellt habe weiß ich, dass in dieser 2. Liste mehrere Namen enthalten sind, die in der ersten, der Hauptliste fehlen. Eine kurze Kontrolle hat mir das dann auch bestätigt. – Was ist also schiefgelaufen? Die einzige gravierende Änderung gegenüber dem Vorgehen in der letzten Runde ist, dass wir dieses Mal den Rechten Anti-Join und nicht den linken gewählt haben. Schließlich sollen ja nur die Datensätze herausgefiltert werden, die in der Haupt-Tabelle fehlen.

Manchmal trügt aber auch der Schein. Klicken Sie in der Überschrift NewColumn auf das Erweitern-Symbol Doppelpfeil und entfernen Sie im Dialog nur das Häkchen bei Ursprünglichen Spaltennamen als Präfix verwenden. Nach einem OK stellt sich das ganze doch schon etwas anders dar:

… es war doch der richtige Weg

… es war doch der richtige Weg

Bingo! Das war’s. Mit diesem Ergebnis bin ich zufrieden; zumindest was die letzten beiden Spalten betrifft. Auch wenn sie gleich die ersten beiden Spalten löschen werden, sollten Sie sich die Überschriften merken. Denn sie werden diese beiden Kopfzeilen gleich noch einmal in exakt dieser Schreibweise brauchen. – Löschen Sie jetzt die beiden linken Spalten und ändern Sie die Überschriften der beiden nun verbliebenen Spalten entsprechend den eben gemerkten oder notierten Begriffen. Achten Sie dabei bitte auch auf anhängende Leerzeichen, die würden das Bild (und den Wert) verfälschen. Wenn Sie mögen, speichern Sie auch diese Abfrage per Schließen & laden.

Verweilen Sie einen Moment in diesem Arbeitsblatt. Vielleicht stellt sich ihnen die Frage, wie sie diese Daten, diese Tabelle im Arbeitsblatt auch später korrekt zuordnen können. Das Ändern des Blatt-Register-Namens ist nicht unbedingt hilfreich, denn die Liste (einschließlich der Überschrift) kann problemlos und ohne Einschränkung der Funktionalität in ein anderes Blatt der Mappe verschoben werden. Allerdings gibt es einen eindeutigen und unverwechselbaren Parameter: Im Menüband ist an erster Position der Tabellenname sichtbar, wenn im Menü die Tabellentools | Entwurf aktiviert sind:

Eindeutige Identifikationsmöglichkeit der Tabelle

Eindeutige Identifikationsmöglichkeit der Tabelle

Dieser Name entspricht dem Namen der Abfrage. Eventuell ist aber ein Leerzeichen durch einen Unterstrich ersetzt worden. – Das ist schon mal ein guter Anhaltspunkt. Aber es geht noch komfortabler: Öffnen Sie die Abfrage noch einmal auf beliebige Weise. Klicken Sie mit rechts in die Überschrift Kunden und wählen aus dem Kontextmenü Umbenennen… Ich benenne diese Spalte nun um in Kunden extern Error. Wiederum Schließen & laden und die Aussagekraft der Tabelle bzw. Abfrage ist in jedem Fall erheblich besser gegeben. 😉 

Hinweis: Auch wenn es ungewöhnlich scheint, dass in dieser Situation die ersten beiden Spalten der Zusammenführen-Abfrage keine Werte enthalten, ist das dennoch absolut korrekt. Der Grund dafür: Die Überschriften der jeweils verknüpften Spalten sind nicht identisch. Wäre die Schreibweise in jedem Punkt gleich, dann würden die Daten auch direkt in die 1. beiden Spalten eingefügt werden.

Wie heißt es doch so schön bei Wilhelm Busch und seinen wohl bekanntesten Figuren (Max und Moritz): „Dieses war der erste Streich, und der zweite folgt sogleich“. Die Basis zum angleichen der 1. Datei (Hauptdatei) ist gelegt, aber es geht ja darum, einen Abgleich der beiden Tabellen untereinander zu erstellen. Und dazu muss natürlich am Ende der Prozedur der Stand beider Listen identisch sein. – Um die Unterschiede aus der Sicht der externen Datei darzustellen, öffnen Sie nun die Abfrage qry_Kundendaten (2). Auch hier gehen Sie genauso vor, wie bei der Erstellung der ersten Fehler-Abfrage. Also eventuell ein Duplikat erstellen, Kombinieren, Abfragen zusammenführen, die andere Datei in den unteren Bereich durch Auswahl einfügen und anschließend Rechter Anti-Join auswählen. Last but not least die ersten beiden Spalten löschen. Und dann natürlich vor dem Speichern die 2. Spalte umbenennen: Kunden intern Error. Jetzt noch Schließen & laden und auch dieser Schritt ist geschafft.


In den beiden neu erstellten Abfragen bzw. Tabellen ist ja nun klar aufgeführt, welche Daten in der jeweils anderen Tabelle fehlen. Das ist zwar schön zu wissen, aber dadurch sind die jeweiligen Kunden-Daten immer noch nicht auf dem aktuellen und gleichen Stand. Es ist also erforderlich, die Original-Tabellen zu ergänzen.

Einer der möglichen Wege scheint zu einfach zu sein, um auch zum Ziel zu führen: Sie markieren jeweils die Daten mit den fehlenden Kunden und fügen diese per Hand einfach in die Liste mit den Original-Daten ein. Und ich kann Ihnen sagen: Es ist wirklich so einfach. Der Zweck wird damit voll und ganz erfüllt. Der einzige Haken bei der Sache: Sie werden diesen Vorgang jeweils von Hand durchführen müssen, wenn Sie einen Abgleich-Lauf gefahren haben. – Dennoch: Weiter unten wird noch eine kleine Fehlerquelle diskutiert.

Wenn Sie solch einen Abgleich in regelmäßigen Abständen vornehmen, dann sollten Sie das ergänzen der Daten auch Power Query überlassen. Beginnen Sie den Vorgang damit, dass sie die Abfrage qry_Kunden (1) beispielsweise durch Klick im linken Seitenfenster öffnen. Auch hier geht der Weg über den Menüpunkt Kombinieren. Allerdings wollen sie keine Abfragen zusammenführen sondern sie werden an die bestehende Query eine weitere Abfrage anfügen. Wählen Sie also diesem Punkt und wenn sie im Textfeld auf den Dropdown-Pfeil_ klicken, ist qry_Kunden (1)_err die richtige Wahl. Bestätigen Sie mit OK und es tut sich etwas, was Sie nicht unbedingt erwartet haben:

Die 3. Spalte ist ein unerwartetes Ergebnis

Die 3. Spalte ist ein unerwartetes Ergebnis

Es wurden zwar alle Datensätze der Abfrage mit den fehlenden Werten angehängt, die Kundennummern stehen auch an der korrekten Position aber die Namen wurden in eine dritte Spalte platziert. Was Sie auf den ersten Blick wahrscheinlich irritieren wird ist eigentlich ganz logisch: Power Query kann nicht wissen, dass die Daten in der Spalte Kunden vom gleichen Typus sind wie in die in der Spalte Kunden extern Error. Im Grunde genommen ist es das gleiche Prinzip wie beim zusammenführen der Daten vorhin. Unterschiedliche Überschriften führen zu unterschiedlichen Spalten.

Da dieses ja ganz offensichtlich ein kleiner Missgriff war, klicken Sie im rechten Seitenfenster bei Angewendete Schritte auf das Löschen-Symbol links des Textes Angefügte Abfrage  und dieser Schritt wird komplett rückgängig gemacht. Wechseln Sie nun zu Abfrage qry _Kunden (1)_err und ändern Sie die Überschrift der zweiten Spalte auf Kunden. Ich weiß, das hatten Sie schon einmal dort stehen. Aber wegen der besseren Identifizierbarkeit hatten Sie den kleinen Ausflug unternommen. 😉 Wenn Sie jetzt noch einmal in dieser Abfrage auf dem gleichen Wege wie eben gehen werden sie rasch erkennen, dass die fehlenden Daten wie gewünscht ein- bzw. angefügt worden sind. Dieses Vorgehen werden sie äquivalent mit der zweiten Kunden-Abfrage durchführen.

▲ nach oben …

Korrekturen doppelter Kundennummern

Fertig. Wirklich fertig? Nein! Es bleibt doch immer noch das Problem mit beispielsweise Frau Irma Hauck. Sie erinnern sich, dass sie einmal mit einem großen I und ein 2. Mal mit einem kleinen l als 1. Zeichen des Vornamens gespeichert ist. Darum ist sie jetzt in beiden Tabellen auch zweifach in jeweils der richtigen und falschen Schreibweise vorhanden. Jetzt könnten Sie natürlich auf die prinzipiell gute Idee kommen, einfach bei der Kundennummer doppelte Datensätze entfernen zu lassen. Aber da stellt sich natürlich die Frage, ob mit Sicherheit jener Datensatz entfernt wird, der fehlerhaft ist. Hier in dieser kurzen Beispiel-Datei wissen Sie, dass nur dieser eine Datensatz ein Ausreißer ist und die anderen integer sind. Aber das ist ja eigentlich die Ausnahme. Oder gibt es vielleicht doch noch mehr dieser Eigentlich-Dubletten? Wenn Sie auch nur den leisesten Verdacht haben, dass Kunden-Namen in verschiedene Schreibweise aber mit gleicher Kundennummer in der Liste vorhanden sein könnten, dann ist ein recht komplexer Umweg wohl die bessere Lösung:

  • Öffnen Sie erst einmal die Abfrage qry_Kundendaten (1), die jetzt ja auch die eingefügten Daten enthält. „Offiziell“ wissen Sie ja gar nicht, ob dort doppelte Kundennummern enthalten sind oder nicht.
  • Um festzustellen, ob überhaupt diese Dubletten vorhanden sind und wenn ja wie viele, nutzen sie (natürlich) auch Power Query. Markieren Sie die Spalte KdNr. und wählen dann im Menüband Gruppe Transformieren den Punkt Gruppieren nach.
  • Die Vorgaben im Dialogfenster sind schon wie gewünscht: Gruppieren nach: KdNr., Neuer Spaltenname: Anzahl und als Vorgang: Zeilen zählen.
  • OK und es wird eine neue Abfrage mit 2 Spalten erstellt.
  • Die Spalte Anzahl enthält jeweils die Anzahl der Einträge für diese Kundennummer. Klicken Sie auf den Dropdownpfeil  dieser Spalte und Sie erkennen, dass es auch Kundennummern mit mehr als einem Eintrag gibt.

Alleine diese Erkenntnis sollte schon ausreichen, dass Sie den Korrekturbedarf erkennen. Möchten Sie noch wissen, welche Kundennummern betroffen sind, entfernen Sie das Häkchen bei der 1 und danach OK. Sie sehen, dass es sogar 2 doppelte Kundennummern gibt.

Wie Sie jetzt vorgehen wollen, sei ihnen überlassen. Diese 2 Zahlen würde ich mir aufschreiben, bei einer größeren Anzahl dieser Abfrage speichern und dann drucken. Oder aber ich mache gar nichts und widme mich gleich der eigentlichen Abfrage im Tabellenblatt. Bei mir ist das Tabelle2, wenn sie Fehlversuche zu verbuchen haben, kann das bei Ihnen durchaus eine andere Registerbezeichnung des Arbeitsblattes es sein. Sie können sich aber gut am rechten Seitenfenster orientieren. Wenn Sie das Arbeitsblatt anwählen und eine beliebige Zelle in der Liste markiert ist dann wird im rechten Seitenfenster die entsprechende Abfrage markiert sein. Hier also qry_Kundendaten (1).

Sie könnten jetzt diese beiden Kundennummern (also die 4 Zeilen) ganz normal filtern. Bei einer größeren Datenmenge ist es gewiss unproduktiv und fehleranfällig. Da schlage ich folgenden Weg vor:

  • In die Zelle C1 trage ich eine Überschrift ein, beispielsweise DubCheck.
  • Klick in irgendeine Zelle der Liste.
  • Im Menü-Register ist der Punkt Tabellentools | Entwurf aktiviert oder Sie machen das.
  • Im Menüband, Gruppe Eigenschaften markieren Sie den Punkt Tabellengröße ändern.
  • Wenn der Laufrahmen um die bisherige Tabelle sichtbar ist, Shift, um den Bereich zu erweitern. Bestätigung mit OK.
  • Tragen Sie in C2 diese Formel ein: =ZÄHLENWENN(A:A;A2)
  • Da die Formel dank der Intelligenten Tabelle automatisch bis zur letzten Zeile ausgefüllt wird, können Sie jetzt sofort alle Zeilen filtern, die mehr als einen Eintrag je Kundennummer haben.
  • Sortieren Sie jetzt nach der Kundennummer.
  • Bei Hugo von Hurtig ändern Sie einen der beiden Datensätze auf die gewünschte Schreibweise; normalerweise wird das „von“ klein geschrieben. Aus Gründen der Sicherheit sollten Sie diesen geänderten Eintrag per copy/paste auch in die andere Zelle kopieren, denn es könnte ja sein, dass eine der Einträge ein angehängtes Leerzeichen hat. Nur so ist gewährleistet, dass alle Zeilen dieser Kundennummer identisch sind.
  • Bei Frau Irma Hauck ist es mit der typischen Excel-Schriftart extrem schwierig, die korrekte Schreibweise zu erkennen. Hier ist es sinnvoll, ein Mal den Namen komplett neu zu schreiben und dann in die andere Zelle zu kopieren.
  • Rechtsklick in eine beschriebene Zelle der Spalte C, Zeile/Spalte löschen | Tabellenspalten um diese Spalte komplett zu löschen.
  • Ein Kontrollblick in das rechte Seitenfenster, es sind in der Abfrage qry_Kundendaten (1) 58 Zeilen geladen.
  • Öffnen Sie die Abfrage beispielsweise durch Doppelklick auf diesen Eintrag im rechten Seitenfenster.
  • Die Spalte KdNr. ist bereits markiert. Symbol Zeilen verringern | Zeilen entfernen | Duplikate entfernen.
  • Wiederum ein Kontrollblick, dieses Mal in die Statuszeile und erkennen, dass nun nur noch 56 Zeilen in der Abfrage vorhanden sind. 💡

Fertig. Wirklich? Ja, wirklich. Zu mindestens dann, wenn sie diese Abfrage per Schließen & laden speichern und als Basis für weitere Auswertungen verwenden. – Der Übung wegen können Sie die zweite der Tabellen gleichermaßen anpassen.

Vorweg angemerkt ein typischer Ausdruck aus der Kommunikation in Foren oder Chats: „Bitte nicht schlagen!“. Wenn Sie das alles noch einmal Revue passieren lassen wird Ihnen vielleicht auffallen, dass sie prinzipiell die doppelte Arbeit gemacht haben. Das Ziel war ja, dass beide Tabellen vom Inhalt her identisch sein sollen. Es würde also reichen, die Haupt­tabelle mit allen fehlenden Daten zu „bestücken“ und dann die Daten von Hand in die andere Tabelle zu kopieren. Damit ist ja zu 100% gewährleistet, dass der Inhalt beider Tabellen identisch ist. Aber bedenken Sie bitte eines: Ein Mal diese vermeintliche Doppelarbeit bedeutet aber auch, dass sie beim nächsten Abgleich nur noch auf Aktualisieren klicken müssen um mit einem Schlag beide Dateien, als auch die in der Tochter-Datei zu aktualisieren.

Ich könnte jetzt noch einen halben Roman dazu schreiben, was da alles noch machbar ist. Ich will Ihnen aber nicht die Freude am Experiment mit Power Query nehmen und auch unserem Sponsor GMG-Computer Consulting die Chance lassen, Ihnen zielgerichtet zu helfen.

▲ nach oben …

Dieser Beitrag wurde unter Daten zusammenführen, Daten-Import / -Export, File-Handling, Filtern & Sortieren, Ohne Makro/VBA, Power Query, Spalten bearbeiten, Suchen und finden, Tabelle und Zelle abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.