Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1856to1860
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Abgleich von 2 Spalten

Abgleich von 2 Spalten
13.11.2021 17:01:21
2
Hallo zusammen,
ich bin nahezu neu hier. Ein Kollege hat mir schon einmal sehr geholfen. Das meiste, was ich über VBA weiß, habe ich hier erfahren. Dafür möchte ich mich hier allgemein auch (noch) mal sehr herzlich bedanken.
Ich habe nun mit Eurer aktiven und passiven Hilfe eine VBA Prozedur erstellt, welche Artikelnamen in 2 Spalten in A und in D vergleicht. In Spalte C kommt als Ergebnis der Prozedur "neu" oder "vorhanden" heraus.
In der Tabelle werden nur 9 in Spalte A mit 5 Datensätzen in Spalte B verglichen. Eigentlich muss ich hier mit circa 20.000 Datensätzen arbeiten. Daher arbeite ich mich auch an Arrays heran.
Meine 1. Von 2 Frage ist, was denkt der Profi von Euch über meine Code, was muss ich verbessern?
Die 2. Frage wäre, ob es bei den beiden FOR-NEXT - Schleifen egal ist über welche Spalte ich zuerst gehe? Wenn ich später über Zeilen und Spalten gehe, ist es egal, ob ich zuerst über die Zeile und dann über die Spalten gehe? Ich frage auch in Hinsicht auf die spätere, gewünschte Verwendung von Arrays.
Vorab vielen, vielen Dank an jene, die mir helfen möchten bzw. können! Ich bin offen für gut gemeinte Kritik und Anregungen.
Mit besten Grüßen zum Wochenende
Robert
https://www.herber.de/bbs/user/149128.xlsb
Mein VBA-Code:

Sub ZweiSpaltenAbgleichen()
Tabelle3.Activate
'***** zellenweiser Abgleich
Dim iNeu2 As Integer, jAlt2 As Integer
Range("B:B").ClearContents
Dim aLast As Integer
Dim dLast As Integer
aLast = Range("A1").CurrentRegion.Rows.Count
dLast = Range("D1").CurrentRegion.Rows.Count
For jAlt2 = 2 To aLast
For iNeu2 = 2 To dLast
If Cells(jAlt2, 1)  Cells(iNeu2, 4) Then
Range("A1").Offset(jAlt2 - 1, 1).Value = "neu"
Else
Range("A1").Offset(jAlt2 - 1, 1).Value = "vorhanden"
Exit For
End If
Next iNeu2
Next jAlt2
'***** / zellenweiser Abgleich
End Sub

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Abgleich von 2 Spalten
13.11.2021 17:22:12
2
Hi Robert,
Arrays sind natürlich i.d.R. die schnellsten Varianten.
Aber vorher kannst Du auch schon deutlich mehr Tempo machen:
Bisher "klapperst" Du für jeden Eintrag aus Spalte A die Einträge in Spalte D mittels For-Next-Schleife einzeln ab, um zu prüfen, ob bereits vorhanden oder nicht.
Das ist bei vielen Daten sicher ein echter Zeitfresser.
In folgendem Beispielcode habe ich für dafür die Find-Methode verwandt - die ist recht flott.
Zusätzlich bekommst Du mehr Tempo, wenn Du zu Beginn des Codes das Screenupdating sowie die automatische Berechnung aus- und am Ende wieder einschaltest.
Und ein Tipp zur Variablendimension: Integer geht bis 32768 - Excel hat aber 1 Mio. Zeilen. Das kann dann schnell zum Überlauf führen.
Also: Zeilenvariablen immer als Long deklarieren.

Sub ZweiSpaltenAbgleichen()
Dim x As Long, lngLast As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
Tabelle3.Activate
Range("B:B").ClearContents
lngLast = Range("A1").CurrentRegion.Rows.Count
For x = 2 To lngLast
If Not Range("D:D").Find(what:=Cells(x, 1), LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then
Cells(x, 2) = "vorhanden"
Else
Cells(x, 2) = "neu"
End If
Next x
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
So viel erstmal für den Anfang.
VG, Boris
Anzeige
AW: Abgleich von 2 Spalten
14.11.2021 12:42:53
2
Hallo Boris,
danke Dir sehr für Deine prompte und geschmeidige Antwort. Das läuft wunderbar. Sehr gut.
Eine Frage hätte ich noch: Ich versuche gerade Arrays zu verwenden und habe daher eine Frage. Vielleicht hast Du eine Antwort für mich parat.
Wenn ich in einem 2d Array über Zeilen und Spalten suche oder gehe, gibt es eine allgemeine Regel? Gehe ich zuerst über die Zeilen oder zuerst über die Spalten? Oder ist das situationsabhängig?
In einem der nächsten Schritte muss ich die Artikelnamen auflösen. So ist der der Artikelname in D2 sehr wohl bereits in A4 vorhanden, wenn man die Reihenfolge der einzelnen Bestandteile des Artikelnamens nicht beachten würde. Da wurde bei der Eingabe nur einfach nicht auf die Reihenfolge geachtet. Davor graut es mir nun bereits.
Danke Dir aber noch einmal vielmals für alle Deine Bemühungen und Deine Zeit.
Beste Grüße
Robert
Anzeige
AW: Abgleich von 2 Spalten
14.11.2021 16:41:04
2
Hi Robert,
zur Verwendung von Arrays google mal ein wenig - z.B. (ungetestet bzw. ungelesen):
https://de.myservername.com/excel-vba-array-array-methods-with-examples
Zu Deinem 2. Punkt "Artikelname nicht exakt sondern nur enthalten" gibt es auch im begrenzten Bereich VBA-Lösungen - einfache mit dem Like-Operator
https://docs.microsoft.com/de-de/office/vba/language/reference/user-interface-help/like-operator
und wenn`s komplizierter wird auch mit den sogenannten Regular Expressions (RegExp)., z.B. hier nachzulesen:
https://qastack.com.de/programming/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops
Dazu muss man aber mal Deine Datensätze sehen.
VG, Boris
Anzeige
AW: Abgleich von 2 Spalten / Teilstrings
15.11.2021 14:28:25
2
Hallo Boris,
vielen, vielen Dank für Deine Hilfe und Deine Links.
Ich habe mir diese angesehen, sie haben mir sehr geholfen. Apropos Datensätze, diese Datensätze sind in Ihrer Struktur identisch mit den Dir bereits bekannten. Ein Artikelname besteht aus einem String, bestehend aus maximal 8 Teilen/Teilstrings.
Nach dem Lesen Deiner Links, habe ich die Dir bekannten Daten ALT und NEU in der beigefügten über die Split-Fkt. in jeweils ein Array eingespielt. Noch einmal herzlichen Dank für Deine hilfreichen Links.
Die abzugleichenden Datensätze NEU sind in Spalte D. Von diesen 6 Datensätzen sind
(Grün) 2 vorhanden, aber die Teilstrings haben eine andere Reihenfolge als in ALT -- soll gefunden werden
(Rot) 2 sind in ALT nicht vorhanden -- soll NICHT gefunden werden -- und
(Schwarz, Fett) 2 sind in identischer Reihenfolge vorhanden. -- soll gefunden werden
Nun muss ich wahrscheinlich versuchen, zeilenweise in den Arrays einen positiven Abgleich durchzuführen für Datensätze welche vollkommen identisch sind und solche welche unter Vernachlässigung der Reihenfolge der Teilstrings identisch sind.
Ist dies überhaupt zu machen? Traue ich mir zu viel zu? Mit Instr führt dies anscheinend nicht zum Erfolg. Deshalb habe ich über die Split-Fkt. die beiden Daten ALT und NEU in einzelne Arrays überführt. Wie ich aber hier durchloope, ist mir im Moment ein Geheimnis. Das Bisherige hätte ich ohne Deine Links aber nicht geschafft. Danke noch einmal.
Mit besten Grüßen
Robert
https://www.herber.de/bbs/user/149150.xlsb
Mein bisheriger Code

Sub ZweiArraysAbgleichen()
Tabelle3.Activate
'***** 1. Array Daten ALT
Dim ZelleALT As String
Dim splitStringALT() As String
Dim myDataArrayALT(1 To 20, 1 To 9) As String
Dim iALT As Integer, jALT As Integer
Dim laRowALT As Integer
laRowALT = Range("A1", Range("A2").End(xlDown)).Rows.Count
'            'Split strings to arrayALT including TRIM and CLEAN functions
For iALT = 1 To laRowALT
ZelleALT = WorksheetFunction.Clean(WorksheetFunction.Trim(Range("A" & iALT + 1).Value2))
splitStringALT = Split(ZelleALT, " ")
For jALT = 0 To UBound(splitStringALT)
myDataArrayALT(iALT, jALT + 1) = splitStringALT(jALT)
Next
Next
'***** 2. Array Daten NEU
Dim ZelleNEU As String
Dim splitStringNEU() As String
Dim myDataArrayNEU(1 To 4, 1 To 9) As String
Dim iNEU As Integer, jNEU As Integer
Dim laRowNEU As Integer
laRowNEU = Range("D1", Range("D2").End(xlDown)).Rows.Count
'Split strings to arrayNEU including TRIM and CLEAN functions
For iNEU = 1 To laRowNEU
ZelleNEU = WorksheetFunction.Clean(WorksheetFunction.Trim(Range("D" & iNEU + 1).Value2))
splitStringNEU = Split(ZelleNEU, " ")
For jNEU = 0 To UBound(splitStringNEU)
myDataArrayNEU(iNEU, jNEU + 1) = splitStringNEU(jNEU)
Next
Next
'**** Hier sollte nun der Abgleich erfolgen und in Spalte B und oder E eine Meldung erfolgen, _
aehnlich der bestehenden Anmerkungen in Spalte F ob der Artikelname _
aus D (2. Array Daten NEU) in A (1. Array Daten ALT) gefunden wurde.
End Sub

Anzeige
Vorschlag
15.11.2021 14:44:33
{Boris}
Hi Robert,
...eröffne dafür einen neuen Beitrag, denn hier schaut außer mir keiner mehr vorbei. Setze im Beitrag einen Link zu diesem Thread, damit man sieht, wo das Problem herkam. Verlinke die Beispielmappe nochmals in Deinem neuen Thread.
Ich denke, dass das nicht trivial ist, aber möglicherweise über die RegExps gelöst werden kann - ggfls. auch mit Power Query.
VG, Boris

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige