Office: Zellen entfernen deren Inhalt nicht Element einer Menge ist

Guten Abend zusammen,

leider habe ich für folgendes Problem nach Internetrecherche und Forumsuche keine Lösung gefunden. Daher möchte ich die Hilfe dieses Forums in Anspruch nehmen.

Problemstellung: In einem Datensatz mit zwei Spalten soll jede Zeile entfernt werden, bei der der Inhalt der linken Zelle (in der ersten Spalte) kein Element der Inhalte aller Zellen der rechten Spalte ist.

Meine bisherigen Versuche bauten auf dem Autofilter auf. Dort habe ich versucht, mit dem benutzerdefinierten Filter und der Relation "entspricht" zu arbeiten. Leider habe ich keine Möglichkeit gefunden, "entspricht" alle zugelassenen Werte auf einmal mitzuteilen. Bisher habe ich es lediglich geschafft, mit "entspricht" auf einen Wert zu testen, nicht auf beiliebig viele gleichzeitig.

Ich hoffe, dass ich mich verständlich ausgedrückt habe und natürlich, dass man mir bei meinem Problem helfen kann.

Schon jetzt herzlichen Dank für die dabei entstandene Mühe.

Viele Grüße
Niklas

 

Hallo Niklas,

ob es da eine Lösung mithilfe von Formeln, dem Autofilter oder ähnlichem gibt, weiß ich nicht.

Aber ich könnte Dir ein Makro anbieten.
Je nachdem wieviele Daten Du in der Tabelle hast, kann es etwas dauern.
Und da ich Deine Tabelle nicht kenne, habe ich es allgemein gehalten und auch keinen "Auslöser" eingebaut.

Vielleicht hilft es Dir ja weiter.

Code:

Option Explicit

Dim loLetzte As Long        'letzte benutzte Zeile
Dim loZeileA As Long        'Zeile in Spalte A
Dim loZeileB As Long        'Zeile in Spalte B

Dim blVorhanden As Boolean  'Wahrheitswert für Vergleich



Public Sub ZeilenLöschen()
    
    With ThisWorkbook.ActiveSheet
        
        'ermittelt die erste leere Zelle in Spalte A
        loLetzte = .Cells(Rows.Count, 1).End(xlUp).Row
        
        'Schleife für jeden Wert in Spalte A
        For loZeileA = 1 To loLetzte
            
            'Schleife für jeden Wert in Spalte B
            For loZeileB = 1 To loLetzte
                
                'Vergleich ob Wert aus Spalte A in Spalte B vorkommt
                If .Cells(loZeileA, 1).Value = .Cells(loZeileB, 2).Value Then
                    blVorhanden = True
                    Exit For
                Else
                    blVorhanden = False
                End If
            
            Next loZeileB
            
            'wenn kein Treffer gefunden wurde:
            If blVorhanden = False Then
                'Zeile löschen
                .Rows(loZeileA).Delete Shift:=xlUp
                'Zeilenvariable um 1 zurücksetzen
                loZeileA = loZeileA - 1
            End If
            
            'beenden wenn nächste Zelle in Spalte A leer ist
            If .Cells(loZeileA + 1, 1).Value = "" Then Exit Sub
        
        Next loZeileA
    
    End With
    
End Sub

 

Re: Zellen entfernen deren Inhalt nicht Element einer Menge

Das hat allerdings zwei Haken:
- wenn in Spalte B einer gelöschten Zeile das Pendant zur Spalte A einer darüber liegenden Zeile steht, so bleibt am Ende ein Wert in Spalte A über, dem die Entsprechung in Spalte B fehlt.
- wenn eine Zeile gelöscht wird, könnte dadurch das Pendant eines Wertes aus Spalte A aus einer darunter liegenden Zelle gelöscht werden, so dass dadurch diese Zeile ebenfalls gelöscht wird, obwohl das Pendant am Anfang vorhanden war.

Ist das so beabsichtigt?
Oder meintest Du vielleicht:

 

Vielen Dank für Deine ausführlichen Antworten.

Zuerst zu Deiner Frage: Ja, es ist so beabsichtigt wie ich geschrieben habe (oder von mir aus nicht die ganze Reihe sondern das Wertepaar der zwei benachbarten Zellen). Allerdings macht es keinen Unterschied, wenn ich zuerst die rechte Spalte auf die nächste Tabelle kopiere und mit dieser dann meinen Vergleich durchführe.

Dadurch dürfte kein Konflikt mehr entstehen, dass ich etwas löschen möchte, was Kriterium des Löschvorgangs ist.

Nun etwas konkreter zu meinen vorliegenden Daten: Der Datensatz besteht aus ca. 250.000 ID-Paaren. Jede ID besteht aus einer Folge von 7 bis 10 Ziffern.

Ist das Makro damit für meinen Datensatz geeignet?

 

