Anzeige
Archiv - Navigation
1640to1644
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

Problem mit VLOOKUP und Strings mit 0

Problem mit VLOOKUP und Strings mit 0
03.09.2018 10:10:38
Jörn
Hallo zusammen,
als Neuling möchte ich mich erstmal bei allen bedanken, die in den Foren dieser Welt ihr geheimes Druidenwissen mit Allen teilen. Ich habe in den letzten Jahren sehr viel lernen können.
Nun habe ich aber ein Problem bei dem ich mir seit 2 Tagen die Finger wund suche und teste.
Folgende Ausgangssituation:
PCs mit Windows 7 oder 10, Office 2010 oder 2016.
Ich will Daten-Tabellen mit bis zu 50Mio 3-stelligen Strings mit einer Vergleichstabelle korrellieren. Die String sind alphanumerisch, d.h. sie können die Form 5HT, J8T usw. haben. Ich schreibe immer eine Anzahl von um die 180 Strings in ein ein String-Array (ergibt sich aus dem Aufbau der Daten-Tabelle). Den Abgleich mache ich bisher über eine SQL-Abfrage, hab aber hier -> https://analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/

Dim arrPRno(), arrFamilies() as String
Dim x, a as Long
Dim result as String
For x = 2 To myWks.Range("M:M").End(xlDown).Row
' Zellinhalt der Daten-Tabelle wird aufgeteilt und in Array geschrieben
arrPRno = Split(Trim(UCase(myWks.Cells(x, 13).Value)), Trennzeichen)
For a = LBound(arrPRno) To UBound(arrPRno)
If Application.VLookup(arrPRno(a), _
myWbk.Worksheets("Familien").Range("A1:A400").Value, 1, True) = arrPRno(a) Then
result = Application.VLookup(arrPRno(a), _
myWbk.Worksheets("Familien").Range("A2:B400"), 2, True)
' Falls der Wert noch nicht vorhanden ist, wird er weggeschrieben
If UBound(Filter(arrFamilies, result, True, vbTextCompare)) = -1 Then
arrFamilies(UBound(arrFamilies)) = result
ReDim Preserve arrFamilies(UBound(arrFamilies) + 1)
End If
End If
Next a
ReDim Preserve arrFamilies(UBound(arrFamilies) - 1)
Next x
Ich hoffe, jemand kann mir weiterhelfen.
Vielen Dank schon mal für eure Mühe!
Beste Grüße
der Jörn

2
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Problem mit VLOOKUP und Strings mit 0
03.09.2018 10:58:13
daniel
HI
SVerweise/VLookUp mit 4. Parameter = Wahr/True sind erheblich schneller als die Variante mit 4. Parameter = Falsch/False, vorallem bei sehr großen Datenmengen.
allerdings muss man folgendes beachten:
1. die durchsuchte Liste muss aufsteigend sortiert sein.
2. sucht man nach einem nicht vorhandenen Wert, so bekommt man keine Fehlermeldung, sonderen es wird der nächstkleinere Wert als Ergebnis zurückgerufen.
3. Einen Fehler bekommt man nur dann, wen man nach einem Wert sucht, der kleiner ist als der erste Wert in der durchsuchten Liste.
das unter 3. aufgeführte Problem kann man auf 2 Wegen umgehen:
a) man prüft vor dem SVerweis, ob der gesuchte Wert größer oder gleich dem ersten Wert in der durchsuchten Liste ist und führt den SVerweis nur in diesem Fall aus
b) man fügt der durchsuchten Liste am Anfang eine Zeile mit dem Wert 0 hinzu, so dass man auf jeden Fall als ersten Listeneintrag einen Wert hat, der kleiner ist als der kleinstmögliche gesuchte Wert.
wenn man trotzdem eine Suche nach genauer Übereinstimmung durchführen will, dann führt man vorab als Prüfung einen SVerweis/VLookUp durch mit der ersten Spalte als Rückgabewert und vergleicht das Ergebnis mit dem gesuchten Wert. Stimmen beide überein, ist es ein Treffer, ansonsten nicht.
Gruß Daniel
Anzeige
AW: Problem mit VLOOKUP und Strings mit 0
03.09.2018 17:31:37
Jörn
Hallo Daniel,
3b war's! Ich hab einfach eine 0 an den Anfang der Vergleichstabelle eingefügt und schon läuft es durch!
Besten Dank!
P.S. Zur Info für alle mit ähnlichen Aufgaben, der "Double True Vlookup" ist um rund Faktor 13 schneller als eine SQL-Abfrage. Gemessen über GetTickCounter.
Viele Grüße
der Jörn

299 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige