Jede Zeile einer Liste/Tabelle vervielfachen
Die Aufgabe
Es existiert eine Aufstellung mit Artikeln des Bekleidung-Bereichs. Diese musterhafte Liste können Sie hier auf unserem Server herunterladen. Das Ziel ist, mit oder ohne VBA/Makros jede Position zu vervielfachen, mehrfach aufzulisten. Dabei soll jeweils der Artikelnummer eine Größenbezeichnung ‑getrennt mit einem Bindestrich- angefügt werden. Die Bezeichnungen sind: XS, S, M, L, XL, XXL. Prinzipiell war das eine Anfrage aus einem Excel-Forum (siehe auch der Link bei der Formel-Lösung). Ich habe das Ganze ein wenig „aufgebohrt” und um eigene Varianten ergänzt.
Wenn Sie meine Muster-Datei geöffnet haben werden Sie gewiss nachvollziehen können, dass zu jedem der Produkte die Angabe der Kleidergröße fehlt. Das Ziel ist es, die Artikelnummer jeweils um die Größen-Bezeichnung zu ergänzen, sodass beispielsweise 1729-XS für ein Kleid der Größe XS bis hin zu 1729-XXL des gleichen Kleidermodells in der maximalen Größe gelistet wird. Im Endeffekt soll jede ursprüngliche Artikelnummer um jegliche Größenbezeichnung ergänzt werden.
Formel-Lösung
Wie schon erwähnt, ist die ursprüngliche Aufgabe in einem Forum gepostet worden. Und da ich denke, dass mein Namensvetter eine stabile Formel-Lösung erstellt hat, verweise ich hier schlicht und einfach auf den entsprechenden Forums-Thread. Blättern Sie bis zum Beitrag #8 und sie erkennen die Lösung von Günter (WillWissen). Ich habe sie nicht weiter geprüft, da aber auch kein Widerspruch im weiteren Verlauf des Threads erfolgt ist, wird das seine Richtigkeit haben. Diese Formel bezieht sich naturgemäß auf die Datei, welche dort zur Verfügung gestellt worden ist (hier noch einmal die Roh-Form).
VBA-Lösung
Auch wenn im eben erwähnten Forum bereits eine VBA-Lösung gepostet worden ist, ich habe sie mir weder angesehen und entsprechend auch nicht getestet. Für mich ist es wichtig, Lösungen absolut autonom zu kreieren, dann weiß ich wenigstens auch noch nach längerer Zeit, was ich bei Bedarf wo ändern kann oder muss. Hier nun der Code, den ich für meine Muster-Datei erstellt, erprobt und für gut befunden habe:
Option Explicit Option Base 1 'Array startet mit 1, nicht mit 0 Sub ArtikelGroesseExpandieren() 'Jedem Artikel eine eigene Zeile für die Größe zuweisen Dim aArtikel1, aArtikel2, aGroessen Dim lRow As Long, Ze As Long, AnzGr As Long, AnzArt As Long, i As Long Dim sh As Object, Sh2 As String, Sh2Da As Boolean 'Tabelle Größen lRow = Cells(Rows.Count, 6).End(xlUp).Row 'letzte Zeile Größen, Spalte F (6) aGroessen = Range(Cells(2, 6), Cells(lRow, 6)) '2. Zeile wegen Überschrift AnzGr = lRow - 1 'Tabelle Artikel With Sheets("Tabelle1") lRow = .Cells(Rows.Count, 1).End(xlUp).Row 'letzte Zeile Artikel, Spalte A (1) aArtikel1 = .Range(.Cells(2, 1), .Cells(lRow, 3)) AnzArt = lRow - 1 ReDim aArtikel2(AnzArt * AnzGr, 3) For Ze = 0 To AnzArt - 1 For i = 1 To AnzGr aArtikel2(Ze * AnzGr + i, 1) = CStr(aArtikel1(Ze + 1, 1)) & "-" & aGroessen(i, 1) aArtikel2(Ze * AnzGr + i, 2) = aArtikel1(Ze + 1, 2) aArtikel2(Ze * AnzGr + i, 3) = aArtikel1(Ze + 1, 3) Next i Next Ze '------------------------- 'Neues Blatt anlegen Sh2 = "Tabelle2" For Each sh In ThisWorkbook.Sheets If sh.Name = Sh2 Then Sh2Da = True Exit For End If Next sh If Not Sh2Da Then Sheets.Add After:=Sheets("Tabelle1") ActiveSheet.Name = Sh2 End If End With With Sheets("Tabelle2") .Cells.ClearContents .Cells(1, 1) = "ArtNr" .Cells(1, 2) = "Bez" .Cells(1, 3) = "Preis" .Range("A2").Resize(AnzArt * AnzGr, 3) = aArtikel2 .Range("C2:C" & UBound(aArtikel2) + 1).NumberFormat = "#,##0.00 $" End With End Sub
Power Query-Lösung (1)
Obwohl ich diesen Weg erst im zweiten Anlauf gefunden habe, möchte ich Ihnen diese Lösung an erster Stelle vorstellen. Wie so oft im Leben fallen auch mir die besseren Möglichkeiten mitunter erst im Nachhinein ein. 😉 Das will aber nicht heißen, dass die im kommenden Abschnitt aufgeführte Lösung schlechter ist. Ganz im Gegenteil, sie bietet für andere Fälle ein deutlich höheres Potenzial.
Beginnen Sie damit, die Zelle A1 zu markieren und per StrgL oder StrgT aus den Produkt-Daten eine Formatierte Tabelle zu erstellen. Ich gebe dieser Tabelle dann auch einen sinnhaften und vielleicht etwas legeren Namen: Klamotten. 🙂 Natürlich können Sie einen beliebigen, anderen Namen verwenden …
Für die Auflistung der Größen verfahren Sie gleichermaßen. Achten Sie aber darauf, dass hier das Häkchen bei Tabelle hat Überschriften gesetzt sein muss. Dieser Tabelle gebe ich dann den Namen Größen.
Ich importiere nacheinander diese beiden Tabellen in den Power Query Editor. Anschließend, direkt nach dem Import jeder der beiden Tabellen Register Datei | Schließen & laden in… | Nur Verbindung erstellen. Damit erreiche ich, dass später keine überflüssigen Tabellen in neu erstellte Arbeitsblätter geschrieben werden.
Öffnen Sie nun auf beliebige Weise die Abfrage Klamotten. Bei mir war es so, dass in der Spalte Preis (aus nicht ersichtlichen Gründen) keine Cent-Beträge erfasst worden sind; es waren nur „glatte” Beträge:
Die Abfrage direkt nach dem Import, ohne Cent-Beträge
Im rechten Seitenfenster erkennen sie bei Angewendete Schritte, dass die 2. Position Geänderter Typ ist. Sie könnten zwar diese Zeile durch einen Klick auf das rote 
löschen, ich empfehle ihn aber, die Überschrift Preis zu markieren und den Datentyp auf Dezimalzahl zu ändern.
Klicken Sie nun im linken Seitenfenster auf den Text Abfragen oder das > – Symbol und wählen Sie dort durch einen Mausklick die Abfrage Größen. In den Zeilen 1–6 stehen die unterschiedlichen Größenbezeichnungen in logischer Reihenfolge untereinander. Wechseln Sie zum Register Transformieren und Klicken Sie in der Gruppe Tabelle auf das Symbol Vertauschen. Das ist jener Vorgang, den sie in Excel unter Transponieren kennen. Aus 6 Zeilen wurde nun 1 Zeile und die ursprüngliche Überschrift wurde durch eigene, jeweils einzigartige Überschriften ersetzt:
Die transponierte, ehemals 6‑zeilige Tabelle
Markieren Sie nun (vorzugsweise mit Shift) alle Spalten, und immer noch im Register Transformieren wählen Sie in der Gruppe Textspalte den Menüpunkt Spalten zusammenführen. Als Trennzeichen wählen Sie das Semikolon, als Spaltenname schreibe ich in das 2. Textfeld des Dialogs Größen.
Die 6 Spalten sind nun zu einer Spalte zusammengefasst worden. Nun wechsle ich zum Register Spalte hinzufügen und klicke hier auf das Symbol Benutzerdefinierte Spalte. Im Dialog gebe ich bei Neuer Spaltenname das Wort Index ein und im großen Textfeld (Benutzerdefinierte Spaltenformel) schreibe ich nur eine 1:
Der Dialog für die (neue) Benutzerdefinierte Spalte
Danach schließe ich den Dialog per OK. Nun wechsle ich zur Abfrage Klamotten und füge auch hier eine Benutzerdefinierte Spalte ein. Sie könnten als Überschrift auch Index verwenden, ich verwende der Klarheit wegen den Namen Pseudo-Index, da es ja kein fortlaufender Index ist. Hinweis: Wenn Sie es genau nehmen, hätte ich auch diesen Namen in der Abfrage Größen verwenden sollen. 😉
Aktivieren Sie das Register Start und Klicken Sie auf das Symbol Kombinieren. Im DropDown erweitern Sie den Punkt Abfragen zusammenführen 
und wählen dort die Möglichkeit Abfragen als neue Abfrage zusammenführen. In der oberen Hälfte des Dialogs erkennen sie die Einträge der Abfrage Klamotten. In der unteren Hälfte wählen Sie die Abfrage Größen. Klicken Sie im oberen Bereich in ein beliebiges Feld der Spalte Pseudo-Index sowie auch in Index, was sich anschließend so darstellt:
In diesem Dialog führen Sie die beiden Abfragen zusammen
Nichts weiter ändern und mit OK bestätigen. Ihre Tabelle wird nun so aussehen:
Die Daten beider Tabellen, erster Schritt der Zusammenführung
Erweitern Sie nun die Spalte Größe durch einen Klick auf den Doppelpfeil 
und entfernen Sie im Dialog nur das Häkchen bei Ursprünglichen Spaltennamen als Präfix verwenden. Schließen Sie das Fenster und ihre Tabelle wird nun so aussehen:
Das Zusammenführen ist nun abgeschlossen
- Löschen Sie nun die Spalten Pseudo-Index sowie Index, sie werden nicht mehr gebraucht.
- Klicken Sie jetzt in die Überschrift(en) ArtNr, Strg und Größe.1, damit diese beiden Spalten markiert sind.
- Achten Sie dabei auf die Reihenfolge der Markierung.
- Das Register Start ist aktiviert, die Spalte Größe.1 ist markiert.
- In der Gruppe Transformieren ein Klick auf Spalte teilen | Nach Trennzeichen.
- Die Vorgabe Semikolon ist korrekt. Belassen Sie es bei der Auswahl Bei jedem Vorkommen des Trennzeichens.
- Klicken Sie auf Erweiterte Optionen und markieren Sie hier den Option-Button Zeilen:
Hier markieren Sie den Radio-Button „Zeilen” (statt „Spalten”)
Nach einem Klick auf OK sind aus den 5 Zeilen wie durch Geisterhand 5 × 6 (also 30) Zeilen geworden. Das Kleid mit der Artikelnummer 1729 ist 6 Mal aufgeführt, jeweils mit einer anderen Größenbezeichnung. Gleiches gilt für die Hose und die weiteren Artikel.
Im letzten Schritt werden Sie die Artikelnummer und die Größenbezeichnung durch ein - getrennt zusammenführen. Dazu Klicken Sie in die Überschrift ArtNr, Strg und danach in Größe.1. Wechseln Sie in das Register Transformieren | Spalten zusammenführen und als Trennzeichen navigieren Sie erst einmal zur untersten Möglichkeit –Benutzerdefiniert– und tragen dann in das neu erschienene Textfeld das - ein. Neuer Spaltenname (optional) überschreiben sie mit Artikelnummer.
Verschieben Sie nun die neu erstellte Spalte Artikelnummer durch ziehen mit der Maus oder Rechtsklick Überschrift | Verschieben | An den Anfang. Bleibt nur noch der Schritt Start | Schließen und laden und in einem neuen Tabellenblatt wird eine neue Tabelle mit dem Wunschergebnis erstellt. Idealerweise werden sie die Spalte Preis noch als Währung formatieren. Meine Lösung können Sie an dieser Stelle herunterladen.
Power Query-Lösung (2)
Power Query wäre kein Teil des Excel, wenn es nicht an vielen Stellen mehrere Möglichkeiten gäbe, zum gewünschten Ziel zu gelangen. Ich hatte schon erwähnt, dass mir der hierüber aufgeführte Weg erst eingefallen ist, nachdem ich die im Folgenden geschilderte Vorgehensweise fertiggestellt hatte. Jede der beiden Arbeitsweisen hat ihre Vor-als auch Nachteile; in vielen Fällen wird entscheidend sein, in welchem Umfeld sie gerade arbeiten und ob Sie Formeln an sich mögen oder nicht (so sehr).
Da in sehr vielen Punkten die Vorgehensweise mit dem vorher gezeigten Beispiel identisch ist, werde ich mich an den Stellen, wo es sinnvoll ist, auf eine stichpunktartige Darstellung beschränken. Der Import der Daten in den Power Query Editor ist beispielsweise absolut gleich. Es existieren danach 2 Abfragen, Artikel (oder Klamotten) und Größen. Beide Abfragen speichern Sie auch wiederum Nur als Verbindung, um nicht unnötige Datenblätter mit nicht erforderlichen Tabellen zu erzeugen.
Zuerst werden sie kontrollieren, ob bei den Klamotten in der Spalte Preis auch die Nachkommastellen angezeigt werden. Idealerweise werden sie den Datentyp auf Dezimalzahl ändern, falls nicht bereits durch Power Query geschehen. Wechseln Sie nun zum Register Spalte hinzufügen und in der Gruppe Allgemein ein Klick auf Indexspalte. Um die Spalte später besser identifizieren zu können, ändern Sie den Namen der Überschrift auf beispielsweise Index Artikel. Das geht prima über F2 oder per Doppelklick, wenn Sie sich nicht durch die Menüs „wühlen” wollen. 😉
Wechseln Sie zur Abfrage Größen. Fügen Sie auch hier auf die gleiche Weise einen Index ein. Sie können die Überschrift so belassen oder auf Wunsch auch abändern. Sie werden im weiteren Verlauf die Anzahl der Positionen, genauer gesagt den Maximal-Wert des Index brauchen. Sie können sich hier die Zahl 5 merken oder aber (optional) Sie erstellen ein Duplikat dieser Abfrage, markiere die Spalte Index, aktivieren das Register Transformieren und wählen dann in der Gruppe Zahlenspalte beim Symbol Statistiken den Punkt Maximum. Idealerweise werden Sie diese Abfrage umbenennen (beispielsweise Max Index Größe) und in der einzigen Zelle diese Abfrage steht dann der Wert 5.
Hinweis: Wenn sie später einmal etwas „sattelfester” mit Power Query geworden sind, können Sie das berechnet Maximum der Spalte Größe auch als Liste speichern und diesen Wert im gleich beschriebenen Schritt (Formel-Eingabe) direkt verwenden, ohne die Zahl einzugeben. Bei Bedarf senden Sie einfach eine Mail an:
Frag-den-Spezialisten@Excel-ist-sexy.de.
Wechseln Sie nun zur Abfrage Klamotten. Register Spalte hinzufügen | Benutzerdefinierte Spalte und belassen Sie gerne den Spaltennamen bei Benutzerdefiniert. Im Bereich Benutzerdefinierte Spaltenformel geben Sie diese Formel ein:
List.Range({0..5}, 0)
(achten Sie auf die runden und geschweiften Klammern!) und nach einem OK stellt sich ihr Bildschirm so dar:
Zwischenstand nach dem splitten der Größe-Spalte
Erweitern Sie die Überschrift Benutzerdefiniert und wählen Sie dort die Möglichkeit Auf neue Zeilen ausweiten. Ihre Tabelle wird sich nun so darstellen:
Jede der 5 einzelnen Positionen/Artikel ist nun erweitert
Sie befinden sich immer noch in der Abfrage Klamotten.
- Wechseln Sie zum Register Start.
- Klicken Sie auf das Symbol Kombinieren und erweitern Sie die obere Auswahl Abfragen zusammenführen , anschließend Klicken Sie auf Abfragen als neue Abfrage zusammenführen.
- Wählen Sie als 2. Abfrage die Abfrage Größen.
- Um die bei den Queries zu verknüpfen den Spalten zu markieren, Klicken Sie bei Klamotten in die Spalte Benutzerdefiniert und bei Größen in die Spalte Index Artikel.
- Bestätigen Sie mit OK.
Dass nun ausschließlich Table in der neuen Spalte steht, das kennen Sie. Erweitern Sie nun die Spalte Größe und entfernen Sie im Dialog nur das Häkchen bei Ursprünglichen Spaltennamen als Präfix verwenden. Die Tabelle stellt sich nun so dar:
Die Abfrage nach dem „entpacken” der Spalte Größe
Sie erkennen, dass die Datensätze jetzt nicht mehr in der ursprünglichen Reihenfolge sind. Darum werden Sie diese nun in exakt der hier aufgezeigten Reihenfolge jeweils aufsteigend sortieren: Zuerst nach Index Artikel und anschließend nach Index.1. Löschen Sie nun die Spalten Index Klamotten, Benutzerdefiniert und Index Größen.
Die Schritte, um die Artikelnummer und die Größenbezeichnung zusammen zu führen kennen Sie bereits: ArtNr und Größe.1 markieren, über Register Minuszeichen Zusammenführen und der neu generierten Spalte die Überschrift Artikelnummer geben. Diese Spalte jetzt noch an den Anfang verschieben und es verbleibt praktisch nur noch der Punkt Schließen & laden. Der Optik wegen werden sie wahrscheinlich nun noch die Spalte mit dem Preis als Währung formatieren. Und natürlich gibt es auch diese Lösung, Klicken Sie einfach hier.
Zugegeben, es gibt noch weitere Wege zur Erreichung dieses Ziels. Aber das überlasse ich Ihrem Forschergeist oder anderen „Spezies”, die dann ihren eigenen Lieblings-Weg darlegen können. 😎
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,50 € freuen …