Office: Duplikate über mehrere Spalten

\@ Helmut (Oge)
Das mit den Spatzen war ein treffender Vergleich. Fliegen die doch so gerne in den Wolken (Cloud). Und da es UNIQUE/EINDEUTIG als Funktion erst ab dem (imho) mit Cloud-Zwang behafteten Excel 365 gibt, kommt mit das nicht auf die Platte/SSD. Auch wenn ich traurig bin, dass ich so manch schöne neue Funktion nicht nutzen kann. (Und ja, für "special customers" habe ich noch einen Rechner mit O 365…)

Ich habe gerade von einem Kunden gehört, dass bei uns in Schleswig-Holstein Lehrern untersagt ist, Office 365 auf privaten Rechnern zu nutzen, wenn auf dem Rechner in irgendeiner Form schulische Daten verarbeitet werden. Insbesodere under dem Aspekt, dass MS die verbilligte EDU-Soft nur noch aus der 365-Generation anbietet ist das schon bedeutsam.

 

Hallo Steyr,

Zuerst einmal, um dich zu entwirren, die Mappe vom Drogist zeigt das Ergebnis einer Power Query Abfrage. Die Abfrage selbst, hat er aber aus der Mappe entfernt, bevor er diese hochgeladen hat.

Ob dabei alle "nice to have" berücksichtigt sind, wage ich zu bezweifeln, denn du hast ja den Wunsch geäussert, definieren zu können, welche Spalten bei dem Dublettencheck berücksichtigt werden sollen.

Diese Anforderung wird sich vermutlich auch nicht, in die Formellösung von Oge integrieren lassen, auch wenn diese sehr genial ist.

Ich habe nun meine Erste hochgeladene Lösung erweitert, damit nun auch die zu prüfenden Spalten vorgegeben werden können.

Hier einige Erläuterungen:

Im Tabellenblatt "Prüfspalten" pflegst du die Spaltentitel, die beim Dublettencheck berücksichtigt werden sollen.

Das Tabellenblatt "Dubletten in Prüfspalten", enthält das Ergebnis einer Power Query Abfrage, die auch in der Mappe enthalten ist. Über "Daten > Abfragen und Verbindungen" kommst du zu der Abfrage, die du dir auch anschauen kannst.

Immer wenn du in "Prüfspalten" oder der Datentabelle "BF über PQ-Result" änderungen vorgenommen hast, musst du die PQ-Abfrage aktualisieren (Cursor irgendwo in der Ergebnistabelle platzieren > rechte Maustaste > Aktualisieren).

Das Ergebnis siehst du dann im Tabellenblatt "BF über PQ-Result". In Zeile 3 sind die Spalten markiert, die bei der Prüfung berücksichtigt wurden. In der Datenliste sind dann alle Dubletten markiert, die mindestens einmal in jeder Prüfspalte enthalten sind. Damit es etwas übersichtlicher wird, habe ich die bedingten Formatierungen 6 Mal mit unterschiedlichen Farben definiert.

Der Power Query Abfrage ist es dabei völlig egal, wie viel Spalten deine Tabelle enthält und wie viele davon bei der Dublettenprüfung berücksichtigt werden sollen.

Nur da sich die "Wird angewendet auf-Bereiche" der bedingten Formatierungen, beim kopieren von Daten, immer von alleine anpassen und sich dabei total zerfleddern, musst du diese immer wieder prüfen und ggf. anpassen.

Da dies jedoch meist sehr mühsam ist, habe ich noch das Tabellenblatt "BF-Vorlage" angelegt, das alle Formatierungen im Originalzustand enthält. Somit kannst du die Formatierungen mittels des Format-Pinsels von hier auf das Tabellenblatt "BF über PQ-Result" übertragen. Dies ist wesentlich einfacher, als die BF immer wieder manuell anzupassen.

Somit kannst du auch imTabellenblatt "BF-Vorlage" die Farbauswahl erweitern und diese dann mit dem Format-Pinsel auf "BF über PQ-Result" übertragen. Bei meinen Tests haben die Formatübetragungen ohne Probleme funktioniert.

 

Hallo WS,

zu

Doch, das geht
1. mit einer einfachen Multiplikation mit einem Vektor in dem die zu berücksichtigenden Spalten eine 1 eingetragen haben und
2. dem Ersetzen der Püfung des Produktes ("Produkt(...)>0"=alle Werte sind 0) durch eine Prüfung der Summe ("Summe(...)=x" = x Werte haben eine 1).

ps.
In deiner Lösung vermisse ich den Eintrag "1". Das kann auch daran liegen dass du die Daten nach der letzten Aktualisierung nochmals bearbeitet hast. Ich kann sie nicht aktualisieren, da sie externe Daten benötigt.

 

Das ist "S-H-isch" zu kurz gesprungen. "Windows 10" reicht schon aus, möglicherweise sogar noch geringere Anforderungen (wer garantiert mir, dass bei einem Update von WIN 7 oder 8 nicht auch "Automatismen" den PC erreicht haben?).

Passt nicht ganz zu folgendem Zusammenhang, aber sollte im Kontext betrachtet werden können:

https://www.clever-excel-forum.de/Th...7793#pid207793

 

Hallo Helmut,

zum Glück hatte ich "vermutlich" geschrieben!

Nicht nur, dass deine Seminarbuchungssoftware immer besser wird, nun fängst du auch an geniale Formeln zu entwickeln, die bisher sehr oft von Lupo kamen.

Wobei ich im Moment nicht in der Lage bin, diese Formeln zu verstehen. Hier habe ich Defizite, die ich wömöglich nie mehr beseitigen kann.

Nachdem ich die Letzte hochgeladenen Mappe an meine Firmen-Emailadresse gesendet habe, kann ich die von dir beschriebenen Probleme nachvollziehen, wobei mir nicht klar ist, woher die externen Verknüpfungen kommen, da ich bewusst keine eingefügt hatte. Allerdings hatte ich zeitgleich andere Mappen mit externen Beziehungen geöffnet. Ich werde die Mappe nun noch einmal neu aufbauen und dann zuerst auf meinem Firmen-Laptop auf externe Verknüpfungen testen.

Mit diesem Hinweis kann ich leider nichts anfangen. Kannst du mir bitte genauer erläutern, wo du den Eintrag "1" vermisst?

 

Das ist alles schon etwas merkwürdig. Nach dem dritten oder vierten Mal speichern, auf meinem Firmenlaptop, kommt nun auf einmal kein Hinweis bezüglich der externen Datenverbindung mehr.

Aber es gab zwischenzeitlich andere Probleme. Wir haben in der Firma zwar auch O365 im Einsatz (Aktuell Version 1809 Build 11929.20838) aber auf meinem privaten Rechner bin ich schon bei Version 2008 (Build 13127.20296).

Auf meinem Firmenlaptop wurden mir wohl deshalb auch die Merge-Befehle sowie die Index-Funktion als Fehlerhaft angezeigt. Erst nachdem ich jeweils vor der fehlerhaft angezeigten Funktion diese neu definiert und dann die nachfolgende gelöscht hatte, ist die Abfrage dann auch auf meinem Firmenlaptop problemlos gelaufen.

Und vorher im Sicherheitscenter hatte er wohl den lokalen Speicherpfad (Laufwerk C*Smilie als die externe Datenverbindung ausgewiesen.

Sende ich mir nun die Firmenkopie an mich und öffne diese, erhalte ich wieder den Hinweis, dass externe Datenverbindungen deaktiviert wurden, die aber wiederum nur auf mein C-Laufwerk verweisen.

Sichere ich die Mappe vom FirmenLaptop auf USB und öffne diese dann auf meinem PC vom USB-Laufwrerk H: dann vberweist die exerne Verbindung auf das Laufwerk H.

Aber zumindest muss ich nun die PQ-Abfrage nicht mehr nachbearbeiten, um diese lauffähig zu bekommen.

Deshalb nun auch mal meine Version vom USB-Stick, die nach Aktivierung der von mir nicht auffindbaren "externen Datenverbindung", problemlos läuft. Und nachdem ich diese Mappe, ohne zu sichern, geschlossen und erneut geöffnet habe, kommt nun die Warnmeldung wegen der externen Datenverbindungen nicht mehr.

Vielleicht ist ja jemand in der Lage, herauszufinden, was die Ursache für die Meldung ist.

 

\@Drogist, deine Lösung (Duplikate über mehrere Spalten_V2_Ergebnis.xlsx ) scheint schlicht und einfach zu sein, habe mich aber noch nicht näher damit beschäftig, was die funktion bzgl. meines Problems angeht

@WS
Deine Lösung (mof - Duplikate über mehrere Spalten (PQ) )sieht sehr elegant aus,

-das mit den externen Daten hat sich gelößt, nachdem ich kurzerhand die neueste Excel Version geladen habe.

-weiterhin überprüfe ich deine Datei noch auf ihre Funktion. sobald ich mehr weiß, gebe ich ein Feedback

Vielen Dank! *Smilie

 

So, endlich kommt mein Feedback!

@WS-53, vielen herzlichen Dank für deine Hilfe, mögen dein Karma dich weiter bringen *hands *redface.gif*

Die excel liste funktioniert genau wie erhofft. einfach tadellos

Ich verstehe allerdings nicht, wozu das Tabellenblatt "BF-vorlage" gut ist.

Werde das Thema als erledigt markieren. *tongue.gif*

 

Hi Steyr,

vielen Dank für die Rückmeldung.

Das Tabellenblatt BF ist nicht notwendig, kann aber hilfreich sein. Durch diverse Aktionen, scheinbar teilweise willkürlich, werden bei bedingten Formatierungen, die Zellbereiche "wird angewendet auf" total zerfleddert und es werden auch viele neue gebildet. Dies passiert auch gerne bei formatierten Tabellen.

Aus 6 BF, mit jeweils einem Zellbereich, können so, mit der Zeit hunderte zusätzliche entstehen und die ursprünglichen sind dann für eine endlos lange Liste von kleinen Zellbereichen gültig.

Diese dann zu bereinigen kann sehr mühsam sein. Aber wenn dir das Passiert ist, dann kannst du einfach über den Formatpinsel, die Original von BF übernehmen. Dies ist viel einfacher.

In einem Abteilungskalender, in dem dies immer wieder passiert ist, werden nun alle BF beim sichern per Makro neu gesetzt.

 

Ich bin es nochmal *tongue.gif*

@WS

Nach einer längeren Testphase musste ich leider 2 Bugs feststellen:

1. (ist an sich nicht schlimm) die verschiedenen Paare werden nicht farbig markiert

2. Nicht alle Paare werden aufgefunden. Die in dieser Nachricht hochgeladenen Beispieltabelle findet sich in Spalte 1 und 2 von tabellenblatt "BF über PQ-result" ganz oben die Zahl "37".

In dem Tabellenblatt "Dubletten in Prüfspalten" taucht diese Zahl aber nirgends auf.

Manchmal stellte ich auch fest, dass wenn ich in die Abfrage im Tabellenblatt "Prüfspalten" weitere Spalten (3, 4, 5, 6,) hinzufüge, dass aufeinmal noch mehr dublikate auftauchen. Das kann ja logisch gesehen nicht sein, es dürften höchstens weniger Dublikate werden...

Kann das irgendwer da draußen logisch nachvollziehen??

Liebe Grüße,
steyr*winken

 

Hallo Steyr,

da gab esmehrere Problem, die ich wohl hoffentlich alle beheben konnte. Zumindest bei meinen Tests sieht es nun korrekt aus.

Zum einen sorgt die Erstellung der Trefferliste immer wieder dafür das die Bereiche der Bedingten Formatierungen angepasst/zerschossen werden. Deshalb habe ich die BF-Vorlage noch einmal angepasst und hoffe, dass dieses Problem damit behoben ist.

Weithin kann es beim Spaltenmerge dazu kommen, das null-Zeilen dazu führen dass die Anzahl der zu vergleichenden Splaten falsch ist. Dies null-Zeilen werden nun vor dem eigentlichen Vergleich ausgefiltert.

Das größte Problem bestand aber darin, dass es vermutlich wegen des 2-fachen Merge mit der Abfrage "Prüfspalten", immer wieder Firewall-Fehler angezeigt wurden. Intressanterweise hat die Abfrage dann aber doch meist Ergebnisse produziert. Diese waren dann aber einfach falsch.

Ich habe nun die Abfrage ziemlich umgestellt, wobei sie dadurch auch wesentlich kompakter wurde und hoffe, dass damit nun alle Probleme beseitigt sind.

Anbei die korrigierte Mappe, wobei ich für die Korektur meine letzte Fassung verwendet habe.

 

Ich sage es mal so: Die Lösung ist funktionell, das Ergebnis schlicht und die Lösungsfindung war gewiss nicht einfach. In der Ergebnis-Liste stehen all jene Werte, die in allen (ausgewählten) Spalten durchgängig Duplikate sind. Und wäre in den Quelldaten noch eine Spalte mit einer eindeutigen Bezeichnung (Index, Kundennummer, …) dann hätte die natürlich auch mit (als beispielsweise erste Spalte) erscheinen können.

Da Power Query ausgesprochen funktionale Ergebnisse liefert, ist auch keine direkte BF möglich. Mir reicht es vollkommen aus, wenn ich sehe, dass dieser, jener und auch ein anderer definierter Wert in den Quelldaten die Bedingung "komplettes Duplikat ind den definierten Spalten" erfüllt; ich würde auch schon in den Rohdaten einen Index einfügen, damit die Zuordnung leichter ist.