Excel VBA - Schnellste Suchmethode nach 2 Kriterien

[MickH74]

Hallo liebes Forum,

ich habe eine Tabelle mit recht vielen Zeilen. In Spalte A steht der Kundenname, in Spalte B die Geschäftsabteilung zu der dieser Kunde gehört. In den Spalten C bis irgendwas stehen dann weitere Daten wie Absatzzahlen u.s.w., die für die Frage aber nicht relevant sind.

In einer zweiten Tabelle steht ebenfalls in Spalte A der Kunden, in Spalte B die GA und in Spalte C der Name des Kundenbetreuers.

Nun soll für jeden Eintrag in Tabelle 1 die Kombination Spalte A und Spalte B in Tabelle 2 gesucht werden und das Ergebnis in Tabelle 1 eingetragen werden. Ich suche also den zuständigen Kundenbetreuer für jede Zeile in Tabelle 1.

Im Moment mache ich das mit 2 verschachtelten Schleifen. Die eine geht Tabelle 1 Zeile für Zeile durch und für jeden Eintrag durchläuft die innere Schleife die zweite Tabelle und sucht nach einer Übereinstimmung von beiden Kriterien (Firma und GA). Sobald ein Treffer gefunden wurde, wird das Ergebnis in Tabelle 1 geschrieben und die innere Schleife abgebrochen. Dann geht es weiter mit der nächsten Zeile aus Tabelle 1.

Das ganze dauert ziemlich lange. Tabelle 1 hat 26.000 Zeilen und Tabelle 2 hat 1.700 Zeilen. Aktuell läuft das Skript etwa 40 Minuten.

Nun meine Frage: gibt es eine schnellere Methode? Vielleicht mit Range.Find? oder Application.VLookup? Ein Problem dabei ist sicherlich, dass auf zwei Kriterien geprüft werden muss.

Danke schon mal für eure Ideen!
Mick

 

[Mip101]

Hey Mick,

wäre es nicht möglich eine Hilfsspalte zu erstellen, welche Spalte A & B (VERKETTEN()) verkettet?

Macht man dies in beiden Tabellen, kann man ziemlich einfach mit SVERWEIS() die übereinstimmenden Felder finden.

Wenn das keine Option ist, müsste man sich entsprechend eine VBA Lösung ausdenken.

Grüße

 

[Mirakel]

Im Prinzip hast du ja blos ein Geschwindigkeitsproblem.
Lohnt es sich denn, überhaupt Zeit darauf zu verwenden?

Wie mit Mip101 vorgeschlagen hat, wären Hilfsspalten eine Lösung und dann ggf. sverweis oder FBA darauf deutlich schneller.
Alternativ kannst du aber auch z.B. eine temporäre Hilfsspalte, ggf. per VBA, für Tabelle 2 erstellen und die Zellen aus Tabelle 1 nur für die Suche verketten.

Zudem kann man je nachdem welche Suchmethode man nutzt, du hast ja schon zwei erwähnt, bzw. umsetzt (Eingrenzung der Spalten in denen gesucht wird) auch noch Zeit sparen.
Dafür müssten wir aber deinen Code evaluieren können.

Mein naheliegendster Vorschlage wäre, in Tabelle 2 per VBA eine Hilfspalte zu erzeugen mit den verketteten Strings aus Spalte 1&2, und per Schleife für jede Zeile aus Tabelle 1 ebenfalls die Spalten 1&2 zu verketten und gleich damit über range.find zu suchen.
Mit dem gefunden Objekt kannst du dann auch leicht über offset auf die entsprechenden Zielspalten zugreifen.

Ich kann mir aber auch gut vorstellen, dass es noch bessere Lösungen gibt.

 

[MickH74]

Danke für Eure Antworten. Aus zwei Feldern mit Verketten eins zu machen würde das Problem mit den 2 Kriterien lösen. Werde ich mal probieren.

Ob sich das lohnt, die Zeit für die Optimierung zu investieren? Wirtschaftlich ist das wahrscheinlich nicht, aber mir geht es auch einfach darum, etwas mit VBA zu machen. Das Programm ist zwar für den Job, aber die Programmierung ist ein Hobby und ich mache das einfach gerne. Daher interessiert es mich einfach, ob und wie man ein Problem besser lösen kann. Nur so lernt man was Neues :-)

Gruß,
Mick

 

[_killy_]

1. Suchbegriff verketten bringt einen deutlichen Vorteil
2. ich würde alle Werte Paare aus Tabelle 2 in Tabelle 1 suchen lassen
(somit hast du nur 1.700 Schleifendurchläufe statt 26.000) -> sollte auch einiges an Zeit sparen

 

[Mip101]

Okay, also wenn du es mit Programmierung lösen möchtest:

1. Lese die 26000 Zeilen als HashMap (A) ein, Hash ist Spalte A+B, Value ist Spalte C
2. Lese die 1700 Zeilen als HashMap (B) ein, Hash ist Spalte A+B, Value ist Spalte C
3. Nun lässt du eine Schleife über die HashMap B beginnen und vergleichst in einer weiteren Schleife in dieser über HashMap A den Key von HashMap B mit HashMap A
4. Immer wenn ein übereinstimmender Eintrag gefunden wurde, löschst du den Eintrag aus HashMap A heraus und schreibst den gefundenen Wert in deine Excel Range (So wird der Pool aus 26000 Einträgen immer kleiner und die Schleife schafft es schneller durch)
5. ...
6. Profit

So würde ich es wahrscheinlich machen, wenn ich nichts vergessen habe und VBA HashMaps kennt.

 

[Mextli]

Um Excel schnell zu bekommen muss man Zelloperationen meiden, d.h. hier zumindest die Tabelle 2 mit den Kundenbetreuern in einem Array zwischencachen, am besten aber beide Tabellen. Außerdem Suchbegriffe mergen und zu guter Letzt das übliche Wundermittel = Screenrefresh aus. Der wirkt zwar nur Wunder, wenn man viele Zell/Areaoperationen hat aber schadet dafür auch nie.

 

[MickH74]

Kurzes Feedback:

Habe nun 2 Arrays erstellt und die Suchbegriffe verkettet.

Neue Zeit für den Durchlauf: 38 Sekunden.

Krass! Danke für die Hilfe!

Gruß,
Mick

 

[Mip101]

40 Minuten vs. 38 Sekunden.

Das ist doch mal deutlich besser!