Quality Control Program -> ALLE Duplikate finden

[Spike87]

Einen guten Tag wünsche ich euch Allen!

Ich habe bisher fleißig mitgelesen und wollte jetzt auch mal gezielt fragen stellen, erstmal zu mir: Ich bin 29 Jahre, habe nach meinem naturwissenschaftlichen Studium eine Weiterbildung ins Datenmanagement gemacht und bin jetzt in der Traineezeit, dementsprechend auch voller Sorge, dass ich übernommen werde!


Derzeit erstelle ich ein Quality Control Programm auf Excel/VBA Basis, welches Daten aus einer xml (Ursprung ist ein online Dokumentenablagesystem) verarbeitet. Die Datei hat ca 20 Spalten und ist 40.000 Zeilen lang. Ich muss sagen, meine Kenntnisse sind eher rudimentär und wir haben nur eine Woche VBA gehabt, ich hab jetzt mit meinem Wissenstand schon einige Abfragemöglichkeiten (Über eine UserForm für den User) eingebaut.

Mich lassen jetzt aber 2 Aspekte ein wenig verzweifeln:

Ich soll alle Duplikate (Innerhalb einer Spalte) und ihren Pfad  (Spalte die danebensteht und verkettet wird bei der Ausgabe) ausgeben.

Also sagen wir mal

pfad1 | Test1

pfad2 | Test2
pfad3 | Test3
pfad4 | Test2
pfad4 | Test1
pfad6 | Test1
Pfad7 | Test4

Ich habe das ganze bisher so gelöst, dass ich über eine Hilfsspalte V2 (nach unten bis 40.000 gezogen)   " =ZÄHLENWENN($U2:U$2;U2) " die Anzahl wenn gefunden ausgebe.

Das ganze wird dann mit:" =WENN(V2>1;Tabelle1!P2&U8&"("&V2&")";) "    verkettet und dann im nächsten Schritt über ein Makro in der nächsten Zeile sortiert.


Was mir nun fehlt! ist natürlich der Erste Wert des Duplikates, der unbedingt auch angegeben sein muss (damit man dann hinterher die Pfade vergleichen kann).

Ich habe einige Ideen dazu, aber die beruhen fast alle auf noch weiteren Hilfsspalten und die Datei hat mittlerweile die 10mb Grenze geknackt und wird schon immer langsamer (und stürzt häufiger ab?!). Aber wenn da jemand eine gute Idee mit Hilfsspalten hat, würde ich mich auch sehr freuen.

Ansonsten wäre es doch bestimmt möglich, über ein VBA Makro und Schleifen das ganze Auswerten zu lassen: Quasi -> WENN(Duplikatanzahl von Wert = 2 ( Dann -> Liste durchsuchen bis Wert UND Duplikatanzahl=1 gefunden;Wert+Pfad ausgeben;)Sonst Nichts);

Hat da jemand eine Idee?

Das größte Problem ist, ich bin der einzige ITler hier und habe auch niemanden um Sachen nachzufragen, kein Buch etc. und bring mir quasi alles beim arbeiten am Projekt und aus google selbst bei und habe Angst, dabei wichtige und viel einfachere Wege zu übersehen.

Ich hätte an sich noch 1000 Fragen, aber ich würde mich wahnsinnig über eine Antwort zu diesem Thema freuen!
Viele dank!

[Fennek]

Hallo,

ich habe verstanden, dass in der Spalte B (Dateinamen) alle Duplikate gesucht werden sollen, und dann ALLE jewiligen Pfade gezeigt werden sollen:

1. Ansatz: Pivot-Tabelle, DateiNamen in die Zeilen, Pfade in "Wert"

2. Ansatz: Spalte B in einen freien Bereich kopieren und "Duplikate entfernen" aus dem Menü Daten. Dann können mit Formeln oder vba alle Pfade gesucht werden. (nur sinnvoll, falls Pivot-Tabells nicht klappt)

Es gibt sicher weitere Ansätze, aber vor deinem Feedback macht es wenig Sinn darüber nachzudenken.

mfg

[Spike87]

Hallo!
Das mit dem Duplikate entfernen war ein interessanter Ansatz, das ich dann quasi über die freien Felder auf die Pfade schließe. Das wäre aber auch wieder nur über weitere Umwege möglich gewesen, da zwischen den Einträgen lücken sind. Dann hätte ich die von den Duplikaten entfernte Spalte mit der Ursprünglichen vergleichen müssen und alle "Abweichungen" quasi aufzeigen und dann mit den Pfaden verknüpfen.
Wäre gegangen (Aber leidet die Performance nicht unter immer mehr Formelabfragen im jeweils 40.000 Zellen bereich?) und was viel gravierender ist:
Wenn man über Daten -> Duplikate entfernen geht, dann sortiert er die Daten zwingend und ich habe alle untereinander stehen und daher ist das ganze hinfällig.


Ich schau jetzt mal wegen der Pivottabelle, bin aber ein wenig erstaunt. Ich dachte Pivottabellen wären einfach nur ein grafisches Schnickschnack und hätten wenig Funktionalität, das wurde bei uns auch leider nur einen Tag lang besprochen.

Habe das ganze auch grade mal probeweise mit einer Do until Schleife gemacht, bei nem counter von 40.000 brauchte er aber ewig und hat dann eine Überlaufmeldung ausgegeben.

Vielen dank schon mal!

[Fennek]

Hallo,

m.M.n. sind Pivot-Tabelle DAS starke Feature in Excel, wenn jemand kein "Formel-Künstler" oder vba-Nerd ist, dürfte das in einem Berufsleben nicht / wenig stören, aber Pivot MUSS jeder, der Datenauswertung machen möchte, kennen.

M.E. sind 40.000 Zeilen nicht besonders viel, was Verarbeitungsziet benötigt sind "volatile" Funktionen, aber die kann man in vba temporär abschalten.

mfg

[Spike87]

Oh ja, ich merk auch dass da scheinbar noch ungeahnte Möglichkeiten drinstecken.
Sehr gut, dass wir das nicht gelernt haben als Data Manager..

Naja leider hat es nicht so funktioniert, wie beschrieben:

Habe jetzt in der Pivottabelle unter Zeilenbeschriftung den Pfad stehen und unter Anzahl von Datei je nach dem eine unterschiedliche Zahl.


Wie komm ich jetzt von dem Stand aus zu den "ersten" Duplikaten, die mir noch fehlen!?

[Fennek]

Hallo,

hier im Forum wird fast nie über Pivot gesprochen, vermutlich, weil man Formeln und vba-Code leicht ins Fenster schreiben kann.

Pivot kann man nicht / schlecht sprachlich erklären, es gibt da auch "tausend" Optionen. Aber du kannst eine Beispieldatei hochladen, die genügend Datensätze enthält, dass zumindest einige Dateinamen mehrere Pfade haben.
(vermutlich reichen 100)

mfg

[Spike87]

  TestDuplikate.xlsx (Größe: 14,21 KB / Downloads: 6)
Das kann ich mir gut vorstellen, dass das schnell mal unübersichtlich wird mit der Erklärung.

Ich habe deswegen eine Beispieldatei erstellt, aber in diesem Fall noch mit einer weiteren Variante, die ich vorher beim Erklären weggelassen habe.
Der Pfad besteht quasi aus 2 Spalten, die ich über & am Ende dann verknüpfe bei der Endausgabe.

Aber ansonsten bleibt alles wie gehabt, wenn ein Dateiname doppelt und mehr vorkommt, soll der Dateiname + Pfad ausgeworfen werden. Das ganze funktioniert auch schon mit meinen Formeln in der Datei, nur leider besteht noch das Problem, dass der ERST gefundene nicht ausgeworfen wird.

Was bei ner quality control natürlich doof ist, da bräuchte man quasi alle "schwarzen Schafe"!

[Rabe]

Hi,

Habe jetzt in der Pivottabelle unter Zeilenbeschriftung den Pfad stehen und unter Anzahl von Datei je nach dem eine unterschiedliche Zahl.
Wie komm ich jetzt von dem Stand aus zu den "ersten" Duplikaten, die mir noch fehlen!?

zuerst mußt Du die Überschriften ebenfalls in den Pivot-Datenbereich einbeziehen:

1. Klick in die Pivot
   
2. Menü: Pivot Table-Tools - Analysieren - Datenquelle ändern...
   
3. dort dann aus $B$3: das machen $B$2:
   
4. dann Dateiname in Zeilenbereich ziehen, in Pivot bei "Zeilenbeschriftung" mit dem kleinen Down-Dreieck die [leer] rausfiltern
   
5. dann WegB über den Dateiname in Zeilenbereich ziehen
   
6. dann Menü: Pivot Table-Tools - Analysieren - Feldeinstellungen - Layout& Drucken den Haken setzen bei "Elementnamen ...
   

   

[Rabe]

Hi,

hier im Forum wird fast nie über Pivot gesprochen,

das stimmt so nicht.

vermutlich, weil man Formeln und vba-Code leicht ins Fenster schreiben kann.

das eher.

[Spike87]

Vielen dank für die Mühe,
nur weiß ich nicht ob es grad in der Kantine zu großzügig Essen gab oder ich einfach auf dem Schlauch stehe, aber ich komm leider bei deiner Anweisung schon im zweiten Schritt nicht weiter.

Mit "Klick in die Pivot" meinst du bestimmt irgendeine Stelle der Pivottabelle selektieren, damit sich Rechts das Fenster mit den Einstellungen öffnet.

Aber das "Menü" finde ich leider nicht und weiß ab da nicht weiter. Habe einfach eine neue Pivottabelle gemacht, in der ich die erste Zeile mit Markiert habe, aber das hat auch keine Veränderung gebracht.

Pivottableoptionen und noch ein paar andere Sachen finde ich wohl, aber leider nicht das gewünschte Menü. Falls das noch wichtig ist: Ich benutze Excel 2010.

Zudem finde ich das rausfiltern nicht über das Dreieck hinaus, meinst du mit "Menü" zufällig das "Dreieck-Menü", das sieht nämlich vom Aufbau aus wie das Menü aus deinem letzten Schritt (Im Screenshot)