Office 365: Excel mit abgleich zweier Datenbanken überfordert

[Ergil]

Guten Tag zusammen,

ich bin mir nicht sicher, ob mir überhaupt jemand bei diesem Problem helfen kann, aber ich dachte mir, einen Versuch ist es Wert:

Ich habe zwei Dateien, eine erstelle ich über unser hauseigenes CRM-System, es ist also kein gängiges wie SAP oder Dynamics NAV/AX.
Die andere Datei erhalte ich einem der unzähligen Zulieferern und die kann immer anders aussehen.
In beiden Fällen handelt sich dabei um Kundenbestandsdaten.

Nun müsste ich abgleichen, welche Daten bereits in unserem System sind und welche nicht. Nach Möglichkeit, mit Excel. Wenn jemand ein besseres Tool kennt, gerne her damit, denn:
Excel stößt hier an seine Grenzen:

Ich habe ein kleines Abgleich-Makro. Ursprünglich hatte das Makro aber nur die Aufgabe kleinere Listen von rund 500-5.000 Datensätze zu durchsuchen. Das dauerte dann je nach Größe der beiden Vergleichsdateien bis zu 45 Minuten. Da hierbei jedoch das Ergebnis wichtiger war, als die aufgewendete Zeit, war dies völlig in Ordnung.
Ich habe dieses Makro zweckentfremdet, um nun die beiden Bestandsdateien zu vergleichen. Leider sind das rund >80.000(!) Datensätze je Datei. Ich musste schnell erkennen, dass dieses Makro wohl kaum für diesen Abgleich geeignet ist. Zumal die Kundendaten bereits nach relevanten Daten gefiltert sind. Würde ich versuchen, alle unsere Kundendaten in eine Exceltabelle zu packen, würde das nicht einmal mehr in eine Excel-Tabelle passen…
 
Jetzt suche ich nach einer gangbaren Möglichkeit, diese Dateien trotzdem zu vergleichen.
Da unter anderem einer Art Kundennummer vorhanden ist, dachte ich, könnte ich es mit einem einfachen SVERWEIS lösen.

Leider ist auch das bei dieser Datenmenge ein sehr langsames unterfangen, zumal die Nummern, wie sie vom Zulieferer kommen und unsere Nummern, wie sie im System zu finden sind, voneinander abweichen können. Um die Daten aufeinander anzupassen brauche ich jedoch Excelfunktionen, welche offenbar für den Einsatz bei so einer großen Tabelle niemals ausgelegt waren. Z.B. erkennt Excel manche Nummern als Text, auch, dann wenn sie rein nummerisch sind. Das wäre so auch in Ordnung, um z.B. führende Nullen beizubehalten o.ä. ist aber für den Abgleich bestenfalls zweitrangig. Die betroffenen Nummern sind, wie üblich durch ein kleines grünes Häkchen in der oberen linken Ecke der jeweiligen Nummer, markiert.
Filter ich diese nun heraus und markiere die Nummern, erscheint ein kleines Ausrufezeichen. Durch klick darauf komm ich wiederrum in ein Kontextmenü, welches mir das Formatieren als Zahl erlaubt. Die herkömmliche variante mit "Zelle formatieren..." hat übrigens keinen Einfluss auf diese Art der Formatierung...

Und genau hier hapert es: Von den 80.000 Datensätzen sind womöglich rund 75.000 betroffen und müssen von dieser Funktion durchgerechnet werden. Dies dauert jetzt schon so um die 6 Stunden. Der Taskmanager meldet, dass Excel inaktiv ist. Das kommt auch beim Makro schonmal vor, aber solange braucht der üblicherweise nicht und irgendwann fängt sich das wieder. Nur soviel Zeit habe ich dafür leider nicht zumal ich den fortschritt auch nicht einschätzen kann.

In der Ausgangslage sind gegenwärtig in keiner der beiden Tabellen irgendwelche Verweise oder andere Formeln enthalten, dennoch scheint Excel mit der Berechnung überfordert.

Versuche ich einen SVERWEIS ohne die Daten entsprechend zu formatieren, treffe ich nur rund 3.000 Datensätze. Stichprobem zeigen aber: die Nummern sind teilweise komplett identisch! Der Weg ist also der richtige, die Anzahl Datensätze und die unterschiedliche Formatierung dieser, macht mir aber einen Strich durch die Rechnung!

Kennt jemand einen gangbaren Weg, wie man solche Datenmengen miteinander vergleichen kann?

Hinweis hierzu: ich habe kein Problem damit, immer wieder händisch zu kontrollieren ob es etwaige Abweichungen in der schreibweise der Nummern gibt. Bevor es das Makro gab, habe ich den Abgleich mit mehreren Sverweisen durchgeführt. Bsp.:

Gesuchte Nr. = K-4711
Nummer in der Zulieferertabelle = 4711
Durch löschen bzw. hinzufügen von „K-„ in einer der beiden Tabellen, konnte ich also per Sverweis treffen, konnte ich das nicht, stand da womöglich „B-4711“ oder „04711“. Auch unterschiedliche Nummernkreise innerhalb einer Zuliefererdatei sind möglich, sodass das der einzige weg ist.

Das Makro entfernt nur die Sonderzeichen und sucht dann vergleichbare Werte, das Problem mit den unterschiedlichen Buchstaben vor der Nummer würde aber auch das Makro überfordern und müssten daher ohnehin manuell nachgezogen werden.

Eine Beispieldatei würde leider auch nichts bringen, ist halt so eine typische Kundendatei:
Kundenummer | Vertragsnummer | Vorname | Nachname | Geburtsdatum | Adresse … usw.

Ich hoffe ihr habt da eine Idee, wie das trotzdem möglich ist.

Vielen Dank schonmal!

MfG

Ergil

[gmg-cc]

Moin Ergil
mache dich einfach einmal mit Power Query aka Daten | Abrufen und transformieren vertraut. Da sind die paar Daten (ja, ich meine das ernst!) recht gut zu verarbeiten. Du wirst dich aber einarbeiten müssen ... 

[Ergil]

Oh ok, ich habe von diesem Tool schonmal gelesen, aber keine Ahnung was das eigentlich ist. Gibt es irgendwelche How-To's oder Wissensbibliotheken wo ich mich einarbeiten kann?

[gmg-cc]

Nun ja,
Mr. Goo und Verwandte sind da recht auskunftsfreudig. Hier könntest du das eine oder andere finden: http://www.excel-ist-sexy.de/kategorie/power-query/

[Ergil]

Hallo nochmal,

hat sich erledigt...

Ursprüngliche Nachricht: (Edit beachten)
ich hab mir das Tool gestern mal angeschaut und heute probiere ich mich darin aus.
Mir ist direkt ein Problem entgegen gekommen:

Wenn ich z.B. das Geburtsdatum des Kunden importiere, zeigt er mir dies im Power Query zunächst als Dezimalzahl an. Wenn ich diese Dezimalzahl nun umwandeln will in ein Datumsformat schreibt mir PQ "Error" in die Zellen. Die Quell .CSV hingegen hat sogar eine korrekte Darstellung des Geburtsdatums.

Jemand ne idee wie ich das verhindern kann?

MfG

Ergil

Edit:

Keine Ahnung wieso, aber nachdem ich die Formatierungseistellung in PQ wieder heraus genommen habe, hat er die Geburtsdatumsspalte nicht mehr in Dezimal sondern im Datumsformat korrekt angezeigt... seltsam...

[gmg-cc]

Moin,
ohne Muster-CSV ist das nur ein Rätselraten ...

[Ergil]

Hallo zusammen,

ich wollte nur nochmal ein Feedback geben:

Die Aufgaben lassen sich tatsächlich (teilweise) mit Power Query lösen!

Leider funktioniert aber auch nicht alles einwandfrei:

- Die Formatierung die ich in PQ Festlege (Zelleninhalt als  Text, Zahl, Dezimal etc.) wird teilweise trotzdem nicht in die Daten übernommen, wodurch ich in den zwei Datenbanken die ich abgleichen will, immernoch 2 verschiedene Formate habe, somit treff ich da mit einem SVERWEIS immernoch nix... Woran das liegt weiß ich nicht, wird wohl irgendeine Krux sein die beim erstellen der jeweiligen .CSV/.XLS entstanden ist.

- Den SVERWEIS zu ersetzen und die eindeutigen Daten Verknüpfen ist ne tolle Sache. Dadurch kann ich Daten nebeneinander Darstellen, die in beiden Datenbanken identisch sind. Leider hilft mir das nicht, wenn es geringfügige (oder auch gravierende) Unterschiede im Aufbau der Vertragsnummern gibt: ich treffe auch damit nix

- Inkonstinenzen in den Datenbanken (können gerade bei so großen Datenmengen ja mal auftauchen) sorgen zudem dafür, dass Excel eine Fehlermeldung auswirft und ich teilweise nicht mehr mit der Tabelle arbeiten kann,  indem der Fehler aufgetreten ist. Sehr schade, dass man dann nur einen (wenn auch unterhaltsamen) Button "Stirnrunzeln senden" zur auswahl bekommt und nicht etwa "Fehler Ignorieren" oder sowas...

Alles in allem fühlt es sich etwas umständlich an, ich Frage mich, ob ich nicht einfach mit der Funktion "intelligente Tabellen" das gleiche Ergebnis erzielt hätte, denn nach dem Import über PQ sind die Datenbanken in einer solchen.

Testdaten zur Verfügung stellen geht aus zwei Gründen nicht:

1. sensible Kundendaten / DSGVO und so
2. Wenn ich die Tabellen versuche zu kopieren und willkürlich fiktive Werte eingebe, dann formatiert er die Werte korrekt. Das Problem wird also beim Export aus dem jeweiligen System generiert. 80.000 Vertragsnummern händisch eingeben, ist aber keine gangbare Lösung um den Fehler zu beheben

Trotzdem Vielen Dank!

Mfg Ergil

[gmg-cc]

a) zu dem hier nicht zitierten Text kann ich nichts sagen ...

Testdaten zur Verfügung stellen geht aus zwei Gründen nicht:

1. sensible Kundendaten / DSGVO und so
2. Wenn ich die Tabellen versuche zu kopieren und willkürlich fiktive Werte eingebe, dann formatiert er die Werte korrekt. Das Problem wird also beim Export aus dem jeweiligen System generiert. 80.000 Vertragsnummern händisch eingeben, ist aber keine gangbare Lösung um den Fehler zu beheben

zu 1. Richtig, auch ohne die bescheuerte CSGVO
zu 2. Es reichen gewiss 20-30 Zeilen CSV. Und die haben per se keine Formatierung ...Darum kannst du derartige Daten durchaus anonymisieren. Meine Empfehlung: Wend dich an einen Dienstleister, der dir eine Vertraulichkeitserklärung (NDA) unterschreibt, der sollte in der Lage sein, das Problem zu beheben. Und höchstwahrscheinlich auch mit Power Query. - Ich habe für mich dieses Thema abgeschlossen.