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

Duplikate löschen mit Bedingung

Duplikate löschen mit Bedingung
15.07.2021 21:21:08
Mouna
Hallo liebes Forum,
ich möchte gern über eine in Excel vorliegende Liste eine Gewinnerziehung machen. Die Beispieldatei ist diese:
https://www.herber.de/bbs/user/147146.xlsx
Nun möchte ich die Zeilen die Duplikate enthalten löschen oder ausblenden. Aber nur die, bei denen in Spalte E ein "X" steht.
Der Eintrag mit dem "X" soll stehen bleiben.
In meiner Datei heißt das, folgende Zeilen sollen vollständig gelöscht oder ausgeblendet werden:
1, 5, 6, 16
Leider fällt mir nicht ein, wie ich das angehen kann. Meine Kenntnisse in VBA sind bescheiden. In Excel sind sie ganz gut.
Vielen Dank für Eure Hilfe im Voraus.
Herzliche Grüße
Mouna

27
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Bedingung unklar!
15.07.2021 21:40:18
GerdL
Moin Mouna,
meinst du mit Zeilen deine eigenen fortlaufenden Nummern?
Was ist mit Micha?
Gruß Gerd
AW: Bedingung unklar!
15.07.2021 22:17:35
Mouna
Nein, ich hatte mich nur mit Zeile 1 vertan. Korrekt sind:
2, 5, 6, 16
Micha hat kein X, deshalb sollen beide Einträge stehen bleiben.
AW: Duplikate löschen mit Bedingung
15.07.2021 21:43:48
Yal
Hallo Mouna,
in dem Fall am einfachste und schnellste geht es mit Power Query (PQ):
_ eine Zelle innerhalb der Tabelle auswählen
_ unter "Einfügen", "Tabelle" anklicken oder Strg+t
_ im Reiter "Daten", "Aus Tabellen" anklicken
Jetzt bist Du im PQ-Editor.
_ Spalte Email markieren (auf dem Überschrift klicken) und aufsteigend sortieren
_ Spalte Gewinner markieren und absteigend markieren (PQ macht daraus eine zusammenhängende Sortierung, nicht 2)
_ Spalte Name und Email markieren (Email würde auch reichen)
_ Rechtsklick auf dem Spaltenüberschrift und Duplikate entfernen auswählen
_ im Reiter "Datei" Schliessen & laden.
Fertig.
Der Trick dabei ist, dass Duplikate entfernen immer der ersten Treffer behält.
Jetzt sehe ich, dass diese Vorgehensweise auch direkt in der Tabelle machbar wäre. Aber dann würdest du Daten vernichten. Mit PQ ist Ergebnis separat und wenn die Quelle sich ändert, brauchst Du nur auf die Ergebnistabelle mit Rechtsklick "Aktualisieren" und schon ist alles neubewertet.
VBA wäre hier aufwendiger. Wobei: die Schritte, die hier gemacht worden sind, ohne Power Query unter Makro-Rekorder machen (zuerst die x sortieren, dann die Email) und that's it.
VG
Yal
Anzeige
AW: Duplikate löschen mit Bedingung
15.07.2021 22:16:27
Mouna
Ganz lieben Dank für Deine ausführliche Rückmeldung. Die Sortierung nach der Spalte mit Mailadresse ist leider nicht möglich, denn wir müssen den Vorgang immer wieder machen.
Hintergrund ist, dass ein Gewinner, der einmal per Zufallszahl gezogen wurde, nicht noch einmal gewinnen kann.
Er muss aber vor der Ziehung zum Gewinner mehrfach in der Liste stehen, denn das erhöht seine Chancen.
AW: Duplikate löschen mit Bedingung
16.07.2021 09:39:20
Yal
Hallo Mouna,
"Die Sortierung nach der Spalte mit Mailadresse ist leider nicht möglich, denn wir müssen den Vorgang immer wieder machen."
-> da sehe ich nicht den Link zwischen Bedarf ("immer wieder") und Voraussetzung (sortiert oder nicht).
"Hintergrund ist, dass ein Gewinner, der einmal per Zufallszahl gezogen wurde, nicht noch einmal gewinnen kann."
-> manche Beschreibungen sind schon von Anfang an besonders wertvoll. Eigentlich willst Du nicht die mit dem x behalten, sondern wegmachen.
Dann Teil 3 meinem Vorschlag gilt: per Hand unter Verwendung von Makro Rekorder.
VG
Yal
Anzeige
AW: Duplikate löschen mit Bedingung
16.07.2021 10:59:55
Mouna
Tut mir leid, das ich mich nicht klar genug ausdrücken kann.
Zu Deiner Anmerkung meines "immer wieder": Das ist vielleicht der falsche Grund. Meine Erfahrung mit dem Sortieren großer Excellisten ist leider schlecht, denn es ist mir - sicher aus Unwissenheit - schon oft passiert, dass beim Sortieren nicht alle Spalten mit sortiert wurden.
Zudem besteht die richtige Datei nachher aus Formeln. Heißt, in den Zellen stehen keine Werte, sondern Formeln, die Werte stehen auf einem anderen Blatt. Damit wird das Sortieren noch schwieriger.
Zu dem zweiten Punkt: Es ist nicht richtig, dass die mit "X" markierten Einträge weg sollen, denn dann würde ein Teilnehmer, der mehrfach in der Liste ist und nur ein X hat, mehrfach stehen bleiben. Zudem sagt mir das X nachher, welche Einträge gewonnen haben. Das brauche ich also zwingend.
Ich habe einen Lösungsvorschlag erhalten, mit dem ich arbeiten kann. Ich verstehe ihn nicht ganz, aber daran arbeite ich noch.
Vielen Dank für Deine Mühe!
Anzeige
AW: zu Deiner Feststellung hier im Beitrag ...
16.07.2021 14:04:04
neopa
Hallo Mouna,
...

denn es ist mir - sicher aus Unwissenheit -  schon oft passiert, dass beim Sortieren nicht alle Spalten mit sortiert wurden
Das könntest Du zukünftig ein für alle mal vermeiden, wenn Du Deine Datenlisten mit der Funktion: "Al Tabelle formatieren" in sogenannte "intelligente" Tabelle wandelst.. Dann werden beim Sortieren immer alle Spalten dieser Tabelle (dies bitte nicht verwechseln mit Tabellenblatt) dieser Tabelle sortiert.
Gruß Werner
.. , - ...
Vor der Einführung der Strg-L-Tab. wusste man noch
17.07.2021 14:54:22
lupo1
a) Innerhalb der zu sortierenden Tabelle darf keine Zeile und keine Spalte komplett leer sein.
b) Vor und hinter der Tabelle (sowie drüber und drunter) hingegen befindet sich 1 Leerspalte(-zeile) (außer, wenn links/drüber keine mehr ist).
Dann ist das Excel-Sortieren in der Lage, den Sortierbereich jederzeit auch automatisch zu bestimmen, wenn man beim Klick auf Sortieren irgendwo in ihm drin steht (auf oder neben einer gefüllten Zelle!). Achtung: Auch hier Sonderfälle zu beachten!
Anzeige
AW: diese Überprüfung ist nur einmal notwendig ...
17.07.2021 16:06:33
neopa
Hallo,
... nämlich dann, wenn man die Liste in eine formatierte Tabelle mit Strg + T (was ich anstelle L bevorzuge) überführen will.. Mann kann aber auch ohne eine Tastenkombination händisch einen beliebigen Bereich (mit Leerzeilen und -spalte) markieren und diese mit der Menüfunktion als solche formatieren. Dann muss später nicht mehr kontrollieren, ob die Bedingungen für eine gewollte Sortierung erfüllt sind.
Gruß Werner
.. , - ...
2 Anmerkungen ...
17.07.2021 20:06:25
lupo1
1) Was ist der Unterschied zw. Strg-L und -T?
2) Manche mögen die Dinger nicht. Ich bin da etwas zwiegespalten. Und so schwierig ist das mit dem Sortierbereich ".CurrentRegion" auch nicht; man lernt früh, a) keine Feldnamenbezeichnung auszulassen und b) zumindest eine Spalte mit immer gefüllten Feldern pro Zeile vorzusehen. Wenn das bei Strg-L fehlt, gibt es keine Rückmeldung. Im klassischen Fall fliegt es einem um die Ohren. Und das ziehe ich vor.
Anzeige
AW: zu 1) kenne momentan keinen Unterschied.owT
18.07.2021 08:45:43
neopa
Gruß Werner
.. , - ...
Evolution
19.07.2021 10:28:22
Yal
Hallo zusammen,
es scheint so zu sein, dass früher das Setzen eines Filters mit Strg+L eingeleitet wurde (eigentlich kleines L).
Es scheint auch ein Ziel von MS zu sein, Datentabelle durchzusetzen: Strg+L führt genau wie Strg+t zu eine Datentabelle.
Und Filter sind jetzt mit Strg+Shift+L zu haben (Shift+L, also grosses L)
Man müsste mit ältere Xl-Version testen. Die habe ich aber nicht.
Das Begriff "intelligente Tabelle" ist das einzige was ich an dem Ding nicht mag.
Trivia: es liegt daran, dass auf Deutsch Tabelle anstatt "Blatt" verwendet wird und somit mit genug Abgrenzung wie auf Englisch zwischen "Sheet" und "Table" gibt. Daher muss man mit einem Zusatz differenzieren. Ich mag lieber "Datentabelle" als "inteligente Tabelle".
VG
Yal
Anzeige
oder: dynamische Tabelle
19.07.2021 13:00:53
lupo1
... in Abgrenzung zum dynamischen Bezug A1#.
AW: ist schon in XL2010 so owT
19.07.2021 13:37:19
neopa
Gruß Werner
.. , - ...
AW: Duplikate löschen mit Bedingung
15.07.2021 22:43:09
Günther
Na ja,
PQ ist schon eine gute Idee. Aber manchmal bedarf es doch des einen oder anderen Schrittes mehr, um (beispielsweise) den richtigen Andreas (18) zu behalten… (ich bewerte die Aussage, dass die x-Werte erhalten bleiben sollen höher als die numerischen Muster-Angaben ☺).
 
Gruß
Günther  |  mein Excel-Blog
AW: Duplikate löschen mit Bedingung
16.07.2021 09:33:17
Yal
HalloGünther,
der "richtige Andreas" wird mit dem x identifiziert. Durch die Sortierung vor dem Duplikatlöschen ist sichergestellt, dass der mit dem x übrig bleibt.
Da die Beschreibung von Mouna unvollständig war, müsste es erneut verabeitet.
VG
Yal
Anzeige
AW: Duplikate löschen mit Bedingung
16.07.2021 09:58:52
Mouna
Ich habe eine Lösung erhalten, vielen Dank!
AW: Duplikate löschen mit Bedingung
15.07.2021 21:52:35
onur
1, 5, 6, 16 ?
Die erste Zeile (Spaltenüberschriften) soll gelöscht werden ?
AW: Duplikate löschen mit Bedingung
15.07.2021 22:13:56
Mouna
Entschuldige, nein, es muss Zeile 2 sein.
AW: reicht Dir eine Hilfsspaltenlösung? ...
15.07.2021 22:37:25
neopa
Hallo Mouna,
... dann in F2 folgende Formel: =(ZÄHLENWENN(A:A;A2)&gt1)*(E2="")*ZÄHLENWENNS(A:A;A2;E:E;"X") und diese nach unten kopieren und mit dem Autofilter die 1 in dieser Spalte ausfiltern.
Gruß Werner
.. , - ...
AW: reicht Dir eine Hilfsspaltenlösung? ...
15.07.2021 22:44:45
GerdL
Das gleiche ohne Formel.

Sub Unit()
Dim Z As Long
For Z = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIfs(Columns(1), Cells(Z, 1), Columns(5), "x") > 0 Then
If WorksheetFunction.CountIf(Columns(1), Cells(Z, 1)) > 0 And IsEmpty(Cells(Z, 5)) Then
Rows(Z).Hidden = True
End If
End If
Next
End Sub
Gruß Gerd
Anzeige
AW: reicht Dir eine Hilfsspaltenlösung? ...
16.07.2021 09:20:28
Mouna
Danke Gernd! Das ist genial und tut genau, was ich mir gewünscht habe! Wenn ich das doch nur verstehen würde...
Wäre es Dir möglich, mir zu erklären, was wofür steht, damit ich es auf andere Dateien übertragen kann?
Gerne direkt im Skript oder wie es für Dich am einfachsten ist.
1.000 Dank!
AW: reicht dir eine pauschale Erklärung? ...
16.07.2021 09:37:11
GerdL
Wie schon geschrieben, Mouna, macht diese Prozedur das Gleiche wie die Formel von Werner.
Übersetze dir die Begriffe ins Deutsche u. stelle ggf. anschließend bitte gezielte Fragen, die noch übrig bleiben.
Gruß Gerd
AW: reicht dir eine pauschale Erklärung? ...
16.07.2021 11:35:24
Mouna
Folgendes habe ich verstanden:
For Z = 2 To Cells(Rows.Count, 1).End(xlUp).Row
bestimmt die Spalte als "B" und geht alle Zellen in der Tabelle von unten durch bis zur ersten Zeile, die einen Eintrag hat.
Richtig?
If WorksheetFunction.CountIfs(Columns(1), Cells(Z, 1), Columns(5), "x") > 0 Then ~f~</i>
Hier verstehe ich die ersten Einträge: Columns(1), Cells(Z, 1), nicht.
Columns (5) ist die Spalte, in der nach dem X gesucht wird.
Wenn das Ergebnis größer als 0 - und damit wahr - ist, dann zähle.
<i> ~f~ If WorksheetFunction.CountIf(Columns(1), Cells(Z, 1)) > 0 And IsEmpty(Cells(Z, 5)) Then

Hier verstehe ich wieder die erste Bedingung nicht: Columns(1), Cells(Z, 1)
Den Rest verstehe ich.
Wenn es Umstände macht, mir das zu erklären, ist es nicht nötig. Denn es funktioniert und ich weiß, wo ich ändern muss, wenn es um andere Spalten geht.
Da ich jedoch gern verstehe, was ich mache, frage ich nach.
Anzeige
AW: Bereich, Zellenwert
16.07.2021 11:43:05
GerdL
Nein, SpalteA, Wert von Zelle in SpalteA (deren Zeile bestimmt durch Zähler Z).
Gruß Gerd
AW: reicht dir eine pauschale Erklärung? ...
20.07.2021 18:07:45
Yal
Hallo Mouna,
Gerd hatte wohl wenig Zeit.
Ich erkläre gern jemandem, der verstehen möchte:

For Z = 2 To Cells(Rows.Count, 1).End(xlUp).Row
man benutzt eine Variable Z, die mit der Wert 2 initialisiert wird und (weil nicht spezifiziert) pro Schleife um 1 erhöht wird, bis diese den Wert nach dem "To" erreicht wird (das weisst Du aber schon).
Nach dem "To" ist die Funktion "Cells()". Diese nimmt die Parameter Zeile und Spalte (klicke auf das Wort Cells und drücke Strg+F1. Dann gelangst Du auf die Online-Hilfe für Cells). Hier wird Cells mit der Zeile "Rows.count" gerufen, spricht die letzte verfügbare Zeile in deiner Excel-Blatt (Version abhängig, sollte in xl2019 1.048.576 sein). Spalte ist 1, also Spalte A.
Dann wird End(xlUp) daruf angewendet, dass den Strg+Pfeil nach oben simuliert. Es wird dann -von unten kommend- die erste befüllte Zelle in Spalte A genommen.
".Row": davon die Zeilenummer.
Man hätte auch Cells(999999, "A").End(xlUp).Row schreiben können. Ist gleichwertig.
An die 2 anderen Stellen, wo "Cells" verwendet wird, ist es dasselbe: Z ist die Zeilenummer, die durch die For-Schleife hochgeht, zweite Parameter ist die Spaltenummer.
So. Klugscheisser-Modus aus ;-)
VG
Yal
AW: reicht dir eine pauschale Erklärung? ...
21.07.2021 16:02:20
Mouna
Yal, ganz lieben Dank für Deine Erklärung! Das hat mir schon sehr geholfen.
Eine Sache, die nicht klappt ist die forlaufende Nummer, die sich mit ausgeblendeten Zeilen aktualisieren soll.
Das tut sie leider nicht, wohl weil es kein Autofilter ist.
Gibt es eine Formel, die ich eintragen kann anstelle derer, die ich jetzt verwendet habe?

7 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige