VBA: "Suchfunktionen"

[Gelöschtes Mitglied 683020]

Gecko lernt VBA, Teil 9000, wieder mit Likes für die Antwort.

Leider eine relativ komplizierte Anfrage, ich bin mir nicht sicher, ob es verständlich rüberkommt. Noch kann ich (Datenschutz), die Tabellen anfügen. Im Grunde ist das Problem relativ einfach, auch bereits gelöst: Ich muss aus einem Workbook A Produktdaten in ein anderes Workbook B übertragen. Das Hauptproblem ist, dass Workbook B standardisiert ist, Workbook A aber aussieht wie Kraut und Rüben, in etwa so:

Workbook A:

Spalte 1 | Spalte 2 | Spalte 3 | Spalte 4 | Spalte 5
Id-Nr | Produktüberschrift | Fließtext (technische Details) | Fließtext (SEO-Text) | Fließtext (Sonstige Anmerkungen)

Workbook B:

Spalte 1 | Spalte 2 | Spalte n | ... Spalte n+1
Id-Nr | Preis | Farbe | Gewicht | Höhe | Breite | ...


Das ganze für ca. ~100 Werte pro Produkt.

Workbook A kommt aus einer alten Datenbank, kann ich nicht bearbeiten, noch scheinen die gewünschten Merkmale (die in Workbook B übertragen werden sollen) via XML auslesbar zu sein. Also wurde das "per Hand" gemacht, was für Produktkataloge mit einer Stückzahl von >1000 ziemlich behindert ist.

Ich hab mir selbst ein paar Makros geschrieben, die Workbook A durchsuchen und Zielwörter in ein neues Arbeitsblatt schreiben. Die Zielwerte kann man dank S-Verweis/Vlookup relativ easy und zeiteffizient übertragen. Für sehr einfache Zahlenwerte (Höhe/Breite/Länge/Gewicht) funktioniert das super, in etwa so:

dim i as long, j as long
dim suchwort as string

i = application.worksheetfunctions.countA(range("A:A"))
'Spalte A = id-nummern
suchwort = inputbox("Suchwort eingeben")

'i = id-nummern

for i = 1 to i


j = instr(cells(i,3), suchwort)
'spalte C / spalte 3 = suchwort kommt eigtl. nur im fließtext "technische Details" vor

if j > 0 then
worksheets(2).cells(i,1).value = cells(i,1)
worksheets(2).cells(i,2).value = Mid(cells(i,3), j, len(suchwort) + 5) 
'fünf stellen sind für zahlen meist ausreichend


else
endif

next i

Syntax jetzt mal aus dem Kopf geschrieben, kann Fehler enthalten, funktioniert aber.

Das eigentliche Problem: Sachen wir Farben werden in tausend Variationen abgegeben (rot <> karmesin <> karmesinrot <> magenta <> magenta rot <> magenta rot / dunkelpink), und je nach "Fließtext" auch etwa als: "Farbe: rot" oder "rote Farbe" oder "rot lackiert". Die Hölle.

Weiterhin, es kann vorkommen, dass ich mehrwertige Einträge übernehmen soll, die im Workbook B in die gleiche Spalte sollen, etwa:

Zusatzaustattung darf enthalten: Stoppuhr, Pulsmesser, Reperaturwerkzeug, ... , Wert 50.

Die oben beschriebenen Daten stehen nun auch noch über drei Spalten verteilt, was noch behinderter ist.

Meine Lösungen sind ähnlich dem ersten Syntax, nur habe ich die Suchworte gleich in eine extra Spalte geschrieben, und lasse die mit einer For-Schleife nach und nach suchen und füge die Suchergebnisse dann nach Durchlauf in neue Spalten. Ein Extra-Makro kann mir dann die Spalten zusammenfügen, falls ich das denn will.
Der Haken: die inStr-Suche durfte ich auch mit Schleifen erweitern, falls der Zielwert häufiger oder falsch-positiv auftaucht, was durchaus der Realität entspricht, etwa könnte im Text stehen:

"Laufen Sie schon vor Scham rot an? Dann probieren Sie unser Laufband der Marke Forrest Gump. Erhätlich in schwarz oder silber!" - offensichtlich will ich rot nicht, sondern "schwarz, silber".

"Zwei Bremstypen: vorne Scheibenbremse, hinten Rücktrittsbremse - beide lösen die V-Brakes-Bremsen ab!" - zugegeben ein schlechtes Beispiel, will aber beide Werte, sollte ich nach "Bremse" suchen; V-Brakes will ich offensichtlich auch gar nicht drin haben.

Als Alternative habe ich zudem noch die Cells.find-Methode ausprobiert (statt inStr), die mir dann aber gleich die ganze Zelle mit dem Krimskrams außenrum vollspammen; d.h. ich darf dann auch noch manuell drüberschauen und ausbessern.

Irgendwie habe ich mich mit dem Ausbessern schon angefreundet, es geht einfach nicht anders. Allerdings: Gibt es irgendwelche Suchmethoden, die zeiteffizienter sind? Egal ob mit Cells.Find oder mit inStr dauert es bis zu 30 Minuten, wenn ich nach ~60 Suchbegriffen in 2000+ Zeilen suche. Ist zwar immer noch sehr viel schneller als die "alles per Hand-Methode", aber nicht wirklich befriedigend.

 

[MegaVolt]

Bist du mit der Automatisierung wirklich schneller als es per Hand zu machen? Du musst ja sowieso alles händisch prüfen und wenn der Fließtext wirklich so komplett wirr ist wie in deinen Beispielen dann halte ich es für nahezu unmöglich, die Texterkennung so korrekt zu machen, dass er nicht ständig false positives hat oder Sachen fälschlicherweise nicht mit reinnimmt. Und wenn man es wirklich sehr sorgfältig per Hand korrekturlesen muss dann kann man es auch gleich so aufbauen, kostet dann kaum mehr Zeit.

Ich würde 2 Praktikanten unabhängig voneinander dransetzen und sie beide (nach den gleichen Vorgaben) die Zieltabelle befüllen lassen und dann einfach beide Ergebnisse vergleichen. Wo es Abweichungen gibt müssen sie dann nochmal ran und schauen wer einen Fehler drin hatte. Damit sollte man eine vernünftige Datenqualität hinbekommen, wahrscheinlich sogar deutlich besser als ein nicht ganz richtig funktionierender Algorithmus der nur flüchtig kontrolliert wurde.

 

[DeViL]

Jo, klassischer Werkstudentenjob. Spar dir die Mühe mit VBA.

 

[Gelöschtes Mitglied 683020]

Wie gesagt, für Zahlen funktioniert es ziemlich gut, da die Standardisiert sind. Die meisten anderen Sachen sind relativ binär, entweder das Produkt hat die Eigenschaft, oder sie hat es eben nicht, womit evtl. noch 5-10% an Werten überbleiben, die u.U. evtl. tatsächlich manuell gelesen werden müssen.

Nochmal, einer VBA-Lösung gegenüber steht: Irgendwas zwischen 100 und 5000 Produkten, die mindestens 60 auszulesende Werte haben, die wiederum im Schnitt zwischen 10 Vorgabewerten haben. So was per Hand zu übertragen dauert nicht nur ewig, sondern ist wahrscheinlich genauso fehleranfällig.

Ansonsten sitzen bei der tatsächlichen Auswertung tatsächlich schon Azubis, Zeitarbeiter und Praktikanten

Viel interessanter ist eigentlich, warum das nicht direkt im XML-Schema halbwegs logisch ausgezeichnet wurde.

 

[Benrath]

Ich habs nich so ganz verstanden, die 60 Zielwerte kennst du also ? eventuell in R oder was anderem mit Regular expressions ? Aber klingt schon recht hoffnungslos, da false positives zu verhindern.

 

[Gelöschtes Mitglied 683020]

Ähnlichen Gedanken hatte ich, aber kein R da und wahrscheinlich kostet die Lizenz dann doch was, weil kommerziell und so.

 

[Benrath]

R ist afaik immer kostenlos,kann sein dass der editor teurer wird wenn du die spezielle version von R studio willst, aber die brauch man ja nicht

 

[FORYOUITERRA]

r an sich ist halt absolut scheiße um an datensatz selbst was zu ändern.

 

[parats']

Schau mal nach fuzzy unter vba. Ist zwar prinzipiell langsam wie sonst was, aber sollte dir zumindest bei allen möglichen Schreibweisen helfen können.

 

[Gelöschtes Mitglied 683020]

Schau mal nach fuzzy unter vba. Ist zwar prinzipiell langsam wie sonst was, aber sollte dir zumindest bei allen möglichen Schreibweisen helfen können.

danke, das könnte tatsächlich bei manchen sachen helfen. ich schau morgen mal wie viel zeit das frisst und wie stark der datensatz dadurch verbessert werden könnte.