Ich habe 600 Artikelcodes und jeder ist mit Attributen im Gesamtartikelcode codiert. Beispielsweise könnten wir den einfachen Artikel 600 haben, dann 600BK (schwarzer Körper) und 600BKR (schwarzer Körper, rot gefärbte Details) und 600BKR-YEL (schwarzer Körper, rot gefärbte Details, gelbe Basis).
Ich habe also die Liste der Artikelcodes:
600
600BK
600BKR
600BKR-YEL
und dann auf einem separaten Arbeitsblatt eine Nachschlageliste für jeden Code und seine Bedeutung:
BK Black Body
R Red Detail
YEL Yellow Base
Ich möchte eine Funktion schreiben, die jeden Code im Artikeltitel findet, der sich auf dem Nachschlageblatt befindet, und dann alle entsprechenden Werte für jeden gefundenen Code zurückgibt, vorzugsweise in einer Zelle.
600
600BK Black Body
600BKR Black Body Red Detail
600BKR-YEL Black Body Red Detail Yellow Base
Ist das möglich?
Antwort1
Ich würde ungefähr Folgendes tun. Angenommen, Ihre Liste mit Artikelcodes befindet sich in Spalte A von Tabelle 1 und beispielsweise in den Zeilen 2-20, und Ihre Nachschlageliste befindet sich in den Spalten A und B von Tabelle 2, Zeilen 2-10:
=IFERROR(VLOOKUP(MID(A2,4,2),Sheet2!$A$2:$B$4,2,0),"")&" "
&IFERROR(VLOOKUP(MID(A2,6,1),Sheet2!$A$2:$B$4,2,0),"")&" "
&IFERROR(VLOOKUP(MID(A2,8,3),Sheet2!$A$2:$B$4,2,0),"")
in Zelle B2 und dann nach unten für alle Zeilen in Blatt1 kopiert.
Obwohl die Nachschlagevorgänge in einer einzigen Formel zusammengefasst werden, wäre dieser Ansatz sehr umständlich, wenn Sie viele Codes haben. Ich würde empfehlen, dass Sie in Blatt1 für jede Codeposition Hilfszeilen einfügen (z. B. mid(A2,4,2)) und diese dann zu einer einzigen Zeichenfolge verketten.
Antwort2
Die kurze Antwort lautet „Ja“, der Weg ist allerdings etwas länger.
Da Ihre Frage viele Möglichkeiten nicht ausschließt (z. B. einfache Elemente, die länger als drei Zahlen oder alphanumerisch codiert sind, mehr als drei Codes pro einfachem Element, Codes mit unterschiedlicher Bedeutung je nach einfachem Element usw.), würde ich vorschlagen, am sichersten mit der Analyse der Codes zu beginnen. Dies sollte Komplikationen vermeiden, wenn GR entweder grauer Körper mit roten Details (oft ein kompatibles Farbschema!) oder grüner Körper ist.
Vorausgesetzt, diese wurden analysiert (in drei Spalten B:D, beginnend in Zeile 2), ist es danach einfach, mit Ihrer Nachschlagetabelle (die auf dem separaten Blatt) den Namen anzugeben codes:
=VLOOKUP(B2,codes,2,0)&VLOOKUP(C2,codes,2,0)&VLOOKUP(D2,codes,2,0)
in Ihrem Artikelcodeblatt. Um Leerzeichen zwischen den Werten zu erhalten, habe ich angenommen, dass alle Einträge in der rechten Spalte Ihres Code-Arrays mit einem Leerzeichen enden (das lässt sich leicht anordnen, z. B. mit =A2&" " nach unten kopiert usw.).
Der schwierige Teil könnte also darin bestehen, die Codes aus den Artikelcodes herauszufiltern. Hierfür würde ich vorschlagen, Spalten wie die folgenden hinzuzufügen (mehr, wenn mehr als drei Codes vorhanden sind):
mit den Formeln unten:
Spalte B gibt an, wo mit der Suche nach Codes begonnen werden soll (falls einfache Codes nicht dreistellig sind). Die Spalten C:D geben an, wo mit der Suche nach dem nächsten Code/der Länge des nächsten Codes begonnen werden soll. Ich stimme zu, das ist nicht elegant, aber relativ vielseitig. Stellen Sie sicher, dass die Analyse auf der linken Seite korrekt ist, bevor Sie nach rechts weitermachen.
Nach erfolgreichem Parsen empfehle ich Kopieren/Einfügen Spezial/Werte (um die Formeln loszuwerden) und dann ersetzenleermit einem Punkt (um Fehler in der Nachschlageformel zu vermeiden, ohne sie komplexer zu machen)*. Außerdem gehen wir davon aus, dass Ihre Liste mit Artikelcodes in Spalte A steht. Löschen Sie die Spalten B:F, bevor Sie die Nachschlageformel wie oben anwenden (oder passen Sie die Verweise entsprechend an) – und fügen Sie bei Bedarf weitere Nachschlagevorgänge hinzu.
*Hinweis: Stellen Sie sicher, dass in jeder Spalte der Nachschlagetabelle ein Punkt in einer Zelle hinzugefügt wird.
Fügen Sie Spalten für c5, c6 und c7 hinzu, um (a) die maximale Länge 7 und (b) das Worst-Case-Szenario (d. h. alle Zeichen einzeln) zu ermöglichen.
Antwort3
Hier ist eine Version, die die Suchfunktion verwendet (nicht-Groß-/Kleinschreibung-sensitive Version von „Find“). Die Einrichtung ist wie folgt.
In Blatt 1 beginnen die nachzuschlagenden Codes in Spalte A von Blatt 1. Das endgültige Ergebnis steht in Spalte B. In den Spalten C, D usw. sind die eindeutigen Codes horizontal in Zeile 1 angeordnet, d. h. „BK“ in C1, „R“ in D2 usw. In der Praxis erreichen Sie dies, indem Sie einfach die Codeliste in der Nachschlagetabelle kopieren und sie mit „Inhalte einfügen“ horizontal in die Zellen C1, D1 usw. transponieren.
Geben Sie dann zunächst in Zelle B2 folgende Formel ein:
=IF(NOT(ISERROR(SEARCH(C$1,$A2))),VLOOKUP(C$1,Sheet2!$A$2:$B$4,2,0),"")
Kopieren Sie diese Formel über die Zeilen von Spalte C bis zu der Anzahl der Codespalten, die Sie in Zeile 2 erstellt haben.
Schließlich verketten Sie in Zelle C2 alle Ergebnisse für Zeile 2, d. h. die Formel
=D2&" "&E2&" "&F2
usw., für alle Spalten mit Codes in Zeile 1. Dieser Schritt ist mühsam, lässt sich aber mit der folgenden VBA-Funktion abkürzen, die die Verkettung aller Zellen eines Bereichs ermöglicht:
Function Concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return the result as a single string
' useful when you have a large range of cells that you need to concatenate
' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
Dim retVal As String, dlm As String, cell As Range
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
retVal = retVal & CStr(cell.Value) & dlm
End If
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
Concat = retVal
End Function
Sie würden diese Funktion in ein Modul in Developer VBA einfügen und kopieren. Die Verwendung ist einfach – beispielsweise concat(C1:D1," ").
Beachten Sie, dass dieser Ansatz für alle 2-stelligen Codes und alle 1-stelligen Codes funktioniert, sofern sie nicht zu den 2+-stelligen Codes gehören, das heißt, wenn keine Codepaare wie „R“ und „BR“ vorhanden sind.