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
Hallo,
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).
Elvisonline,
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.
@ 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 ...
Danke für den Hinweis, Günther.
Da habe ich dieses Mal gar nicht drauf geachtet.
Hallo,
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.
mfg
Hallo Leute,
danke für die Antworten. Würde mich über einen fertigen Code natürlich freuen. Würde der Code "nur" zählen oder klappt das auch mit der Formatierung?
Hallo,
so als Entwurf einer Lösung:
Code:
Sub iFen()
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
End With
End Sub
Meine Beispieldatei ist angehängt.
mfg
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ß
Hallo,
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).
mfg