Microsoft Excel

Herbers Excel/VBA-Archiv

Duplikate finden bei großen Datenmengen | Herbers Excel-Forum


Betrifft: Duplikate finden bei großen Datenmengen von: Wolfango
Geschrieben am: 21.02.2012 14:11:05

Hallo Experten,

folgende Formel zum Auffinden von Duplikaten habe ich mal hier im Forum gelernt (von mir dann angepasst):

=WENN(ZÄHLENWENN($F$3:$F$130000;F3)>1;"duplicate";"")

Nun habe ich eine Datenbank mit 120.000 Datensätzen. Es funktioniert auch mit o.g. Formel, aber der Rechner hat nach 15min immer etwa 5% neu berechnet; dauert also viel zu lange.

Weiß jemand eine Alternative, die den Rechner nicht derart in die Knie zwingt?

Gruß,

Wo.

  

Betrifft: AW: Duplikate finden bei großen Datenmengen von: Lutz Petzold
Geschrieben am: 21.02.2012 15:47:08

Hallo Wolfango,

Du hast die Formel einmal pro Spalte und zusätzlich in mehreren Spalten stehen?
Wann benötigt der Rechner solange, beim Öffnen der Datei?

M.f.G.
Lutz


  

Betrifft: AW: Duplikate finden bei großen Datenmengen von: Wolfango
Geschrieben am: 21.02.2012 15:57:58

...ich habe die Formel nur in einer einzigen Spalte stehen.....und gezählt werden auch nur die Einträge in Spalte F (dies sind allerdings etwa 50 Zeichen pro Zelle)....das ganze dann über fast 130.000 Datensätze.

Die Datei steht auf manuell berechnen!
Sobald ich nun sage 'Blatt berechnen', dauert es mehrere Minuten bis die Anzeige von 0 auf 1% springt. (Pentium 4 - 2GB)

Gruß,
Wo.


  

Betrifft: AW: Duplikate finden bei großen Datenmengen von: Tino
Geschrieben am: 21.02.2012 16:06:01

Hallo,
kann es auch eine VBA Variante sein?


Gruß Tino


  

Betrifft: AW: Duplikate finden bei großen Datenmengen von: Wolfango
Geschrieben am: 21.02.2012 16:12:49

...wenn es keine Formelösung gibt und dies das Tempo beschleunigt.
Wie sähe die denn aus?

(Anm: ist schon bemerkenswert, da lassen sich jetzt in Excel über 1Mio Zeilen eingeben....aber was braucht man den für Rechner um dann damit zu arbeiten....)

Gruß,
Wo.


  

Betrifft: AW: Duplikate finden bei großen Datenmengen von: Tino
Geschrieben am: 21.02.2012 16:21:39

Hallo,
hier mal eine Variante zum testen.
Im Code müsstest Du evtl. die Tabelle und den Zellbereich anpassen.
Die Ausgabe erfolgt im Code ab G3, dies müsstest Du evtl. auch anpassen.
Bei mir dauert die Verarbeitung ca. 1 Sekunden.

Sub Find_Doppelte()
Dim oDic As Object, ArrayData(), ArrayAusgabe()
Dim n As Long
Set oDic = CreateObject("Scripting.Dictionary")

With Sheets("Tabelle1") 'Tabelle anpassen 
    ArrayData = .Range("F3:F130000").Value2 'Zellbereich anpassen 

    Redim ArrayAusgabe(1 To Ubound(ArrayData), 1 To 1)
    
    For n = 1 To Ubound(ArrayData)
        oDic(ArrayData(n, 1)) = oDic(ArrayData(n, 1)) + 1
    Next n
    For n = 1 To Ubound(ArrayData)
        If oDic(ArrayData(n, 1)) > 1 Then ArrayAusgabe(n, 1) = "duplicate"
    Next n
    
    'Ausgabe erste Zelle anpassen 
    .Range("G3").Resize(Ubound(ArrayAusgabe)) = ArrayAusgabe
End With

End Sub
Gruß Tino


  

Betrifft: Duplikate, Unikate-->Dictionary von: ransi
Geschrieben am: 21.02.2012 17:43:25

HAllo Thino

"dauert die Verarbeitung ca. 1 Sekunden."
Das ist für 130000 Datensätze sauschnell
Aber selbst da geht noch was:

