Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Problem mit dem Entfernen von Duplikaten

Problem mit dem Entfernen von Duplikaten
06.05.2019 20:45:14
Duplikaten
Hallo,
ich hoffe jemand hat eine Lösung für mich,
ich möchte mit den Kriterien Spalte A und B Duplikate entfernen aber
ich möchte, das alle Zeilen die einen Inhalt in Spalte C haben erhalten bleiben, selbst wenn sie ein Duplikat sind.
Oder anders gesagt es sollen nur Duplikate entfernt werden, wenn in der betroffenen Zeile nichts in Spalte C steht.
Gibt es da eine Lösung?
Danke
Jenny

28
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Problem mit dem Entfernen von Duplikaten
06.05.2019 21:23:44
Duplikaten
Hi
noch mal ne Rückfrage:
nehmen wir an, du hast folgende Kombination in zwei Zeilen ("_" steht für leer)
A|B|C
x|y|z
x|y|_
ist die zweite Zeile jetzt ein Duplikat und kann gelöscht werden oder ist sie ein Unikat?
ggf lädst du mal ein paar Beispielzeilen hoch und markierst die Zeilen, die gelöscht werden sollen.
Gruß Daniel
AW: Problem mit dem Entfernen von Duplikaten
06.05.2019 21:37:50
Duplikaten
Hallo Daniel,
ja in diesem Fall wäre es ein Duplikat. Ich hab das einfach so gemeint:
https://www.herber.de/bbs/user/129626.xlsx
Gruß
Jenny
AW: Problem mit dem Entfernen von Duplikaten
06.05.2019 21:53:01
Duplikaten
HI
da fällt mir spontan nur folgende Lösung ein:
1. falls in den Echtdaten nicht vorhanden: füge eine Überschriftenzeile ein, die zu bearbeitenden Daten dürfen nicht in Zeile 1 beginnen, sondern frühestens ab Zeile 2
2. sortiere die Liste nach Spalte A, B und C, so dass Duplikate direkt untereinander stehen und die Zeile, die in C einen Wert haben, zuerst kommen (Leerzellen sortiert Excel nach unten)
3. füge dann in Zelle D2 diese Formel ein und kopiere sie nach unten:
=WENN(C2"";ZEILE();WENN(UND(A2=A1;B2=B1);0;ZEILE()))
4. schreibe in die Zelle D1 (Überschrift der Hilfsspalte) die 0
5. führe jetzt mit den Spalten A-D die Funktion DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN aus, mit der Spalte D als Kriterium und der Option "keine Überschrift"
6. lösche die Hilfsspalte wieder
Gruß Daniel
Anzeige
AW: Problem mit dem Entfernen von Duplikaten
06.05.2019 22:08:08
Duplikaten
Hallo Daniel,
damit kann leider etwas nicht stimmen.
Ich bin jetzt hingegangen und habe die Tabelle nach Spalte C sortiert, sodass die 796 Zeilen mit Inhalten in Spalte C oben stehen.
Und habe dann in D797 die Formel =ZÄHLENWENNS(A$1:A796;A797;B$1:B796;B797) eingefügt, und da die Formel überall 0 ausgibt, bedeutet dass dass aktuell unter den Zeilen die keinen Text in Spalte C haben, sich keine Duplikate befinden.
Trotzdem löscht dein Vorschlag 14 Zeilen.
Außerdem habe ich bei deinem Vorschlag ein Problem, ich habe bereits eine Hilfsspalte benötigt um die jetzige Reihenfolge festzulegen, sprich ich müsste jedesmal wieder eine Hilfsspalte bemühen um die ursprüngliche Reihenfolge wiederherzustellen.
Gruß
Jenny
Gruß
Jenny
Anzeige
AW: Problem mit dem Entfernen von Duplikaten
06.05.2019 22:16:00
Duplikaten
nee du musst nach A und B mit Prio 1 und 2 sortieren und nach Spalte C mit Prio 3.
alle Zeilen, die in A und B gleich sind, müssen direkt untereinander stehen und innerhalb dieses Blocks müssen die Zeilen, die in C einen Wert haben, oben sein:
A|B|C
a|b|x
a|b|y
a|b|_
c|d|z
c|d|_
zu der Sortiererei:
ein anderer Weg ist mir nicht eingefallen, wenn nicht jemand eine bessere Idee hat, kommst du um das sortieren nicht herum.
Gruß Daniel
Gerade die BspDatei zeigt ein weiteres Problem, …
07.05.2019 00:04:55
Luc:-?
…Jenny,
denn darin scheint die Reihenfolge des Auftretens im Original eine Rolle zu spielen, denn du lässt im Fall x y _ | x y _ | x y z sowohl das 1. als auch das letzte Auftreten von x y zu! Bei der vorgeschlagenen Sortierung würden wohl ebenso wie bei nachfolgendem 1.Fml-Vorschlag beide x y _ wegfallen (Hilfsspalte = 0).
1. Fml für Hilfsspalte D (Reihenfolge irrelevant, vorherige Sortierung nicht erfoderlich):
D1[:D7]:=WENN(C1>"";1;GANZZAHL(1/ZÄHLENWENNS(A$1:A$7;A1;B$1:B$7;B1))) ⇒ {1;1;0;0;1;0;1}
2. Fml für Hilfsspalte E (Reihenfolge relevant, vorherige Sortierung auszuschließen):
E1[:E7]:=WENN(C1>"";1;GANZZAHL(1/ZÄHLENWENNS(A$1:A1;A1;B$1:B1;B1))) ⇒ {1;1;0;1;1;0;1}
Die Hilfsspalte kann dann zum Filtern oder ZeilenLöschen per VBA benutzt wdn.
Gruß, Luc :-?
Anzeige
AW: bedingte Formatierung
06.05.2019 21:26:59
Fennek
Hallo,
es gibt sicher auch andere Ansätze, aber ein Weg wäre zuerst die Duplikate mit der bedingten Formtierung zu markieren, die Farbe fest zu übernehmen und dann alle gefärbten Zeilen, die in Spalte C leer sind, zu löschen.
mfg
AW: bedingte Formatierung
06.05.2019 21:41:52
Jenny
Hallo Fennek,
ich muss dir was gestehen, ich hab noch nie mit bedingter Formatierung gearbeitet, dachte jetzt eher an einer Hilfsspalte WAHR/FALSCH nach der ich vorgehen kann. Bei BF brauche ich eine Anleitung wie man das macht.
Sorry
Jenny
AW: Christian?
06.05.2019 21:50:26
Fennek
ist das so?
AW: Christian?
06.05.2019 21:56:31
Jenny
Ich weiß ja jetzt nicht wo du den Namen herhast, aber falls aus der Datei ich nutze im Moment das Laptop von meinem Mann, meins ist in Reparatur. ich will das mal nicht ausschließen dass da irgendwas von ihm in der Datei steht.
Anzeige
AW: Problem mit dem Entfernen von Duplikaten
06.05.2019 22:10:27
Duplikaten
Hi,
mal in deiner Beispieldatei ausprobieren
VG, Christian
Option Explicit
Public Function getRange(ByRef ws As Worksheet, ByRef StartZelle As String) As Range
Set getRange = ws.Range(StartZelle).CurrentRegion.Offset(1).Resize(ws.Range(StartZelle). _
CurrentRegion.Rows.Count - 1)
End Function
Sub DuplikateEliminieren()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Tabelle1")
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
Dim coll As Collection
Set coll = New Collection
' Datenbereich wie im Beispiel nur wird angenommen dass in Zeile 1 eine Überschrift  _
steht
Dim rng As Range
Set rng = getRange(ws, ws.Cells(1, 1).Address)
Dim r As Variant
Dim arr(2) As Variant
For Each r In rng.Rows
Dim key As String
key = r.Cells(1, 1).Value & r.Cells(1, 2).Value
If Not r.Cells(1, 3).Value = "" Then
arr(0) = r.Cells(1, 1).Value
arr(1) = r.Cells(1, 2).Value
arr(2) = r.Cells(1, 3).Value
dict.Add r.Row, arr
End If
If Not dict.exists(key) Then
arr(0) = r.Cells(1, 1).Value
arr(1) = r.Cells(1, 2).Value
arr(2) = ""
dict.Add key, arr
End If
Next r
' Ausgabe in Spalte E (i + 5)
Dim e As Variant
' Erste Ausgabezeile
Dim zeile As Long
zeile = 2
For Each e In dict.items
Dim i As Long
For i = 0 To UBound(e)
ws.Cells(zeile, i + 5).Value = e(i)
Next i
zeile = zeile + 1
Next e
End Sub

Anzeige
AW: Problem mit dem Entfernen von Duplikaten
06.05.2019 22:16:06
Duplikaten
Hallo Christian,
ich hoffe es macht dir nichts aus, wenn ich mit dem testen bis morgen warte, zum einen muss ich um 5:30 aufstehen, zum anderen habe ich morgen nachmittag auch frische Daten für die Originaltabelle, wo ich es direkt auch prüfen kann ob bei den frischen Daten sich auch welche eingeschlichen haben, die bereits vorhanden sind.
Aber vielen Dank für deine Mühe
Jenny
AW: Problem mit dem Entfernen von Duplikaten
07.05.2019 20:08:24
Duplikaten
Hallo,
nochmal ein bisschen überarbeitet:
VG, C.
Option Explicit
Public Function getRange(ByRef ws As Worksheet, ByRef StartZelle As String) As Range
Set getRange = ws.Range(StartZelle).CurrentRegion.Offset(1).Resize(ws.Range(StartZelle). _
CurrentRegion.Rows.Count - 1)
End Function
Sub DuplikateEliminieren()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Tabelle1")
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
' Datenbereich wie im Beispiel nur wird angenommen dass in Zeile 1 eine Überschrift  _
steht
Dim rng As Range
Set rng = getRange(ws, ws.Cells(1, 1).Address)
Dim r As Variant
Dim arr(2) As Variant
For Each r In rng.Rows
Dim key As String
key = r.Cells(1, 1).Value & r.Cells(1, 2).Value
If Not dict.exists(key) Then
arr(0) = r.Cells(1, 1).Value
arr(1) = r.Cells(1, 2).Value
arr(2) = r.Cells(1, 3).Value
dict.Add key, arr
Else
If Not r.Cells(1, 3).Value = "" Then
arr(0) = r.Cells(1, 1).Value
arr(1) = r.Cells(1, 2).Value
arr(2) = r.Cells(1, 3).Value
dict.Add r.Row, arr
End If
End If
Next r
' Ausgabe in Spalte E (i + 5)
Dim e As Variant
' Erste Ausgabezeile
Dim zeile As Long
zeile = 2
For Each e In dict.items
Debug.Print VarPtr(e)
Dim i As Long
For i = 0 To UBound(e)
ws.Cells(zeile, i + 5).Value = e(i)
Next i
zeile = zeile + 1
Next e
End Sub

Anzeige
ein bisschen ZÄHLENWENN
06.05.2019 22:35:48
WF
Hi,
schreib z.B. in K1:
=(ZÄHLENWENN(A$1:B1;A1)=1)*(ZÄHLENWENN(A$1:B1;B1)=1)+(C1"")
runterkopieren
Filtere dann die Tabelle nach 0 in Spalte K und lösche diese Zeilen.
WF
AW: ein bisschen ZÄHLENWENN
06.05.2019 22:46:32
Jenny
Hallo Werner,
erstmal vielen Dank,
aber so ganz passt das auch nicht, was passt ist, in keiner Zeile in der etwas in Spalte C steht steht eine 0.
Aber trotzdem hab ich 18572 mal eine 0, obwohl es aktuell Duplikate ausschließlich in Zeilen mit Inhalt in Spalte C gibt.
Kann es sein dass die Formel jede der beiden Spalten für sich genommen überprüft ob es ein Duplikat gibt? So war das nicht gemeint, es sollte nur überprüft werden, ob die Kombination aus dem Text in Spalte A und dem in Splalte B schon einmal vorkam, nicht jeder Text für sich genommen.
Gruß
Jenny
Anzeige
Dann als Kombi
06.05.2019 23:11:13
WF
Hi,
in K1:
=WENN(C1"";1;SUMMENPRODUKT((A$1:A1&B$1:B1=A1&B1)*1))
Dann nach Zahlen größer 1 in K filtern und diese Zeilen löschen.
WF
AW: Dann als Kombi
06.05.2019 23:31:43
Daniel
Hi WF
müsste hier nicht auch sortiert werden, um das richtige Ergebnis zu bekommen?
teste mal einfach mit zwei Zeilen:
Variante A:
A|B|C
x|y|z
x|y|_
Variante B:
A|B|C
x|y|_
x|y|z
in Variante A wird mit deiner Lösung die Zeile mit dem Blank gelöscht, in Variante B nicht.
Gruß Daniel
sehe ich so als richtig/logisch
06.05.2019 23:54:42
WF
Hi Daniel,
Variane A:
x|y an zweiter Stelle gab's ja schon an Position 1 - ist also ein Duplikat (Spalte C spielt keine Rolle)
in Variante B steht es an erster Stelle und an Position 2 steht es sowieso wegen C.
WF
Anzeige
AW: sehe ich so als richtig/logisch
07.05.2019 00:00:40
Daniel
sehe ich jetzt anders, aber das muss Jenny entscheiden, was sie haben will.
Gruß Daniel
AW: Dann als Kombi
07.05.2019 19:58:02
Jenny
Hallo WF,
die Formel funktioniert, hat aber auch eine ziemliche Rechenzeit bei 27000 Zeilen.
Daher meine Überlegung: ich weiß ja dass standardmäßig keine zu löschenden Zeilen in der Tabelle stehen, ich will lediglich wenn neue Zeilen hinzukommen prüfen ob sie bereits vorhanden sind und wenn ja löschen.
Ich kann natürlich, deine Formel in

=WENN(C26862"";1;SUMMENPRODUKT((A$1:A26862&B$1:B26862=A26862&B26862)*1))
ändern, wenn es erst in Zeile 26862 losgehen soll und von da aus nach unten kopieren, aber gibt es auch die Möglichkeit dass er automatisch die Zeilennummer nimmt anstatt dass ich 5mal die 1 in 26862 ändern muss?
Weil da ständig Zeilen hinzukommen ändert sich ja auch dann jedesmal die Zeile.
Danke
Jenny
Anzeige
AW: Dann als Kombi
07.05.2019 20:07:32
Daniel
HI
lass einfach die Formel in der ersten Zeile stehen und lösche sie in den darunterliegenden, bereits geprüften Zeilen.
Dann kannst du sie immer von der ersten Zeile kopieren, um sie dann nur bei den neuen Zeilen einzufügen um diese zu prüfen. Die Anpassung der letzten Zelle macht Excel dann automatisch.
Gruß Daniel
AW: Dann als Kombi
07.05.2019 20:10:14
Jenny
stimmt, geht auch, danke
AW: Dann als Kombi
07.05.2019 20:37:10
Daniel
wenn du dir nur den Formeltext merken willst, dann wechsle in die Z1S1-Bezugsart (Datei - Optionen - Formeln).
in der Z1S1-Bezugart werden die relativen Zellbezüge in den Formeln korrekt relativ beschrieben und nicht mit Pseudo-Absolutadressen angezeigt.
Das hat dann zur Folge, dass die Formeltexte auch in allen Zellen gleich sind, und es egal ist, in welche Zelle du den jeweiligen Formeltext einträgst.
du kannst beliebig zwischen den beiden Bezugsarten umschalten. Excel wandelt die Formeln immer automatisch in die jeweilige Schreibweise um (ist nur eine Frage der Darstellung, inhaltlich ändert sich nichts).
Gruß Daniel
AW: mit Formeln
07.05.2019 09:03:12
Fennek
Hallo,
für das Beispiel geht:

in D1: =A1&B1 und nach unten kopieren
in E1: =und(Zählenwenn($D$1:D1;D1>1;istleer(C1))

Alle Zeilen mit "Wahr"in Spalte E werden gelöscht.
mfg

AW: mit Formeln
07.05.2019 20:01:36
Jenny
Hallo Daniel,
ich musste deine erste Formel in Spalte F schreiben, da D und E bereits belegt sind, aber

=und(Zählenwenn($F$1:F1;F1>1;istleer(F1))
sagt man mir ich hätte zuviele Argumente eingegeben.
Und selbe Frage wie an WF, kann man F1 durch F in der aktuellen Zeile ersetzen, dass es egal ist in welcher Zeile ich starte?
DAnke
Jenny
nachtrag
07.05.2019 20:03:14
Jenny
natürlich C1 in der letzten Klammern aber trotzdem zuviele Argumente
AW: Beispieldatei
07.05.2019 22:47:33
Jenny
ok, mit der Formel aus der BSP klapt es auch bei mir. Danke

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige