Hallo liebe Forengemeinde, habe in einer Spalte (ca.5000 Zeilen) Zahlen oder Kombinationen aus Zahlen und Buchstaben. Z.B.: 200056895 oder 32475EUN34. Mit einer Formel (Ergebnis-Zelle) würde ich jetzt gerne die Gesamt-Anzahl der Duplikate bestimmen. Ob die Zelleninhalte 2-fach oder x-fach vorkommen ist nicht wichtig. Ich möchte hiermit bestimmen wie viele "Fehler" meine Spalte beinhaltet. Problem: Die Zelleninhalte sind teilweise mehr als 15-stellig. Die übliche Zählung mit Summenprodukt und Zählenwenn scheitert wegen der Genauigkeit. Der Umweg über Hilfsspalte und Kombination mit ' (Genauigkeit) scheitert aus Performancegründen (Frontend). Leere Zellen oder 0 sollen nicht betrachtet werden. Im 2.Step würde ich dann noch alle Duplikate bedingt formatiert anzeigen lassen - scheitere aber hier auch an der Genauigkeit. Würde mich über Hilfe freuen. Danke
du schreibst, dass du keine Hilfsspalte willst und Zählenwenn nicht geht. Du kannst noch überlegen, ob du alles in Text umwandelst - mit TEXT(A2;"#") oder indem du verkettest &"" und dann zählenwenn verwendest. Kannst auch über eine Pivot-Tabelle nachdenken. Wenn dies auch nicht funktioniert, dann bleibt wohl nur der VBA-Weg, um Hilfsspalten zu bilden und wieder zu löschen. Übrigens die bedingte Foratierung ist auch nicht sehr ressourcenschonend (volatil).
um eine Hilfsspalte kommst du vermutlich nicht rum - gerade aus Performancegründen. Wenn man diese aber via Power Query füllt dann ist wieder etwas performance schonender.
Wir sehen uns! ... Detlef
Meine Beiträge können Ironie oder Sarkasmus enthalten.
@ shift-del Das war (natürlich) auch mein erster Gedanke, aber als mein Blick dann noch oben rechts zu den Userdaten wanderte erkannte ich, dass sich das Ganze in einem Antiquariat (xls 2007) abspielt ...
Beste Grüße Günther
Excel-ist-sexy.de …schau doch mal rein! Der Sicherheit meiner Daten wegen lade ich keine *.xlsm bzw. *.xlsb- Files mehr herunter! -> So geht's ohne!
5000 Zeilen sind für das "altmodische" VBA mit Arrays und Dictionary vermutlich unter 1 Sekunde zu bewältigen. Aber der Fragesteller sollte zuerst bestätigen, dass er mit einem Code etwas anfangen kann.
With CreateObject("Scripting.Dictionary") Ar = Columns(1).SpecialCells(2) For i = 1 To UBound(Ar, 1) If Ar(i, 1) <> 0 Then If Not .exists(Ar(i, 1)) Then y = .Item(Ar(i, 1)) Else 'färben der Duplikate Cells(i, 1).Interior.Color = vbYellow End If End If Next i 'Ausgabe in Text-Spalte Columns(3).NumberFormat = "@" Range("C1").Resize(.Count) = Application.Transpose(.keys) Debug.Print .Count
Hallo Fennek, vielen Dank! Das mit der Formatierung klappt "fast" perfekt. Makro färbt nur bis zur nächsten leeren Zelle. Der Code kreiert in C eine Liste der Werte ohne Duplikate. Wäre es noch möglich die Anzahl der Duplikate zu zählen? Danke Gruß
im Debug-Fenster steht die Anzahl ohne Duplikate, alle Werte kannst du entweder in Sheet ermitteln oder mit Ubound(Ar).
Gefärbt wird nur in Spalte A und auch nur das 2. oder jedes weitere Vorkommen. Wenn die ganze Zeile gefärbt werden soll, muss ein ".resize" ergänzt werden.
ungegeprüft:
Code:
cells(i,1).resize(,### Anzahl der Spalten ###) .interior.color = vbyellow
Die Anzahl der Spalten kann direkt fest eingegeben werden oder variabel mit ubound(Ar,2).
