Doppelungen in Excel finden

Computer: Software & Programmierung Office Software
1

Hallo,
ich habe ein xls-sheet mit 10000 Datensätzen in einer Spalte und vermute, dass einige wenige doppelt sind. Kann ich die mit einer Funktion finden?
Nochmal das Problem vereinfacht dargestellt:
A
B
C
D
E
E
F
G
…
Wie finde ich die doppelten, also E?
Viele Grüße und riesiges Danke
Klaus

2

Und dann?

ich habe ein xls-sheet mit 10000 Datensätzen in einer Spalte
und vermute, dass einige wenige doppelt sind. Kann ich die mit
einer Funktion finden?

Hi Klaus,
was soll dann mit den doppelten geschehen? Selektieren, Farblich markieren, Zeilen löschen oder was?
Gruß
Reinhard

3

Hallo Reinhard,
ist ganz egal, ich muss sie nur erkennen. Werden wohl nur ca. 2-3 der 10000 Daten sein. Ich möchte die Doppelten erstmal finden, um dem Fehler auf den Grund zu gehen, im Anschluss werd ich sie löschen.
Also farblich markieren oder selektieren reicht schon. Das was einfacher ist…
Danke
Grüße
Klaus

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

4

Hallo,
ich habe ein xls-sheet mit 10000 Datensätzen in einer Spalte
und vermute, dass einige wenige doppelt sind. Kann ich die mit
einer Funktion finden?
Nochmal das Problem vereinfacht dargestellt:
A
B
C
D
E
E
F
G
…
Wie finde ich die doppelten, also E?

Hallo Klaus,

mit diesem Makro wird zuerst sortiert und dann jedes Duplikat rot markiert. Es geht davon aus, dass alle zu suchenden Werte in Spalte A stehen und dass keine Überschrift vorhanden ist. Trifft diese Annahme nicht zu, müsste es angepasst werden:

Sub Duplikate()

Dim I As Integer
Dim Z As Integer
ActiveSheet.UsedRange.Select
Selection.Sort Key1:=Range(„A1“), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
I = Selection.Rows.Count
Debug.Print I
Range(„A2“).Select

For Z = 2 To I

If Cells(Z, 1).Value = Cells(Z - 1, 1).Value Then

Cells(Z, 1).Interior.ColorIndex = 38
Else

End If
Next Z

End Sub

Gruß

Steffen

5

Hallo Reinhard,
ist ganz egal, ich muss sie nur erkennen. Werden wohl nur ca.
2-3 der 10000 Daten sein. Ich möchte die Doppelten erstmal
finden, um dem Fehler auf den Grund zu gehen, im Anschluss
werd ich sie löschen.
Also farblich markieren oder selektieren reicht schon. Das was
einfacher ist…

Da melde ich mich mal. Ich kann das ganze nur in Englisch erklären, da ich keinerlei Deutsche Version habe. Dürfte sich jedoch mit einem Wörterbuch umsetzen lassen.
Für jede Zelle, welche in der reihe der farblich zu markierenden ist, wird eine bedingte Formatierung eingerichtet (geht mit copy&paste). Also erst mal für eine zelle einrichten:
Zelle markieren, Menue Punkt Format - Conditional Formatting. Dort als bedingung „Formel lautet“ folgendes eintragen: =SUMIF( ;:wink:> Und dann die Formatierung auffällig machen. Ich empfehle „Hintergrund einfärben“ Das sieht man eher als die Schriftfarbe ändern. Dann mit Copy & Paste Special - Only Formats auf alle Zellen übertragen. Funktioniert bestimmt auch mit anderen Formeln. Habe es jedoch in der Praxis gerade eben nur mit dieser hinbekommen. Theoretisch geht auch ein countif(…), jedoch nicht getestet. Als Beispiel habe ich hier: =SUMIF(B2:B8;B8;B2:B8)>B8 als Formel eingetragen. Im Bereich B2 bis B8 sind die Werte und die Zelle B8 soll eingefärbt werden, wenn ihr Wert noch einmal in der Liste auftaucht. Viel Spass!

6

vbaliste.xls bei XL2000 ist engl/dt. Wörterbuch

Da melde ich mich mal. Ich kann das ganze nur in Englisch
erklären, da ich keinerlei Deutsche Version habe. Dürfte sich
jedoch mit einem Wörterbuch umsetzen lassen.

Hi Frank,
keine Ahnung ob man mit einem normalen Wörterbuch die englische Bezeichnung in Excel-Syntax für Bereich.Verschieben erhält :smile:
Ich habe nur excel2000 von daher k.A. obs das in früheren schon oder in späteren Versionen noch gab/gibt.
Jedenfalls, heisst die Datei bei excel2000
vbaliste.xls , darin werden auch die normalen Tabellenfunktionen übersetzt.
Gruß
Reinhard

7

ich habe ein xls-sheet mit 10000 Datensätzen in einer Spalte
und vermute, dass einige wenige doppelt sind. Kann ich die mit
einer Funktion finden?

Hi Klaus,
wenn deine Werte in Spalte A stehen, so schreibe in B1
=WENN(ZÄHLENWENN(A$1:A1;A1)>1;„Duplikat“;"")
und kopiere das die Spalte B hinunter.
Gruß
Reinhard
ps: Nicht auf meinem Mist gewachsen, logo von den Formel-Kings auf http://www.excelformeln.de/formeln.html?welcher=79 :smile:

8

OT Vba Code Tipps

Sub Duplikate()

Dim I As Integer
Dim Z As Integer
ActiveSheet.UsedRange.Select
Selection.Sort Key1:=Range(„A1“), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
I = Selection.Rows.Count
Debug.Print I
Range(„A2“).Select

For Z = 2 To I

If Cells(Z, 1).Value = Cells(Z - 1, 1).Value Then

Cells(Z, 1).Interior.ColorIndex = 38
Else

End If
Next Z

End Sub

Hi Steffen, darf ich dir was zu deinem Posting sagen? Ist konstruktiv und positiv gemeint.
Benutze bitte bei der Darstellung von vba-Code den pre-Tag, er, bzw Tags werden unterhalb des Eingabefensters wenn du antwortest , erläutert.
D.h. vor dem Code schreibst du ein pre , nach dem Code ein /pre , beides in diesen eckigen Klammern eingefasst und deine Einrückungen im Code bleiben erhalten und der Code ist um Welten besser lesbar.
Im Anhang unten siehst du wie es dann mit pre aussieht.

i(gross) bzw l benutze ich nie als Variablenbezeichnung. je nach Schriftarteinstellung sind I l 1 nur mühsam voneinander zu unterscheiden.

Select kommt wohl vom Makrorekorder, ist aber zu 99,5% unnötig und verwirrt, grad bei längeren Codes ungemein.
Anstatt
ActiveSheet.UsedRange.Select
Selection.Sort Key1:=Range(„A1“), Order1:=xlAscending, …
direkt so:
ActiveSheet.UsedRange.Sort Key1:=Range(„A1“), Order1:=xlAscending,…

und wenn du mehrmals auf deine Selection zugreifen musst, wie nachher bei dir mit:
I = Selection.Rows.Count
dann gleich so:

with ActiveSheet.UsedRange
.Sort Key1:=Range(„A1“), Order1:=xlAscending, …

'andere Befehle

I = .Rows.Count

end with

Und anstatt rows.count wird die letzte „volle“ zelle, bzw deren Zeilennumer, einer Spalte so ermittelt: (Spalte ist D)
letzte=range(„D65536“).end(xlup).row
oder wenn zu erwarten sein muss dass ggfs auch Zeile 65536 voll sein könnte:
letzte= IIF(range(„D65536“)"",65536,range(„D65536“).end(xlup).row)
(IIF lernte ich auch erst vor Wochen kennen, wusste nicht dass es den Befehl gibt.)

Und zu „usedrange“, das ist in manchen Fällen sehr gut, aber auch problematisch auszuwerten.
Wenn jmd mal in Zelle IV65536 die Farbe änderte oder da steht ein Leerzeichen drin oder oder ist usedrange das ganze Tabellenblatt, also würde ich lieber alle Spalten abklappern um per eben erwähnten letztenZeile Ermittlung, den wahren usedrange zu kennen, also die zellen wo auch was drinsteht.

Dim as Integer ist bei Zeilen grundsätzlich nicht angesagt, Integer geht bis 32000 und eiun bisschen, Excel2000 hat aber 65536 zeilen, also bitte as Long deklarieren.

Ich habe das jetzt so runtergetippt, mag sein dass manche Codezeilen (besonders die mit dem IIF, irgednwas passt mir da nicht *grübel*) Fehler enthalten, aber ich hoffe es wurde klar was ich meine.
Gruß
Reinhard

Sub Duplikate()
Dim I As Integer
Dim Z As Integer
ActiveSheet.UsedRange.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, \_ 
Orientation:=xlTopToBottom
I = Selection.Rows.Count
Debug.Print I
Range("A2").Select
For Z = 2 To I
 If Cells(Z, 1).Value = Cells(Z - 1, 1).Value Then
 Cells(Z, 1).Interior.ColorIndex = 38
 End If
Next Z
End Sub
1 „Gefällt mir“
9

Hallo Reinhard,

dank Dir für die professionelle Durchsicht meines „rustikalen“ Codes.

Als reiner Endanwender (der nie programmieren gelernt hat) versuche ich die anfallenden Problemchen möglichst schnell zu lösen. Z.B. die gewünschte (Teil-) Aktionen mit dem Recorder aufzeichnen und zusammen basteln, Schleifchen hier, Schleifchen dort, fertig ist die Laube. Aber es erfüllt seinen Zweck … :smile:)

Gruß aus Stuttgart

Steffen

10

Hi Frank und Reinhard

Ich habe nur excel2000 von daher k.A. obs das in früheren
schon oder in späteren Versionen noch gab/gibt.
Jedenfalls, heisst die Datei bei excel2000
vbaliste.xls , darin werden auch die normalen
Tabellenfunktionen übersetzt.

Das File vbaliste.xls gab es schon in meinem Excel7 (=1995)!
Ich schreibe das nur, um den für mich neuen Tipp von Reinhard auszuprobieren mit den Begriffen in spitzen Klammern!
Gruss
Erich

11

wenn deine Werte in Spalte A stehen, so schreibe in B1
=WENN(ZÄHLENWENN(A$1:A1;A1)>1;„Duplikat“;"")
und kopiere das die Spalte B hinunter.
Gruß
Reinhard
ps: Nicht auf meinem Mist gewachsen, logo von den Formel-Kings
auf http://www.excelformeln.de/formeln.html?welcher=79 :smile:

Bei dieser Variante muss allerdings beachtet werden, dass man wirklich erst beim letzten Vorkommen eines Wertes nachschauen kann, ob er doppelt vorkommt. Will man allerdings neben jedem Wert die Anzahl stehen haben, muss man den gesamten Bereich fest vorgeben:

=WENN(ZÄHLENWENN(A$1:A **$10000** ;A1)\>1;"Duplikat";"")

oder kürzer*:

=ZÄHLENWENN(A$1:A **$10000** ;A1)\>1

Nachteil: Sie dauert u.U. länger, weil immer der gesamte Bereich durchsucht werden muss.
In diesem Falle hier ist aber sicher Deine (geklaute :wink:) Variante günstiger.

*) Dabei wird einfach der Wahrheitswert direkt ausgegeben. Ich verwende diese Variante sehr häufig, zumal sie tendenziell schneller ist, da die Wenn-Funktion nicht ausgeführt werden muss und die Datei nicht so groß wird. Wenn es viele Datensätze sind, ziehe ich eine solche Formel runter, kopiere sie direkt, klebe sie als reine Werte zurück und filtere dann die Daten mit dem Autofilter nach „Wahr“. Dabei werden dann alle Duplikate angezeigt. Bei der zweiten Variante mit „A$10000“ hingegen würden nicht nur die Duplikate, sondern auch die jeweils ersten Vorkommen der mehrfachen Werte angezeigt. Das kann wichtig sein bei der Weiterverarbeitung.

PS: Wenn man nicht wissen will, welche Duplikate drin sind, sondern diese einfach loswerden will, kann man sehr schön die „Spezialfilter“ aus dem „Daten“-Menü verwenden. Nachteil ist nur, dass man eine Überschrift braucht.
Bis ich das entdeckt hatte, benutzte ich immer ein eigenes Makro dafür. Das kann das gleiche und noch etwas mehr, hat aber den Nachteil, dass es deutlich langsamer arbeitet.

Kristian

12

FYI
siehe auch mein Posting an Reinhard zwei weiter unten oderso.