Sub Find_Doppelte1()
Dim oDic As Object, ArrayData(), ArrayAusgabe()
Dim n As Long
Dim T As Double
T = Timer
Set oDic = CreateObject("Scripting.Dictionary")
With Sheets("Tabelle1") 'Tabelle anpassen
    ArrayData = .Range("F3:F130000").Value2 'Zellbereich anpassen
    
    Redim ArrayAusgabe(1 To UBound(ArrayData), 1 To 1)
    
    For n = 1 To UBound(ArrayData)
        If oDic.exists(ArrayData(n, 1)) Then
            ArrayAusgabe(n, 1) = "duplicate"
            ArrayAusgabe(oDic(ArrayData(n, 1)), 1) = "duplicate"
            Else:
            oDic(ArrayData(n, 1)) = n
        End If
        
    Next n
    'Ausgabe erste Zelle anpassen
    .Range("G3").Resize(UBound(ArrayAusgabe)) = ArrayAusgabe
End With
Debug.Print Timer - T; " Find_Doppelte1"
End Sub



ransi


  

Betrifft: AW: Duplikate, Unikate-->Dictionary von: Tino
Geschrieben am: 21.02.2012 18:06:51

Hallo,
auch nicht schlecht, super Idee!

Gruß Tino


  

Betrifft: AW: Duplikate, Unikate-->Dictionary von: Lutz Petzold
Geschrieben am: 21.02.2012 18:13:58

Hallo Tino,

eine Frage, bei mir dauert die Ausführung des Codes mindestens 1 Minute (nicht gestoppt), Rechner so schlecht, oder hängts am Office 2007???

M.f.G.
Lutz


  

Betrifft: AW: Duplikate, Unikate-->Dictionary von: Tino
Geschrieben am: 21.02.2012 18:32:12

Hallo,
die Verarbeitung hängt natürlich von vielen Faktoren ab.
Rechner, welche Daten werden verarbeitet (Zahlen oder langer Text),
andere Daten in der Tabelle, Formel usw…

Hier mal meine Testmappe,
bei schwankt es von 0,9 bis 1,3 Sekunden,
wenn ich nur Zahlen verarbeite wird der Code noch schneller.

Der Button Erstelle Beispiel Daten für Test erstellt die Testdaten und der andere macht die Prüfung.

https://www.herber.de/bbs/user/79023.xlsm


Gruß Tino


  

Betrifft: AW: Duplikate, Unikate-->Dictionary von: Tino
Geschrieben am: 21.02.2012 18:32:41

Hallo,
die Verarbeitung hängt natürlich von vielen Faktoren ab.
Rechner, welche Daten werden verarbeitet (Zahlen oder langer Text),
andere Daten in der Tabelle, Formel usw…

Hier mal meine Testmappe,
bei mir schwankt es von 0,9 bis 1,3 Sekunden,
wenn ich nur Zahlen verarbeite wird der Code noch schneller.

Der Button Erstelle Beispiel Daten für Test erstellt die Testdaten und der andere macht die Prüfung.

https://www.herber.de/bbs/user/79023.xlsm


Gruß Tino


  

Betrifft: AW: Duplikate, Unikate-->Dictionary von: Lutz Petzold
Geschrieben am: 21.02.2012 18:52:23

Hallo Tino,

bei Deiner Tabelle knapp 4s

Danke für die Info....

M.f.G.
Lutz


  

Betrifft: AW: Duplikate, Unikate-->Dictionary von: Wolfango
Geschrieben am: 22.02.2012 10:19:32

Danke an Tino!
Danke an Ransi!

...für die VBA-Codes.

Bei mir kommt dann leider immer die Meldung 'Index außerhalb des gültigen Bereichs'.
Weiß nicht, was ich tun muss (sorry, keine Ahnung von VBA)??

Anm: Spalte F enthält die Daten, die auf Eindeutigkeit geprüft werden müssen, Spalte G ist leer.

Danke und Gruß,
Wo.


  

Betrifft: nur in dieser Version? von: Tino
Geschrieben am: 22.02.2012 14:32:35

Hallo,
konnte ich bei mir nur feststellen wenn ich den Code mit F8 Schrittweise durchführe.
Aus irgendeinen Grund verliert das Dictionary Objekt den Wert den es zuvor in der
Zeile oDic(ArrayData(n, 1)) = n zugewiesen bekommet bekommt.
Dies konnte ich aber auch nur in dieser Version feststellen, in meiner Version nicht.
Vielleicht kennt aber Ransi den Grund!

Gruß Tino


  

Betrifft: Keine Ahnung was in Version 13 klemmt ! von: ransi
Geschrieben am: 22.02.2012 16:34:52

HAllo


In Excel 2007 und Excel 2010 läuft der Code sauber durch.

2,63671875 Sekunden; Version 12.0
2,72265625 Sekunden; Version 14.0

ransi


  

Betrifft: AW: Duplikate finden bei großen Datenmengen von: Lutz Petzold
Geschrieben am: 21.02.2012 16:17:57

Hallo Wolfango,

ich muß noch mal nachhaken, benutzt Du diese Formel nur einmal, oder für jede der 130000 Datenzeilen, um die Doppelgänger zu markieren?
Oder sind weitere Rechenoperationen in der Datenbank durchzuführen?
Bei einer großen Anzahl von Rechenoperationen mit der notwendigen Bildschirmaktualisierung kann dass längere Bearbeitungszeit in Anspruch nehmen.
Hier bestünde die Möglichkeit per VBA die Berechnung anzustoßen und dabei die Bildschirmaktualisierung während dieser Zeit auszuschalten.

M.f.G.
Lutz


  

Betrifft: AW: Duplikate finden bei großen Datenmengen von: Wolfango
Geschrieben am: 21.02.2012 16:47:04

ich muß noch mal nachhaken, benutzt Du diese Formel nur einmal, oder für jede der 130000 Datenzeilen, um die Doppelgänger zu markieren?
...letzteres! Die Formel habe ich mit AutoAusfüllen über 130.000 Datensätze nach unten kopiert um hierüber die doppelten Datensätze zu markieren!
Hab' das ganze jetzt mal auf einem etwas schnelleren Rechner laufen lassen, der war dann nach 1h 30min fertig. Auf dem Bildschirm wird während dieser Zeit gar nichts aktualisiert.

Meine Gedanken gingen in die Richtung, dass es viell. noch eine ganz andere Lösung gibt, Doubletten zu finden; ohne ZÄHLENWENN, da diese Funktion bei dieser Menge Daten offensichtl. sehr viel Rechenkapazität benötigt?

Gruß,
Wo.


  

Betrifft: AW: Duplikate finden bei großen Datenmengen von: Lutz Petzold
Geschrieben am: 21.02.2012 17:55:28

Hallo Wolfango,

noch ein Vorschlag:

- Spalte F markieren
- unter bedingte Formatierung neue Regel erstellen
- Formel zur Ermittlung... auswählen und Deine Formel =ZÄHLENWENN($F$3:$F$130000;F3) > 1 eingeben
- Hintergrundfarbe auswählen
- Doppelte Zellen werden markiert (bei Eingabe des neuen Zellwertes dynamische Änderung
vorausgesetzt der Berechnungsmodus steht auf Automatik, alternativ Neuberechnung anstoßen)

M.f.G.
Lutz


  

Betrifft: AW: Duplikate finden bei großen Datenmengen von: firmus
Geschrieben am: 21.02.2012 22:16:43

Hi Wolfango,

kann mir vorstellen, dass diese Variante schneller ist (weil weniger rechnen).
Tabelle nach Spalte F sortiert.
In Zelle G2 kommt die Formel "=Wenn(F2=F1;G2=G1+1;1)
Zelle G2 nach unten kopieren.

Beim Ersten Auftreten eines Wertes in der Spalte F steht die "1", bei allen weiteren gleichen Werten wird
hochgezählt. Bei Duplikaten kann damit EIN Eintrag erhalten bleiben - so gewollt von Dir?

Rückmeldung wäre nett.

Gruß vom Faschingsmuffel,
Firmus


  

Betrifft: AW: Duplikate finden bei großen Datenmengen von: Wolfango
Geschrieben am: 22.02.2012 10:09:06

...Danke für den Vorschlag.
Die Daten dürfen (aus bestimmten Gründen) nicht sortiert werden.

Gruß,
Wo.


Beiträge aus den Excel-Beispielen zum Thema "Duplikate finden bei großen Datenmengen"