Zeilen automatisch löschen

Forumthread: Zeilen automatisch löschen

09.01.2013 11:45:43

Stefan

Hallo zusammen,
ich habe eine Tabelle mit etwa 3000 Zeilen und 8 Spalten. Nun würde ich gerne erreichen, dass alle Zeilen die in 7 der 8 Spalten (Kriterien) einen identischen Inhalt haben, gelöscht werden und nur noch 1 Zeile übrig bleibt - und zwar jene mit dem höchsten Zahlenwert.
Ein Bsp. in Bezug auf die angeängte Testtabelle (zu finden unter: https://www.herber.de/bbs/user/83306.xlsx ):
- Die Spalte "Stadtgröße" soll diejenige Spalte sein, die nicht identisch sein muss.
- "Land" und "Richtung" sollen identisch sein.
- Es soll nur 1 Zeile für identsche Ausprägungen unter "Land" und "Richtung" übrig bleiben - und zwar jene mit dem höchsten Wert.
- Für die erste Kategorie "Land = Deutschland" und "Richtung = Nord" wäre dies, da der Wert für "Deutschland, Nord, Großstadt" am höchsten ist, die Zeile "Deutschland, Nord, Großstadt".
- Die Zeile "Deutschland, Nord, Kleinstadt, Anzahl" soll gelöscht werden.
- Zu beachten ist, dass die Tabelle nicht immer alle Ausprägungen aufführt, d.h. es können beispielsweise auch Zeilen komplett fehlen (siehe Dänemark, wo das Kriterium NORD komplett fehlt und für "Dänemark, West, Kleinstadt" nur eine Zeile vorhanden ist).
Bitte entschuldigt die komplizierte Erklärung. Ich hoffe mein Anliegen wird deutlicher, wenn ihr einen Blick auf das ebenfalls im Anhang eingepflegte Wunsch-Ergebnis werft.
Liebe Grüße
Stefan

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Zeilen automatisch löschen

09.01.2013 12:11:38

Klaus

Hi Steffan,
aufgrund deines Levels ohne VBA:
in deiner Beispieltabelle:
E3: =A3&B3
F2: "FILTER" (oder irgend eine Überschrift)
F3: {=MAXA(WENN(E:E=E3;D:D))=D3}
Matrixformeln: {} nicht mit eingeben, sondern Formel mit STRG+SHIFT+ENTER abschließen
markiere F3:F16 (bzw F3:letzte-Zeile-in-F) an, setze hier den Autofilter. Filtere alle Ergebnisse "WAHR". Kopiere die gefilterte Liste an einen Ort deiner Wahl.
Erklärung: in E erstelle ich mir einen Code, der alle Kriterien zusammenfasst. In F schaue ich, ob hier die höchste Zahl zu diesem Code vorliegt. (das geht einfacher, als 2-7 Spalten einzeln zu prüfen und in der Matrix zu verketten).
Grüße,
Klaus M.vdT.

AW: Zeilen automatisch löschen

10.01.2013 10:50:32

Stefan

Hallo Klaus,
danke für Deine Rückmeldung.
Ich hätte noch eine kleine Erweiterung:
Wenn der Unterschied zwischen dem höchsten und dem zweithöchsten Wert mehr als 10% ist, soll gelten, was bisher galt, sprich: die Zeile soll gelöscht werden.
Wenn der Unterschied zwischen dem höchsten und dem zweithöchsten Wert allerdings geringer als 10% ist, wäre gut, wenn eine manuelle Entscheidung getroffen werden könnte. Wäre es möglich, dass Zeile mit dem zweithöchsten Wert dann nicht direkt gelöscht wird, sondern z.B. gelb markiert wird (damit man diese visuell schnell erkennt und manuell eine Entscheidung fällen kann)?
Ich hoffe, ich konnte deutlich machen, was gemeint ist.
Herzlichen Dank und liebe Grüße
Stefan

AW: Zeilen automatisch löschen

10.01.2013 11:37:13

Klaus

Hi Stefan,
formuliere doch bitte die Aufgabe das nächste mal gleich vollstandig! Mein Matrixformel-Ansatz ist da kaum zu gebrauchen, damit ist die Energie die ich investiert habe um dir zu helfen jetzt für die Tonne, das frustriert.
Wenn der Unterschied zwischen dem höchsten und dem zweithöchsten Wert allerdings geringer als 10% ist, wäre gut, wenn eine manuelle Entscheidung getroffen werden könnte. Wäre es möglich, dass Zeile mit dem zweithöchsten Wert dann nicht direkt gelöscht wird, sondern z.B. gelb markiert wird (damit man diese visuell schnell erkennt und manuell eine Entscheidung fällen kann)?
Das eine Formel sowas nicht kann weisst du selber!
Ich würd da jetzt so vorgehen: (ich gehe davon aus, dass die Ursprungsliste nicht verändert oder sortiert werden darf).
in Vba ein Script schreiben dass folgendes macht:
- Alles in ein leeres Tabellenblatt kopieren
- mit =A3&B3 den Code anlegen oder gleich nach den benötigten Feldern in der Reihenfolge kopieren.
- alle größten Werte grün färben, alle zweitgrößten gelb, alle 3tt bis n-t größten Werte löschen (und eventuell dabei deine 10% Regel beachtet)
- neben jeden grünen Wert in einer freien Spalte das Wort "WAHR" schreibt
- eine Messagebox zeigt, in der steht: "Bitte entscheiden für die gelben Felder" oder so ähnlich.
Dann per Hand neben jedes Gelbe Feld WAHR oder FALSCH schreiben, oder einfach WAHR für jedes das gehalten werden soll.
ein neues Makro starten, welches:
- die Liste nach "WAHR" filtert
- die gefilterte Liste an die gewünschte Stelle kopiert.
Das mache ich aber jetzt nicht für dich, da deine Beispieldatei in Zeilen und Spalten von deiner Masterdatei abweicht und es dann eh nichts bringt. Du kannst aber die meisten der o.g. Vorgänge per Makrorekorder aufzeichnen, die Farbmarkierung bekommst du über Formeln (Matrix-Max-Wenn) selber hin.
Grüße,
Klaus M.vdT.

AW: Zeilen löschen mit Duplikate entfernen

09.01.2013 13:37:24

Daniel

Hi
sortiere die "Datei" nach "Land", Richtung und auf jeden fall nach "Anzahl".
nach Anzahl musst du Absteigend sortieren (so daß die grösste Zahl oben steht)
dann markierst du die Tabelle und wendest du die Funktion DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN an.
hierbei gibst du die Spalte mit "Land" und "Richtung" als Kriterium an.
für die Umsetztung als Makro kannst du gut den Recorder verwenden.
Gruß Daniel

AW: Zeilen löschen mit Duplikate entfernen

10.01.2013 10:52:04

Stefan

Hallo Daniel,
danke für die einfache und tolle Lösung. War ja doch so einfach... :-)
Allerdings habe ich Klaus eben noch eine Erweiterung vorgeschlagen. Bei dieser läuft Deine einfache Variante vermutlich leider fehl oder siehst du auch hier eine Möglichkeit?
Danke und Grüße
Stefan

AW: Zeilen löschen mit Duplikate entfernen

10.01.2013 13:05:55

Daniel

Hi
das kann man schon damit lösen, man muss dann halt noch kreativ werden und sich ein paar Formeln ausdenken, mit denen man in Hilfsspalten die Zellen entsprechend markieren kann.
das könnte in deinem Fall so aussehen:
(in Spalte A stehe das Land, in Spalte B die Richtung und in Spalte D der Wert)
1. sortieren nach A, B und absteigend nach D.
2. folgende Formel in Spalte E (für Zeile 2), diese Formel nummeriert die Einträge durch:

=Wenn(Und(A1=A2;B1=B2);E1+1;1)

3. folgende Formel in Spalte F (wieder für Zeile 2):

=Wenn(E>2;0;Wenn(E2=1;Zeile();Wenn(D2/D1>0,9;Zeile();0)))

diese Formel markiert alle Zeilen, die gelöscht werden sollen mit 0 und alle, die stehenbleiben sollen mit der Zeilennummer (einem eindeutigen Wert)
dh. der grösse Eintrag einer Gruppe (mit 1 in Spalte E) bekommet die Zeilennummer, alle anderen werte die 0. Für die zweitgrössten Werte wird je nach grösse Entschieden, ob 0 oder Zeilennummer in die Zelle kommt.
jetzt kannst du noch von Hand die Spalte F überprüfen und bei allen zweitgrössten Werten, bei denen in F die Zeilennumer steht, diese durch die 0 ersetzen, falls diese noch gelöscht werden sollen.
4. Schreibe jetzt noch in Zelle F1 die 0.
5. Markiere die ganze Tabelle und wende die Funktion DUPLIKATE ENTFERNEN an mit der Spalte F als Kriterium.
Gelöscht werden alle Zeilen, in denen die 0 steht (mit ausnahme der ersten Überschriftenzeile)
das geht auch schnell und einfach, man muss halt nur ein bissen selber denken und kreativ mit den vorhandenen Funktionen umgehen können.
Gruß Daniel

Infobox / Tutorial