Regelmäßig und immer wieder wird Foren nach der Möglichkeit gefragt, Spal­ten in ver­schiede­nen Tabellen oder Tabel­len­blät­tern (teil­weise auch in unter­schiedlichen Dateien) daraufhin zu ver­gle­ichen, welche Dat­en in ein­er zu ver­gle­ichen­den Liste/Tabelle fehlen. In dieser Datei sind einige Kun­den­na­men in der Tabelle1 Spalte A aufge­führt, in dem gle­ichen Tabel­len­blatt auch in Spalte D und auch noch zusät­zlich im Arbeits­blatt Tabelle2 Spalte A sind jene Kun­den­na­men aufge­führt, die gegen die erst­ge­nan­nten Kun­den (die in Spalte A) abgeglichen wer­den sollen.

Mit Hilfsspalte

Die wohl am meis­ten genutzte Möglichkeit beste­ht darin, eine Hil­f­ss­palte einzufü­gen bzw. zu nutzen und dort per Formel zu berech­nen, ob der jew­eilige Kun­den­name in der zu ver­gle­ichen­den Liste enthal­ten ist oder nicht. Ide­al­er­weise wer­den Sie die Spalte A mit der Tas­tenkom­bi­na­tion StrgT oder StrgL zu ein­er „Intel­li­gen­ten Tabelle” machen. Acht­en Sie bitte darauf, dass im Dialogfen­ster das Häkchen bei Tabelle hat Über­schriften geset­zt ist. Anschließend an das OK wer­den Sie in B2 diese Formel ein­tra­gen:
=ZÄHLENWENN($D$2:$D$48; A2)
… und wegen der Funk­tion­al­ität der Intel­li­gen­ten Tabelle wird diese Formel nun automa­tisch bis zur let­zten Zeile nach unten kopiert. Sie ver­gle­ichen also die bei­den Lis­ten dieses Arbeits­blatts. Das Ergeb­nis ist in diesem Fall entwed­er 1 oder 0. Bei ihren eige­nen Dateien kann es dur­chaus vorkom­men, dass auch höhere Zahlen als die 1 angezeigt wer­den. Das ist immer dann der Fall, wenn in der zu ver­gle­ichen­den Tabelle der entsprechende Name bzw. Wert mehrfach vorkommt. Jed­er Kun­den­name mit ein­er 1 (bzw. einem höheren Wert) ist auch in der 2. Liste vertreten, eine 0 bedeutet, dass dieser Name in der Ver­gle­ich­sliste fehlt. – Zur Kon­trolle kön­nen Sie beispiel­sweise die Summe in Spalte B bilden und sie wer­den fest­stellen, dass als Ergeb­nis 46 her­auskommt. Das aber sollte sie irri­tieren, denn es fehlen doch nur 3 Namen in der zu ver­gle­ichen­den Spalte. Zugegeben, der Fehler ist nicht leicht zu find­en. Fil­tern Sie doch erst ein­mal die Spalte B nach dem Wert 0. Dann bleiben die 4 fehlen­den Namen übrig:

Die gefilterten Namen

Die kön­nen Sie jet­zt auf­schreiben oder aber in die Zwis­chen­ablage kopieren, den Fil­ter wieder aufheben und den Inhalt der Zwis­chen­ablage dann beispiel­sweise in F2 ein­fü­gen. Dort ste­hen dann die 4 Namen direkt untere­inan­der. Den ersten und die bei­den let­zten Namen wer­den sie unschw­er in Spalte D find­en, der zweite Name ist hier auch vorhan­den, scheint aber iden­tisch mit dem aus der 1. Spalte zu sein.

Zugegeben, ich habe Ihnen hier eine richtig „nette” Gemein­heit einge­baut. 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 jet­zt auf, warum sie der Mei­n­ung waren, die bei­den Namen seien iden­tisch? – In dieser Muster-Liste sind ja nur 50 Kun­den-Namen enthal­ten. Bei Tabellen mit tausenden von Zeilen wird Ihnen die Suche durch Augen­schein gewiss rasch lästig. Hier empfehle ich Ihnen, nach Teilen des entsprechen­den Namens zu suchen und dann zu ver­gle­ichen.

In jedem Fall haben sie ihr Ziel erre­icht. Sie sehen entwed­er auf dem Papi­er oder in ihrem Arbeits­blatt eine Liste der Namen, die in der zweit­en Liste fehlen. Dass Sie bei der Gele­gen­heit den Vor­na­men der Frau Hauck auch noch anpassen wer­den, ver­ste­ht sich von alleine. 😎

Prinzip­iell wer­den sie mit ein­er Ver­gle­ich­sliste in einem anderen Tabel­len­blatt oder in ein­er anderen Arbeitsmappe gle­icher­maßen vorge­hen. Da ist der direk­te visuelle Ver­gle­ich dann zwar nicht ganz leicht, aber dafür ist die erste Seite vielle­icht etwas über­sichtlich­er.

Möcht­en Sie den Ver­gle­ich auch von der zweit­en Liste zur ersten machen, dann ist das Vorge­hen iden­tisch. Eventuell wer­den sie die unge­filterten Ergeb­nisse auch in die jew­eils andere Arbeitsmappe kopieren, um in jedem Work­book den Überblick zu haben. – Im Anschluss ist es dann gewiss kein Prob­lem, die fehlen­den Dat­en an das Ende der entsprechen­den Liste zu kopieren.

▲ nach oben …

Vergleich mittels Power Query

Wenn sich die Liste mit den Ursprungs­dat­en ständig verän­dert / ver­größert, dann ist es vielle­icht müßig, immer wieder diesen Vor­gang durchzuführen. Vielle­icht ist es auch nicht ganz so schön, eine Hil­f­ss­palte in eine beste­hende Tabelle einzufü­gen. Hier bietet sich das Pow­er Query an, welch­es seit Ver­sion 2016 fest in Excel inte­gri­ert ist. Beim ersten Mal ist der Aufwand vielle­icht etwas höher, spätestens aber dann, wenn sie nach ein­er Änderung in den Quell­dat­en die Auswer­tung erneut durch­führen wollen, wer­den sie den Vorteil von PQ zu schätzen wis­sen.  💡 

Nicht in Tabelle_(2)

Um eine „klare Lin­ie” zu schaf­fen, schließen Sie diese Mappe ohne sie zu spe­ich­ern oder spe­ich­ern Sie das Work­book unter einem anderen Namen, damit das Orig­i­nal für weit­ere Übun­gen unverän­dert bleibt. Öff­nen Sie nun bitte wiederum diese Datei. Es ist die unverän­derte Aus­gangslage der eben ver­wen­de­ten Arbeitsmappe. Sie haben hier also die gle­ichen Voraus­set­zun­gen, wie zu Beginn der ersten Lösung mit der ZÄHLENWENN-Funk­tion. Markieren Sie hier eine beliebige Zelle in den Dat­en der Spalte A, aktivieren die Pow­er Query-Funk­tion­al­ität und wählen dann den Punkt Aus Tabelle bzw. Von Tabelle. Acht­en Sie auch hier darauf, dass bei dem vorgeschla­ge­nen Bere­ich für die Dat­en das Kästchen Tabelle hat Über­schriften mit einem Häkchen verse­hen ist.

Nach einem OK öffnet sich der Abfrage-Edi­tor und die Namen der Kun­den sind alle in der einzi­gen Spalte vorhan­den. Geben Sie im recht­en Seit­en­fen­ster der Tabelle einen „sprechen­den” Namen, beispiel­sweise qry_Kunden 1. Ich ver­wende das Prä­fix qry_ immer zur Ken­ntlich­machung eine Abfrage (Query), für Tabellen ver­wende ich immer tbl_ als Prä­fix. Anschließend ein Klick auf das Sym­bol Schließen & laden. Umge­hend wird in einem getren­nten Tabel­len­blatt eine neue Liste mit den Dat­en der Abfrage erstellt. Dass diese Tabelle iden­tisch mit den Dat­en der ursprünglichen Tabelle sind, ist hier nicht rel­e­vant. Wichtig ist nur der Bezug zur Abfrage. – Wenn Sie mehr Erfahrung mit PQ gesam­melt haben, wer­den Sie solch eine Abfrage vielle­icht nur als Verknüp­fung her­stellen, ohne sie in ein­er physis­chen Tabelle zu spe­ich­ern.

Wech­seln sie wieder zum Blatt Tabelle1 und erstellen sie auf gle­ichem Wege eine neue Abfrage mit den Dat­en der Spalte D. Geben Sie in dieser Abfrage im Edi­tor den Namen qry_Kunden 2. In den meis­ten Fällen ist links des Edi­tors derzeit kein Seit­en­fen­ster zu sehen, nur ein schmaler Balken mit der Beze­ich­nung Abfra­gen und einem nach rechts zeigen­den Pfeil >. Klick­en Sie entwed­er auf den Text oder den Pfeil und es wird sich auch links ein Seit­en­fen­ster öff­nen, wo sie bei­de existieren­den Abfra­gen sehen:

Bei­de Abfra­gen im linken Seit­en­fen­ster

Klick­en Sie dort auf die Abfrage qry_Kunden 1 und diese wird zur aktuellen Abfrage im Query Edi­tor. Wählen Sie nun im Reg­is­ter Start den Punkt Kom­binieren und im Drop­down-Menü die Auswahl Abfra­gen zusam­men­führen. Das Ganze stellt sich nun so dar:

Der erste Schritt beim Zusam­men­führen

Sie erken­nen in der oberen Hälfte die ersten Namen der Kun­den aus der Quellta­belle. In dem Textfeld darunter Klick­en Sie rechts auf den Drop­down-Pfeil und wählen durch Klick die zweite Abfrage qry_Kunden 2. Nun markieren Sie im oberen sowie im unteren Teil die jew­eils einzige Spalte. Unter­halb dieser bei­den Fen­ster kön­nen Sie nun noch die Join-Art definieren. Wählen Sie hier den Vor­let­zten Punkt Link­er Anti Join (Zeilen nur in erster):

Der zweite Schritt beim Zusam­men­führen

… und bestäti­gen Sie nun mit der Schalt­fläche OK. Umge­hend wer­den Ihnen in der Abfrage die 4 in Tabelle_2 fehlen­den Kun­den­na­men aufge­lis­tet:

Die gefilterten Unter­schiede der zweit­en Spalte

Nochmals zur Klarstel­lung: Link­er Anti-Join (Zeilen nur in erster) bedeutet, dass all jene Zeilen (Namen) aufgezeigt wer­den, die nur in der ersten (sprich oben angezeigten) Datei vorhan­den sind und in der zweit­en (unteren) fehlen.

Die zweite Spalte der Ergeb­nis-Abfrage mit dem -Sym­bol brauchen Sie in diesem Fall nicht, löschen Sie diese ein­fach. Schließen & laden und diese Tabelle wird auch in ein neues Tabel­len­blatt geschrieben. In einem weit­eren Arbeits­blatt wird auch automa­tisch diese zweite Abfrage geschrieben. Das Arbeits­blatt mit den 46 Namen kön­nen Sie auch löschen, es wird ‑im Gegen­satz zu den 4 gefilterten Namen- nicht mehr gebraucht. Was vielle­icht noch sin­nvoll oder hil­fre­ich wäre: Passen Sie die Über­schrift der Liste mit den in Tabelle_2 fehlen­den Kun­den so an, dass klar ersichtlich ist, was diese Namen darstellen. Und das machen Sie ide­al­er­weise in dem Query-Edi­tor, denn son­st wird bei der näch­sten Aktu­al­isierung wieder die Über­schrift aus dem Edi­tor in die erstellte Tabelle über­nom­men, was Sie ja gewiss nicht wollen.

Aus Train­ings-Grün­den soll­ten Sie auch noch ein­mal auf die Dat­en im zweit­en Arbeits­blatt zugreifen und diese zum Ver­gle­ich ver­wen­den. Sie sollen also all jene Namen her­aus­find­en, die zwar in Tabelle_2 nicht jedoch in Tabelle_1 aufge­führt sind. – Wenn Sie mögen, üben Sie noch ein wenig. In jedem Fall wer­den Sie diese Mappe vor der kom­menden Übung schließen. Ob Sie spe­ich­ern, bleibt Ihnen über­lassen.

▲ nach oben …

Gegenseitiger Abgleich

Nun ist es ja nicht immer so, dass in ein­er Liste alle Namen vorhan­den sind und in ein­er weit­eren Auf­stel­lung nur die fehlen­den aufgezeigt wer­den sollen. Es gibt dur­chaus auch Sit­u­a­tio­nen, wo die zweite Liste Dat­en enthält, welche in die erste über­tra­gen wer­den müssen. Anders aus­ge­drückt: Die Tabelle_1 soll um all jene Namen ergänzt wer­den, die auss­chließlich in der Tabelle_2 vorhan­den sind. Naturgemäß ist es meist extrem schwierig, in großen Datenbestän­den einen Daten­ab­gle­ich in angemessen­er Zeit vorzunehmen.

In der Real­ität hat ja jed­er Kunde auch eine Kun­den­num­mer, welche ein ein­deutiges Merk­mal darstellt. In diesen Dateien sind (nach dem ent­pack­en) die gle­ichen Kun­den (und einige wenige Ergänzun­gen) mit ihren Kun­den­num­mern erfasst. Sie merken vielle­icht schon an der Wort­wahl, dass es sich hier um zwei Arbeitsmap­pen han­delt; damit haben Sie Gele­gen­heit, auch diese Sit­u­a­tion ein­mal zu trainieren. Als weit­ere Erschw­er­nis kommt dazu, dass auch Unter­schiede gefun­den wer­den sollen, wenn die Groß- / Klein­schrei­bung unter­schiedlich ist. Also: Hugo von Hur­tig ist nicht gle­ich Hugo Von Hur­tig und wird entsprechend auch nicht als gle­ich­er Daten­satz, son­dern als Unter­schied gefun­den wer­den (solange die Kun­den­num­mer nicht iden­tisch ist und zum Ver­gle­ich genutzt wird). Und genau das ist ein weit­eres schla­gen­des Argu­ment für Pow­er Query.

Da in diesem Fall eine geöffnete Tabelle mit ein­er Liste ver­glichen wer­den soll, die nicht ein Teil der aktuellen Mappe ist, wird ein ander­er Weg zur Inte­gra­tion der Dat­en in den Query Edi­tor beschrit­ten. Aber step bei step, von Beginn an. Die oben aufgeführte/verlinkte Datei ist ja vom Typ *.zip, sie muss also von Ihnen erst ein­mal ent­packt wer­den. Sie haben jet­zt 2 Files zur Ver­fü­gung:
Spalten_vergleichen_Liste_ 2a.xlsx und Spalten_vergleichen_Liste_ 2b.xlsx

Öff­nen Sie zu Beginn die erst­ge­nan­nte Datei (2a). Erstellen Sie mit­tels der Pow­er Query Funk­tion­al­ität eine Abfrage mit diesen Dat­en. Natür­lich Bear­beit­en und danach spe­ich­ern Sie gle­ich die Query, ohne Änderun­gen vorzunehmen per Schließen & laden.

Um die Dat­en der zweit­en, aus dieser Sicht der exter­nen Datei zu erlan­gen, wech­seln Sie erst ein­mal wieder zum Arbeits­blatt Tabelle1. Mit­tels Pow­er Query wählen in Ver­sio­nen ²⁰¹⁰⁄₁₃ direkt den Punkt Aus Datei, begin­nend mit Ver­sion 2016 im Menü Dat­en den Punkt Neue Abfrage und dort Aus Datei. Im näch­sten Schritt wählen Sie Excel-Datei bzw. Aus Arbeitsmappe. Die fol­gen­den Schritte sind selb­sterk­lärend; Sie suchen und markieren die entsprechende Datei (2b) im File-Dia­log, Klick­en auf Importieren und wählen natür­lich das Blatt Tabelle1 und im Nav­i­ga­tor laden Sie diese Auswahl über die Bear­beit­en – Schalt­fläche. Umge­hend wird auch von diesen Dat­en eine weit­ere Abfrage erstellt. Diese Query ist nun auch in der Mappe, von wo aus die zweite Datei importiert wor­den ist.

Erweit­ern Sie nun das linke Seit­en­fen­ster so, dass die bei­den Abfra­gen dort sicht­bar sind. Zunächst soll­ten Sie die bei­den Namen der Abfra­gen ändern. Dazu kön­nen Sie auch im linken Seit­en­fen­ster auf einen Ein­trag Klick­en, F2 und dann den jew­eils neuen Namen schreiben. Mein Vorschlag für die bei­den Beze­ich­n­er: qry_Kundendaten (1) und entsprechend qry_Kundendaten (2). Aktivieren Sie nun die erste der bei­den Abfra­gen durch einen Klick in linken Seit­en­fen­ster auf diesen Ein­trag.

Wenn Sie die höhere Flex­i­bil­ität bewahren wollen, wählen Sie nun im Menü Start | Abfrage | Ver­wal­ten den Punkt Duplizieren. Dadurch wird eine Kopie der aktuellen Abfrage erstellt. Hier­aus soll ja der Extrakt jen­er Daten­sätze erstellt wer­den, die zwar in der exter­nen Datei nicht jedoch in der Haupt-Datei (Kun­den­dat­en (1)) vorhan­den oder vielle­icht anders geschrieben sind. Darum benen­nen Sie diese duplizierte Abfrage in qry_Kundendaten (1)_err um. „err” ste­ht übri­gens für error, Fehler.

Die fol­gen­den Schritte ken­nen Sie schon weitest­ge­hend: Im Reg­is­ter Start das Sym­bol Kom­binieren, im Unter­menü den Punkt Abfra­gen zusam­men­führen. Im neuen Dia­log wählen Sie als zweite Abfrage qry_Kundendaten (2). Obwohl die bei­den Über­schriften zu den Kun­den­na­men unter­schiedlich sind, markieren Sie in bei­den Abfra­gen die rechte der bei­den Spal­ten, also die mit den Kun­den­na­men. Es ist wahrschein­lich, dass jet­zt eine Sicher­heitsabfrage durch Pow­er Query erfol­gt:

Pow­er Query nimmt es mit der Daten­sicher­heit recht genau …

Lesen Sie sich die gerne aufmerk­sam durch, ich habe mich für diese Übung dafür entsch­ieden, die Option Öffentlich zu wählen und dann zu spe­ich­ern. Sit­u­a­tions­be­d­ingt werde ich mich bei geschäftlichen Dat­en gewiss zu ein­er adäquat­en Ein­stel­lung entschei­den.

Beim Join-Art wählen Sie hier Rechter Anti-Join, denn es sollen ja all jene Zeilen als Ergeb­nis der Abfrage gelis­tet wer­den, die nur in der zweit­en (unteren) Liste enthal­ten sind und nicht in der oberen. Anschließend OK. Ich muss geste­hen, dass mich dieses Ergeb­nis doch ziem­lich über­rascht hat:

Nicht gut, es soll­ten mehrere Zeilen sein …

Da ich sel­ber die bei­den Tabellen erstellt habe weiß ich, dass in dieser 2. Liste mehrere Namen enthal­ten sind, die in der ersten, der Hauptliste fehlen. Eine kurze Kon­trolle hat mir das dann auch bestätigt. – Was ist also schiefge­laufen? Die einzige gravierende Änderung gegenüber dem Vorge­hen in der let­zten Runde ist, dass wir dieses Mal den Recht­en Anti-Join und nicht den linken gewählt haben. Schließlich sollen ja nur die Daten­sätze her­aus­ge­filtert wer­den, die in der Haupt-Tabelle fehlen.

Manch­mal trügt aber auch der Schein. Klick­en Sie in der Über­schrift New­Col­umn auf das Erweit­ern-Sym­bol  und ent­fer­nen Sie im Dia­log nur das Häkchen bei Ursprünglichen Spal­tenna­men als Prä­fix ver­wen­den. Nach einem OK stellt sich das ganze doch schon etwas anders dar:

… es war doch der richtige Weg

Bin­go! Das war’s. Mit diesem Ergeb­nis bin ich zufrieden; zumin­d­est was die let­zten bei­den Spal­ten bet­rifft. Auch wenn sie gle­ich die ersten bei­den Spal­ten löschen wer­den, soll­ten Sie sich die Über­schriften merken. Denn sie wer­den diese bei­den Kopfzeilen gle­ich noch ein­mal in exakt dieser Schreib­weise brauchen. – Löschen Sie jet­zt die bei­den linken Spal­ten und ändern Sie die Über­schriften der bei­den nun verbliebe­nen Spal­ten entsprechend den eben gemerk­ten oder notierten Begrif­f­en. Acht­en Sie dabei bitte auch auf anhän­gende Leerze­ichen, die wür­den das Bild (und den Wert) ver­fälschen. Wenn Sie mögen, spe­ich­ern Sie auch diese Abfrage per Schließen & laden.

Ver­weilen Sie einen Moment in diesem Arbeits­blatt. Vielle­icht stellt sich ihnen die Frage, wie sie diese Dat­en, diese Tabelle im Arbeits­blatt auch später kor­rekt zuord­nen kön­nen. Das Ändern des Blatt-Reg­is­ter-Namens ist nicht unbe­d­ingt hil­fre­ich, denn die Liste (ein­schließlich der Über­schrift) kann prob­lem­los und ohne Ein­schränkung der Funk­tion­al­ität in ein anderes Blatt der Mappe ver­schoben wer­den. Allerd­ings gibt es einen ein­deuti­gen und unver­wech­sel­baren Para­me­ter: Im Menüband ist an erster Posi­tion der Tabel­len­name sicht­bar, wenn im Menü die Tabel­len­tools | Entwurf aktiviert sind:

Ein­deutige Iden­ti­fika­tion­s­möglichkeit der Tabelle

Dieser Name entspricht dem Namen der Abfrage. Eventuell ist aber ein Leerze­ichen durch einen Unter­strich erset­zt wor­den. – Das ist schon mal ein guter Anhalt­spunkt. Aber es geht noch kom­fort­abler: Öff­nen Sie die Abfrage noch ein­mal auf beliebige Weise. Klick­en Sie mit rechts in die Über­schrift Kun­den und wählen aus dem Kon­textmenü Umbe­nen­nen… Ich benenne diese Spalte nun um in Kun­den extern Error. Wiederum Schließen & laden und die Aus­sagekraft der Tabelle bzw. Abfrage ist in jedem Fall erhe­blich bess­er gegeben. 😉 

Hin­weis: Auch wenn es ungewöhn­lich scheint, dass in dieser Sit­u­a­tion die ersten bei­den Spal­ten der Zusam­men­führen-Abfrage keine Werte enthal­ten, ist das den­noch abso­lut kor­rekt. Der Grund dafür: Die Über­schriften der jew­eils verknüpften Spal­ten sind nicht iden­tisch. Wäre die Schreib­weise in jedem Punkt gle­ich, dann wür­den die Dat­en auch direkt in die 1. bei­den Spal­ten einge­fügt wer­den.

Wie heißt es doch so schön bei Wil­helm Busch und seinen wohl bekan­ntesten Fig­uren (Max und Moritz): „Dieses war der erste Stre­ich, und der zweite fol­gt sogle­ich”. Die Basis zum angle­ichen der 1. Datei (Haupt­datei) ist gelegt, aber es geht ja darum, einen Abgle­ich der bei­den Tabellen untere­inan­der zu erstellen. Und dazu muss natür­lich am Ende der Proze­dur der Stand bei­der Lis­ten iden­tisch sein. – Um die Unter­schiede aus der Sicht der exter­nen Datei darzustellen, öff­nen Sie nun die Abfrage qry_Kundendaten (2). Auch hier gehen Sie genau­so vor, wie bei der Erstel­lung der ersten Fehler-Abfrage. Also eventuell ein Dup­likat erstellen, Kom­binieren, Abfra­gen zusam­men­führen, die andere Datei in den unteren Bere­ich durch Auswahl ein­fü­gen und anschließend Rechter Anti-Join auswählen. Last but not least die ersten bei­den Spal­ten löschen. Und dann natür­lich vor dem Spe­ich­ern die 2. Spalte umbe­nen­nen: Kun­den intern Error. Jet­zt noch Schließen & laden und auch dieser Schritt ist geschafft.

In den bei­den neu erstell­ten Abfra­gen bzw. Tabellen ist ja nun klar aufge­führt, welche Dat­en in der jew­eils anderen Tabelle fehlen. Das ist zwar schön zu wis­sen, aber dadurch sind die jew­eili­gen Kun­den-Dat­en immer noch nicht auf dem aktuellen und gle­ichen Stand. Es ist also erforder­lich, die Orig­i­nal-Tabellen zu ergänzen.

Ein­er der möglichen Wege scheint zu ein­fach zu sein, um auch zum Ziel zu führen: Sie markieren jew­eils die Dat­en mit den fehlen­den Kun­den und fügen diese per Hand ein­fach in die Liste mit den Orig­i­nal-Dat­en ein. Und ich kann Ihnen sagen: Es ist wirk­lich so ein­fach. Der Zweck wird damit voll und ganz erfüllt. Der einzige Hak­en bei der Sache: Sie wer­den diesen Vor­gang jew­eils von Hand durch­führen müssen, wenn Sie einen Abgle­ich-Lauf gefahren haben. – Den­noch: Weit­er unten wird noch eine kleine Fehlerquelle disku­tiert.

Wenn Sie solch einen Abgle­ich in regelmäßi­gen Abstän­den vornehmen, dann soll­ten Sie das ergänzen der Dat­en auch Pow­er Query über­lassen. Begin­nen Sie den Vor­gang damit, dass sie die Abfrage qry_Kunden (1) beispiel­sweise durch Klick im linken Seit­en­fen­ster öff­nen. Auch hier geht der Weg über den Menüpunkt Kom­binieren. Allerd­ings wollen sie keine Abfra­gen zusam­men­führen son­dern sie wer­den an die beste­hende Query eine weit­ere Abfrage anfü­gen. Wählen Sie also diesem Punkt und wenn sie im Textfeld auf den Drop­down-Pfeil_ Klick­en, ist qry_Kunden (1)_err die richtige Wahl. Bestäti­gen Sie mit OK und es tut sich etwas, was Sie nicht unbe­d­ingt erwartet haben:

Die 3. Spalte ist ein uner­wartetes Ergeb­nis

Es wur­den zwar alle Daten­sätze der Abfrage mit den fehlen­den Werten ange­hängt, die Kun­den­num­mern ste­hen auch an der kor­rek­ten Posi­tion aber die Namen wur­den in eine dritte Spalte platziert. Was Sie auf den ersten Blick wahrschein­lich irri­tieren wird ist eigentlich ganz logisch: Pow­er Query kann nicht wis­sen, dass die Dat­en in der Spalte Kun­den vom gle­ichen Typus sind wie in die in der Spalte Kun­den extern Error. Im Grunde genom­men ist es das gle­iche Prinzip wie beim zusam­men­führen der Dat­en vorhin. Unter­schiedliche Über­schriften führen zu unter­schiedlichen Spal­ten.

Da dieses ja ganz offen­sichtlich ein klein­er Miss­griff war, Klick­en Sie im recht­en Seit­en­fen­ster bei Angewen­dete Schritte auf das Löschen-Sym­bol links des Textes Ange­fügte Abfrage  und dieser Schritt wird kom­plett rück­gängig gemacht. Wech­seln Sie nun zu Abfrage qry _Kunden (1)_err und ändern Sie die Über­schrift der zweit­en Spalte auf Kun­den. Ich weiß, das hat­ten Sie schon ein­mal dort ste­hen. Aber wegen der besseren Iden­ti­fizier­barkeit hat­ten Sie den kleinen Aus­flug unter­nom­men. 😉 Wenn Sie jet­zt noch ein­mal in dieser Abfrage auf dem gle­ichen Wege wie eben gehen wer­den sie rasch erken­nen, dass die fehlen­den Dat­en wie gewün­scht ein- bzw. ange­fügt wor­den sind. Dieses Vorge­hen wer­den sie äquiv­a­lent mit der zweit­en Kun­den-Abfrage durch­führen.

▲ nach oben …

Korrekturen doppelter Kundennummern

Fer­tig. Wirk­lich fer­tig? Nein! Es bleibt doch immer noch das Prob­lem mit beispiel­sweise Frau Irma Hauck. Sie erin­nern sich, dass sie ein­mal mit einem großen I und ein 2. Mal mit einem kleinen l als 1. Zeichen des Vor­na­mens gespe­ichert ist. Darum ist sie jet­zt in bei­den Tabellen auch zweifach in jew­eils der richti­gen und falschen Schreib­weise vorhan­den. Jet­zt kön­nten Sie natür­lich auf die prinzip­iell gute Idee kom­men, ein­fach bei der Kun­den­num­mer dop­pelte Daten­sätze ent­fer­nen zu lassen. Aber da stellt sich natür­lich die Frage, ob mit Sicher­heit jen­er Daten­satz ent­fer­nt wird, der fehler­haft ist. Hier in dieser kurzen Beispiel-Datei wis­sen Sie, dass nur dieser eine Daten­satz ein Aus­reißer ist und die anderen inte­ger sind. Aber das ist ja eigentlich die Aus­nahme. Oder gibt es vielle­icht doch noch mehr dieser Eigentlich-Dublet­ten? Wenn Sie auch nur den leis­es­ten Ver­dacht haben, dass Kun­den-Namen in ver­schiedene Schreib­weise aber mit gle­ich­er Kun­den­num­mer in der Liste vorhan­den sein kön­nten, dann ist ein recht kom­plex­er Umweg wohl die bessere Lösung:

• Öff­nen Sie erst ein­mal die Abfrage qry_Kundendaten (1), die jet­zt ja auch die einge­fügten Dat­en enthält. „Offiziell” wis­sen Sie ja gar nicht, ob dort dop­pelte Kun­den­num­mern enthal­ten sind oder nicht.
• Um festzustellen, ob über­haupt diese Dublet­ten vorhan­den sind und wenn ja wie viele, nutzen sie (natür­lich) auch Pow­er Query. Markieren Sie die Spalte KdNr. und wählen dann im Menüband Gruppe Trans­formieren den Punkt Grup­pieren nach.
• Die Vor­gaben im Dialogfen­ster sind schon wie gewün­scht: Grup­pieren nach: KdNr., Neuer Spal­tenname: Anzahl und als Vor­gang: Zeilen zählen.
• OK und es wird eine neue Abfrage mit 2 Spal­ten erstellt.
• Die Spalte Anzahl enthält jew­eils die Anzahl der Ein­träge für diese Kun­den­num­mer. Klick­en Sie auf den Drop­downpfeil  dieser Spalte und Sie erken­nen, dass es auch Kun­den­num­mern mit mehr als einem Ein­trag gibt.

Alleine diese Erken­nt­nis sollte schon aus­re­ichen, dass Sie den Kor­rek­turbe­darf erken­nen. Möcht­en Sie noch wis­sen, welche Kun­den­num­mern betrof­fen sind, ent­fer­nen Sie das Häkchen bei der 1 und danach OK. Sie sehen, dass es sog­ar 2 dop­pelte Kun­den­num­mern gibt.

Wie Sie jet­zt vorge­hen wollen, sei ihnen über­lassen. Diese 2 Zahlen würde ich mir auf­schreiben, bei ein­er größeren Anzahl dieser Abfrage spe­ich­ern und dann druck­en. Oder aber ich mache gar nichts und widme mich gle­ich der eigentlichen Abfrage im Tabel­len­blatt. Bei mir ist das Tabelle2, wenn sie Fehlver­suche zu ver­buchen haben, kann das bei Ihnen dur­chaus eine andere Reg­is­ter­beze­ich­nung des Arbeits­blattes es sein. Sie kön­nen sich aber gut am recht­en Seit­en­fen­ster ori­en­tieren. Wenn Sie das Arbeits­blatt anwählen und eine beliebige Zelle in der Liste markiert ist dann wird im recht­en Seit­en­fen­ster die entsprechende Abfrage markiert sein. Hier also qry_Kundendaten (1).

Sie kön­nten jet­zt diese bei­den Kun­den­num­mern (also die 4 Zeilen) ganz nor­mal fil­tern. Bei ein­er größeren Daten­menge ist es gewiss unpro­duk­tiv und fehler­an­fäl­lig. Da schlage ich fol­gen­den Weg vor:

• In die Zelle C1 trage ich eine Über­schrift ein, beispiel­sweise DubCheck.
• Klick in irgen­deine Zelle der Liste.
• Im Menü-Reg­is­ter ist der Punkt Tabel­len­tools | Entwurf aktiviert oder Sie machen das.
• Im Menüband, Gruppe Eigen­schaften markieren Sie den Punkt Tabel­len­größe ändern.
• Wenn der Laufrah­men um die bish­erige Tabelle sicht­bar ist, Shift→, um den Bere­ich zu erweit­ern. Bestä­ti­gung mit OK.
• Tra­gen Sie in C2 diese Formel ein: =ZÄHLENWENN(A:A;A2)
• Da die Formel dank der Intel­li­gen­ten Tabelle automa­tisch bis zur let­zten Zeile aus­ge­füllt wird, kön­nen Sie jet­zt sofort alle Zeilen fil­tern, die mehr als einen Ein­trag je Kun­den­num­mer haben.
• Sortieren Sie jet­zt nach der Kun­den­num­mer.
• Bei Hugo von Hur­tig ändern Sie einen der bei­den Daten­sätze auf die gewün­schte Schreib­weise; nor­maler­weise wird das „von” klein geschrieben. Aus Grün­den der Sicher­heit soll­ten Sie diesen geän­derten Ein­trag per copy/paste auch in die andere Zelle kopieren, denn es kön­nte ja sein, dass eine der Ein­träge ein ange­hängtes Leerze­ichen hat. Nur so ist gewährleis­tet, dass alle Zeilen dieser Kun­den­num­mer iden­tisch sind.
• Bei Frau Irma Hauck ist es mit der typ­is­chen Excel-Schrif­tart extrem schwierig, die kor­rek­te Schreib­weise zu erken­nen. Hier ist es sin­nvoll, ein Mal den Namen kom­plett neu zu schreiben und dann in die andere Zelle zu kopieren.
• Recht­sklick in eine beschriebene Zelle der Spalte C, Zeile/Spalte löschen | Tabel­lenspal­ten um diese Spalte kom­plett zu löschen.
• Ein Kon­troll­blick in das rechte Seit­en­fen­ster, es sind in der Abfrage qry_Kundendaten (1) 58 Zeilen geladen.
• Öff­nen Sie die Abfrage beispiel­sweise durch Dop­pelk­lick auf diesen Ein­trag im recht­en Seit­en­fen­ster.
• Die Spalte KdNr. ist bere­its markiert. Sym­bol Zeilen ver­ringern | Zeilen ent­fer­nen | Dup­likate ent­fer­nen.
• Wiederum ein Kon­troll­blick, dieses Mal in die Sta­tuszeile und erken­nen, dass nun nur noch 56 Zeilen in der Abfrage vorhan­den sind. 💡

Fer­tig. Wirk­lich? Ja, wirk­lich. Zu min­destens dann, wenn sie diese Abfrage per Schließen & laden spe­ich­ern und als Basis für weit­ere Auswer­tun­gen ver­wen­den. – Der Übung wegen kön­nen Sie die zweite der Tabellen gle­icher­maßen anpassen.

Vor­weg ange­merkt ein typ­is­ch­er Aus­druck aus der Kom­mu­nika­tion in Foren oder Chats: „Bitte nicht schla­gen!”. Wenn Sie das alles noch ein­mal Revue passieren lassen wird Ihnen vielle­icht auf­fall­en, dass sie prinzip­iell die dop­pelte Arbeit gemacht haben. Das Ziel war ja, dass bei­de Tabellen vom Inhalt her iden­tisch sein sollen. Es würde also reichen, die Haupt­Tabelle mit allen fehlen­den Dat­en zu „bestück­en” und dann die Dat­en von Hand in die andere Tabelle zu kopieren. Damit ist ja zu 100% gewährleis­tet, dass der Inhalt bei­der Tabellen iden­tisch ist. Aber bedenken Sie bitte eines: Ein Mal diese ver­meintliche Dop­pelar­beit bedeutet aber auch, dass sie beim näch­sten Abgle­ich nur noch auf Aktu­al­isieren Klick­en müssen um mit einem Schlag bei­de Dateien, als auch die in der Tochter-Datei zu aktu­al­isieren.

Ich kön­nte jet­zt noch einen hal­ben Roman dazu schreiben, was da alles noch mach­bar ist. Ich will Ihnen aber nicht die Freude am Exper­i­ment mit Pow­er Query nehmen und auch unserem Spon­sor GMG-Com­put­er Con­sult­ing die Chance lassen, Ihnen ziel­gerichtet zu helfen.

▲ nach oben …