Microsoft Excel

Herbers Excel/VBA-Archiv

Zeilen löschen wenn Werte nicht vorhande

Betrifft: Zeilen löschen wenn Werte nicht vorhande von: Andreas
Geschrieben am: 13.08.2020 14:21:12

Hallo Excelfreunde,

Ich benötige mal wieder Eure Hilfe.

Folgende Ausgangssituation:
In Tabelle1 Spalte“A“ stehen Projektnummern die 1x, doppelt aber auch mehrfach vorhanden sein können.
In den Spalten „C“ und „D“ stehen die entsprechenden Werte dazu.
In Tabelle2 Spalte“B“ stehen dann Projektnummern (eindeutig und nur einmal vorhanden)

Jetzt möchte ich gern über ein Makro realisieren das die in Tabelle1 in der Spalte „A“ stehenden Projektnummern mit den Projektnummern welche in der Tabelle2 Spalte “B“ stehen verglichen werden.
Es sollen alle ZEILEN (Datensätze) in Tabelle1 komplett gelöscht werden wenn deren Projektnummern in Tabelle2 Spalte „B“ NICHT vorhanden sind.
Folgender Hintergrund: In Tabelle1 sind ca. 248.000 Zeilen (Datensätze) die anschließend durch SVERWEIS oder wie ich es bereits gelöst habe

'=WENNFEHLER(AGGREGAT(15;6;Tabelle1!$C$2:$C$165386/($B5&C$4=Tabelle1!$A$2:$A165386&Tabelle1!$B$2:$B$165386);1);"")

In Tabelle2 aufbereitet und eingelesen werden sollen.
Ich musste schon die automatische Berechnung in Excel deaktiviern und auf manuelle Berechnung umschalten weil die Aktualisierung sonst ewig gedauert hätte.
Selbst bei der manuellen Berechnung wenn ich die Datei speichere dauert der Vorgang mehre Minuten (20- über 40 min.) um die große Datenmenge abzuarbeiten.
Jetzt würde ich gern die Datensätze in Tabelle1 auf ein Minium reduzieren.
Dabei sollen ALLE ZEILEN komplett gelöscht werden deren Projektnummern NICHT in Tabelle2 Spalte „B“ vorhanden sind
Wenn in Tabelle1 Spalte „A“ die Projektnummer (welche auch immer mehrmals vorkommen kann) weil dies auch so gewollt ist, in Tabelle2 Spalte „B“ auch vorhanden ist sollen diese aber alle bestehen bleiben.
Also wirklich nur die Zeilen in Tabelle1 löschen deren Projektnummern nicht in Tabelle2 Spalte „B“ vorhanden sind
Ich habe mal eine absolut abgespeckte Datei mit hochgeladen wie das am Ende aussehen soll.
Originaldatei hat im xlsx Format ca. 53 MB und in Tabelle1 ca. 248.000 Datensätze

Ich hoffe das ich das einigermaßen verständlich rüber gebracht habe wie ich das gern haben würde.

Vielleicht kann mir einer von Euch dazu einen Code schreiben der mir die ZEILEN automatisch löscht.
Habe bei meinen Recherchen im Internet nicht wirklich passendes dazu gefunden.

hier noch meine Datei dazu:
https://www.herber.de/bbs/user/139612.xlsb

Danke bereits im Voraus für Eure Unterstützung
Gruß Andreas

Betrifft: AW: Zeilen löschen wenn Werte nicht vorhande
von: UweD
Geschrieben am: 13.08.2020 14:35:31

Hallo

- füge auf Tabelle1 hinten eine Hilfsspalte ein. Beispiel E

- Formel in E2 =ZÄHLENWENN(Tabelle2!B:B;A2)

- setze dann eine Filter auf diese Spalte und lass dir nur die 0 anzeigen.
- Alle gezeigten Zeilen ab Zeile 2 löschen
- Hilfsspalte löschen

Kannst du auch in einem Makro so machen


LG UweD

Betrifft: AW: Du hast den Vorgängerthread nicht verlinkt ...
von: neopa C
Geschrieben am: 13.08.2020 14:36:54

Hallo Andreas,

... ich meine jedenfalls die Tabelle zumindest schon gesehen zu haben. Hatte ich im vorherigen thread nicht schon darauf verwiesen, dass dies bei derartiger Datenmenge anstelle mit Formel auch mittels Pivotauswertung zu lösen ist bzw. auch mit PowerQuery? Wenn nicht, dann tue ich es jetzt.

Gruß Werner
.. , - ...

Betrifft: AW: Du hast den Vorgängerthread nicht verlinkt ...
von: Andreas
Geschrieben am: 13.08.2020 15:45:26

Hallo UweD,
Danke erst einmal für die schnelle Antwort
habe es mit der Formel
'=ZÄHLENWENNS(Tabelle2!B5:B16;A2)probiert da erscheint bei allen Einträgen eine 1
'=ZÄHLENWENN(Tabelle2!B5:B16;A2)probiert da erscheint bei allen Einträgen eine 0

beide Varianten führen nicht zum Ergebnis.

Hallo Werner,

Ja du hattest mich schon darauf hingewiesen.
Aber ich probiere seit Tagen an einer Lösung und komme nicht weiter.
Dein Vorschlag scheint vielleicht gut gemeint zu sein. Nur kann ich ihn nicht so umsetzen das ich meine weitere Vorgehensweise damit realisieren kann.
Ich benötige eigentlich diese Methode damit ich in Tabelle2 genau diese Darstellung bekomme und dann die Daten so wie sie angezeigt werden 1:1 in eine andere Datei an die vorgegebene Stelle wieder eingefügt werden.
Vielleicht für andere nicht unmittelbar gut verständlich aber die Datei welche von ca. 150 Usern täglich bearbeitet werden muß hat auch eine Größe von ca. 12 MB und wird durch Eingaben über eine UserForm in Abhängigkeiten gefüllt.
Ursprünglich wollte ich ja die Daten in dieser Datei verküpfen und über SVERWEIS aktualisieren.
Leider ist die Ausgangasdatei aus SAP ca. 52 MB groß und mit dieser Masse an Zeilen so für mich nicht mehr Händelbar.
Deshalb brauche ich unbedingt diese Zwischenlösung. Ich bin mir klar das dies auch nicht der perfekte Weg sein kann aber mit meiner Kenntnis stoße ich jetzt an Grenzen


Vielleicht kann mir doch noch einer Helfen. Habe die Datei so 1:1 hochgestellt nur mit halt viel weniger Datensätzen.
In der Spalte "B" in der Tabelle2 werden es max.800 Datensätze die zu bearbeiten und auszulesen wären

Gruß Andreas

Betrifft: vor Tagen: filter?
von: Fennek
Geschrieben am: 13.08.2020 16:13:39

Aus dem Gedächtnis:

Vor einigen Tagen habe ich für diese Frage (?) einen Code mit Array "Filter" geschrieben. In meinen Unterlagen konnte ich ihn nicht mehr finden.

Es war sehr einfach:

Alles in ein Array kopieren, alle Spalten in ein 1-d Array, dann nach jeder der ID des 2. Blatts filtern.

Wie bereits gesagt,verlinke bitte den alten Beitrag!

mfg

Betrifft: AW: dann eben nur eine PQ-Lösung ...
von: neopa C
Geschrieben am: 13.08.2020 16:22:33

Hallo Andreas,

... mit einer solchen bedarf es als Teillösung 1 keine Dutzend interaktive Schritte (zur Aufstellung) um die Datensatzanzahl der Beispieldatei in Tabelle1 von 2106 auf 101 zu reduzieren. Dieser Lösung ist es egal ob 2106 oder 1Mio Datensätze in Tabelle1 stehen und in Tabelle 2 ca 650 Projektnummern und nicht wie in der Beispieldatei 12 Nummern. Die Pivotisierung lässt sich auf dieser Basis dann sicher auch mit PQ realisieren (hab ich mir jetzt noch nicht näher angeschaut mit welche Schritten)

Nach Datenänderungen/Erweiterung in beiden Tabellenblättern bedarf es dann lediglich eines Mausklicks zur Aktualisierung der Auswertung.

Gruß Werner
.. , - ...