250000 ist mal ne Hausnummer...
Sorry, dafür kannste meine Lösung wohl leider vergessen.
Ich habe auf meinem (altersschwachen) Rechner mal gestoppt:
1000 Zeilen dauern etwa 24 sec.
10000 Zeilen habe ich nach 5 min. abgebrochen, und Excel war bei gut 3500 Zeilen...

Die Feinheiten an Deine Bedürfnisse anzupassen wär wohl nicht so das Problem, aber bei der Geschwindigkeit macht es keinen Sinn.

Das Problem ist, jede Zelle A muss mit jeder Zelle B verglichen werden, außer es wird vorher Übereinstimmung gefunden.
Das ergibt im Maximalfall 250000 * 250000 Vergleiche.

Ich wünsche Dir das sich jemand von den richtigen Auskennern hier meldet und Dir eine bessere Lösung bieten kann.

Als Tipp für die Zukunft:
Anforderungen, Datenmengen etc. direkt bei der Fragestellung so konkret wie möglich definieren.
Das spart Zeit.

 

Hallo Achim,

vielen Dank, ich nehme das für die Zukunft auf. Tut mir leid, wenn ich Dir dadurch vergebliche Mühe gemacht habe.

Kannst Du mir dennoch sagen, wie Dein Makro aussehen würde, wenn ich die o.g. Probleme vermeiden möchte. Ist es beispielsweise möglich, meine Spalte B zusätzlich in die Spalte D zu kopieren, damit den Vergleich durchzuführen und nachher nur die Zellen in Spalte A und Spalte B zu löschen?

Wäre das etwa folgendermaßen möglich (ich habe gegenüber Deinem Code lediglich eine kleine Veränderung beim Löschen durchgeführt):

Code:

Option Explicit

Dim loLetzte As Long        'letzte benutzte Zeile
Dim loZeileA As Long        'Zeile in Spalte A
Dim loZeileD As Long        'Zeile in Spalte D

Dim blVorhanden As Boolean  'Wahrheitswert für Vergleich

Sub Social_Graph_Filter()

    With ThisWorkbook.ActiveSheet
       
        'ermittelt die erste leere Zelle in Spalte A
        loLetzte = .Cells(Rows.Count, 1).End(xlUp).Row
       
        'Schleife für jeden Wert in Spalte A
        For loZeileA = 1 To loLetzte
           
            'Schleife für jeden Wert in Spalte D
            For loZeileD = 1 To loLetzte
               
                'Vergleich ob Wert aus Spalte A in Spalte D vorkommt
                If .Cells(loZeileA, 1).Value = .Cells(loZeileD, 2).Value Then
                    blVorhanden = True
                    Exit For
                Else
                    blVorhanden = False
                End If
           
            Next loZeileD
           
            'wenn kein Treffer gefunden wurde:
            If blVorhanden = False Then
                'Zeile löschen
                .Cells(loZeileA, 1).Delete
                .Cells(loZeileA, 2).Delete Shift:=xlUp
                'Zeilenvariable um 1 zurücksetzen
                loZeileA = loZeileA - 1
            End If
           
            'beenden wenn nächste Zelle in Spalte A leer ist
            If .Cells(loZeileA + 1, 1).Value = "" Then Exit Sub
       
        Next loZeileA
   
    End With

End Sub

Auch wenn der Algorithmus nicht sehr effizient ist, würde es mir schon helfen, wenn er ohne Konflikte funktioniert. Ich bin Student und arbeite mit diesen Daten im Rahmen eines Projekts, dass ich zusammen mit einem Institut bearbeite. Dadurch habe ich auch Zugang zu großer Rechenleistung, wenn nötig.

Viele Grüße
Niklas

 

Hallo!

Autofilter war schon eine gute Idee, dann allerdings mit Hilfsspalte:

=ZÄHLENWENN(B:B;A2)=0

ergibt WAHR, wenn A2 nicht in Spalte B auftaucht.

Also dann die Hilfsspalte nach FALSCH filtern.

 

Hallo,

danke, die Idee hat mir sehr geholfen! Zum Glück konnte ich auf einen kleinen Trick zurückgreifen, denn mit der o.g. Methode hätte Excel ja tatsächlich 250.000^2 Vergleiche durchgeführt.

Das hat mein Rechner aber nicht mitgemacht. Allerdings konnte ich aus der rechten Spalte ca. 2000 verschiedene Werte isolieren (sprich, in den mehr als 250.000 Werten der rechten Spalte verbirgen sich nur 2000 verschiedene).

Leider kann ich über die linke Spalte keine Aussage treffen. Somit überprüfe ich jetzt mit Deiner Methode, schatzi, für jeden der 250.000 Werte der linken Spalte, ob er in einem der 2000 Werte enthalten ist. Anstatt 250.000^2 Vergleiche jetzt also nur no 250.000 * 2000.

Das ist zwar auch ganz schön happig, lässt sich aber mit etwas Geduld noch am eigenen Computer erledigen.

Vielen Dank nochmal an Euch beide!

Viele Grüße
Niklas