Wie kann man mit Visual Basic eine Tabelle Zeile für Zeile durchgehen und dabei die Werte einer Spalte prüfen, z.B. ob diese doppelt in der Tabelle vorkommen?
2 Antworten
Ich sehe 3 Möglichkeiten:
Variante 1:
Der pragmatische Ansatz wäre die Funktion "Duplikate Entfernen". Mit VBA kannst du das mit Hilfe von:
ActiveSheet.Range("$A$2:$A$11").RemoveDuplicates Columns:=1, Header:=xlNo
machen. Dabei ist ein wichtiger Punkt die Frage, was mit den übrigen Zeilen passieren soll. Liegt ein Wert in Spalte A doppelt vor, sind die anderen Zellen dann identisch, oder können Spalten B bis ... unterschiedlich sein, du nimmst aber immer nur die erste gefundene? Es gibt verschiedene Optionen, dass du ganze Zeilen entfernen kannst, auch wenn diese "nicht identisch sind". Wenn also Spalte A doppelte Zeilen enthält, diese Zeilen in Spalte B, C, .. aber unterschiedlich sind, kann die Funktion dennoch diese als Dupplikate komplett entfernen.
Auf deine Frage wäre also der einfache Weg: Alles kopieren, nach Spalte A sortieren und dann duplikate entfernen.
Variante 2:
Kannst du dir nicht eine zusätzliche Spalte erzeugen (z.B. A, welche dann die "darüberliegenden" Werte auf vorkommen des Wertes aus der Zeile überprüft
=IFERROR(MATCH(B2;$B$1:B1;0);"-1") //englische Version
Das Ergebnis ist eine Zahl >0, wenn der Wert bereits einmal in einer Zeile darüber vorgekommen ist. Die Zahl ist die Reihe des Bereichs. Wurde kein Wert gefunden, gibt die Formel einen Fehler aus und der Fehler wird mit "-1" ersetzt. Dann musst du im VBA nun also nur noch die Spalte A auf vorkommen von -1 prüfen um Zeilen mit Werten zu finden, die bisher nicht vorkamen.
Variante 3:
Willst du alles in VBA machen, kommst du um die bereits angesprochenen doppelten Schleife nicht herum. Ich würde dabei jedoch vorschlagen, dass du dir ein "lookup"-Array anlegst, und den Wert immer mit den Werten im Array überprüfst.
Pseudocode:
lookupArray = {}
targetRow = 1;
For row x = 1 to n
value = cell (A,X);
found = false;
for lookupIndex = 1 to lookupIndex.last
if (lookupArray[lookupIndex] = value) then
found = true;
end if;
next lookupIndex;
if found = false then
copyRow x to targetRow;
targetRow = targetRow+1;
add value to lookupArray;
end if;
Für eine vollständig korrekte Lösung in VBA habe ich mir die Zeit gespart, du musst also selber noch die korrekten Befehle zusammenfügen.
Warum dieser Vorschlag?
Wenn VBA auf Zellen im Arbeitsblatt zugreifen muss, ist der Code ziemlich langsam. Wenn du dir das duplikate-Array also im Speicher von VBA selber aufbaust und in VBA abfragst, ist es erheblich schneller.
Vielen Dank für die ausführliche Erklärung, habe es teilweise schon umgesetzt, erstmal ohne look up Array aber irgendwie passiert gar nichts beim ausführen
Was für eine Tabelle? Excel? SQL? Array?
Generell:
Erzeuge dynamische Liste vom gewünschten Datentyp.
Schleife von 1 (oder 0, falls VB.NET) bis Anzahl Zeilen (-1, falls VB.NET). Dann Wert aus Spalte der Tabelle holen. Wenn Wert bereits in dynamischer Liste => Doublette. Wenn nein: Wert in dynamische Liste einfügen und Schleife fortsetzen.
Alternativ (langsam): Schleife wie oben, Wert aus Spalte holen, dann weitere Schleife über alle Zeilen der Tabelle. Wenn Wert in einer Zeile vorkommt wo Schleifenindex1 <> Schleifenindex2, dann Doublette, sonst nicht.
Also ich verstehe das so. Du kopierst Zeilen aus einem Tabellenblatt1 in ein anderes Tabellenblatt2, aber Du willst eine Zeile aus Tabellenblatt1 nur kopieren, wenn sie im Tabellenblatt2 noch nicht vorkommt?
Dann musst Du das machen, was ich oben schon sagte, nämlich eine Doppelschleife. Mein Visual Basic ist ein wenig eingerostet, aber Du wirst schon verstehen, was ich meine:
Dim zeileKopieren as Boolean = True
For Zeile = 2 To ZeileMax
For Zeile2 = 1 to ZeileMaxInTabelle2
IF .Calls(Zeile, 140).Value = <EntsprechenderWertAusZeileInTabelle2> THEN
zeileKopieren = False
END IF
NEXT
IF zeileKopieren THEN
<KopiereZeileInTabelle2>
END IF
NEXT
fast richtig verstanden. Ich kopiere die Zeile aus tabellenblatt1 nur wenn diese Zeile bzw. der Wert einer Schlüsselspalte noch nicht im tabelleblatt1 vorkommt. Es sollen Duplikate quasi aus Tabellenblatt1 heraus gefiltert werden.
Schlüsselspalte in Tabelle 1
1 -> wird kopiert
2 -> wird kopiert
3 -> wird kopiert
4 -> wird kopiert
1 -> wird nicht kopiert, da die 1 oben schon vorkommt
3 -> wird nicht kopiert, da die 1 oben schon vorkommt
Ist es nicht das gleiche, ob Du in Tabelle1 oder Tabelle2 schaust, ob der zu kopierende Wert schon da ist?
Du kannst obigen Code ja entsprechend abwandeln, so dass die innere Schleife ebenfalls auf Tabelle1 schaut. Wichtig ist dann aber, dass Du auch noch prüfst, das Zeile und Zeile2 unterschiedliche Werte haben. Ansonsten findest Du nur Duplikate :-)
Ja, das stimmt. Aber ich komme irgendwie nicht drauf wie ich sagen, wie z.B. in SQL .Cells(Zeile, 140).Value IN Menge.
Diese Menge soll aus allen Werten der Spalte bestehen ausgeschlossen der ausgewählte Wert.
Ich programmiere sonst nur Java oder C#. Hab noch nie Visual Basic seit heute programmiert:-)
Naja, weil es auch nicht geht :-) Du benötigst in jedem Fall eine doppelte Schleife. Die äußere durchläuft alle möglicherweise zu kopierenden Zeilen, eine innere schaut für jede konkrete äußere Zeile nach, ob sie zu kopieren ist oder nicht.
Umgesetzt:-) Vielen Dank für die Hilfe!:-)
For Zeile = 2 To ZeileMax
For Zeile1 = 2 To Zeile - 1
If .Cells(Zeile, 140).Value = .Cells(Zeile1, 140) Then
zeileKopieren = False
Else
zeileKopieren = True
End If
If zeileKopieren Then
.Rows(Zeile).Copy Destination:=Tabelle2.Rows(n)
n = n + 1
End If
Fehlen hier nicht "Next Zeile" und "Next Zeile1"?
Dein Fehler ist, dass du zeileKopieren in jeder Überprüfung zwischen Zeile und Zeile1 setzt. d.h., dass am Ende der For Zeile2-Schleife die überprüfung zwischen Zeile und der letzten Zeile2 (Zeile-1), also der Zeile davor entscheidet, ob kopiert werden soll oder nicht. Was du stattdessen suchst ist eine Antwort auf die Frage, ob in Zeilen 2 bis Zeile-1 mindestens einmal ein duplikat vorkommt, es also kopieren = false gibt.
Änderungsvorschlag also:
For Zeile = 2 To ZeileMax
//neuer Befehl:
zeileKopieren = TRUE
For Zeile1 = 2 To Zeile - 1
If .Cells(Zeile, 140).Value = .Cells(Zeile1, 140) Then
zeileKopieren = FALSE
//wurde zeile KOpieren einmal auf
//FALSE gesetzt, kann es nicht mehr
//auf true gesetzt werden.
End if
next Zeile1
If zeileKopieren Then
...
end if
Next Zeile
Hey, vielen Dank für deine Antwort, habe es jetzt geschafft eine Excel-Tabelle von oben bis unten zu durchlaufen zu wenn etwas eintritt, die jeweilige Zeile n in eine neue Tabelle zu kopieren.
Jetzt habe ich nur noch ein Problem.
Ich will sagen, dass wenn der Inhalt der jeweils markierten Zelle in der Spalte DZ noch einmal vor kommt, dass dann diese nicht mit in die neue Tabelle kopiert wird.
Wie kann ich dies umsetzen?
Hier mein bisherigen Programmcode:
For Zeile = 2 To ZeileMax
If .Cells(Zeile, 140).Value <> "Hier muss noch was hin..." Then
.Rows(Zeile).Copy Destination:=Tabelle2.Rows(n)
n = n + 1
End If
Die jeweiligen Variablen wurden oben definiert.