Die Aufgabe

Zugegeben, die Überschrift ist kryptisch. Aber einen komplexen Sachverhalt in wenige Worte zu fassen, ist nicht immer leicht …  🙄 Aber jetzt folgt eine etwas ausführlichere Beschreibung des Problems, welches ich in einem Forum gelesen habe:

Ein Lieferant schickt einem Händler Listen in einem Format, welches zwar übersichtlich ist, aber für für eigene Auswertungen und Listen-Erstellungen  in der Form nicht genutzt werden kann.

Die Excel-Aufstellung sieht so aus, wie in dieser Datei (es sind übrigens die Original-Daten aus dem Netz). Gebraucht wird aber eine Liste, wo jede Artikelnummer so oft aufgeführt wird, wie Accessorys vorhanden sind. Das bedeutet auch, dass eine Artikelnummer ohne Accessory nicht gelistet werden soll.

Hier ein Ausschnitt aus der Darstellung im Forum:

IST und SOLL im Bild dargestellt (aus Forum kopiert)

Die Spalte A:B (rosé markiert) stellen den IST-Zustand dar, Spalte D:E das SOLL, also das Wunschziel; der Zielbereich kann natürlich in einem getrennten Blatt sein.

▲ nach oben …

 Lösung per Makro/VBA

Eine mögliche Lösung ist ein Makro, eine VBA-Routine. Die Basis-Daten, welche importiert oder geladen wurden, stehen im Tabellenblatt RohDaten. Es existiert ein weiteres Tabellenblatt, wo die gesplitteten Daten hinein geschrieben werden: SplitData. Um das Makro schlank zu halten, habe ich auf eine Überprüfung der Existenz der Ziel-Datei verzichtet. Die muss vorhanden sein. Dieser Code führt zum Erfolg:

Option Explicit
Option Base 1

Sub TrenneAccessories()
   Dim lRow As Long, Ze As Long, Anz As Long, i As Long
   Dim aData, aSpData(), SpDataZe As Long, aSplit
   
   With Sheets("RohDaten")
      lRow = Cells(Rows.Count, 1).End(xlUp).Row
      aData = Range("A2:B" & lRow)
   End With
   
   For Ze = 1 To UBound(aData)
      If aData(Ze, 2) > "" Then
         Anz = Anz + UBound(Split(aData(Ze, 2))) + 1
      End If
   Next Ze
   
   SpDataZe = 1
   ReDim aSpData(Anz, 2)
   For Ze = 1 To UBound(aData)
      If aData(Ze, 2) > "" Then
         aSplit = Split(aData(Ze, 2), ",")
         For i = 0 To UBound(aSplit)
            aSpData(SpDataZe, 1) = aData(Ze, 1)
            aSpData(SpDataZe, 2) = aSplit(i)
            SpDataZe = SpDataZe + 1
         Next i
      End If
   Next Ze
   With Sheets("SplitData")
      .Cells.ClearContents
      .Cells(1, 1) = Sheets("RohDaten").Cells(1, 1)
      .Cells(1, 2) = Sheets("RohDaten").Cells(1, 2)
      .Range("A2:B" & UBound(aSpData) + 1) = aSpData
   End With
End Sub

Wenn Sie andere Blattnamen bevorzugen, passen Sie den Code bitte an.

▲ nach oben …

Lösung per Power Query

Wenn Ihr Excel Power Query nutzen kann, dann bietet sich für solche Aktionen dieses Add-In bzw. diese Funktionalität an. Zu Beginn ist das Vorgehen in 2010/13 und 2016 unterschiedlich, im wichtigsten Teil jedoch gleich.

Excel 2010 / 2013

Voraussetzung ist natürlich, dass Sie das Add-In auch installiert haben. Klicken Sie auf den Menüpunkt Power Query und

Excel 2016

In dieser Version ist Power Query bereits integriert. Achten Sie darauf, dass eine beliebige Zelle im Bereich der Daten markiert ist. Klicken Sie auf den Menüpunkt Daten und in der Gruppe Abrufen und transformieren wählen Sie Aus Tabelle. Kontrollieren Sie die Angaben des Dialogfensters und bestätigen Sie mit OK.

▲ nach oben …

Alle Versionen

Es öffnet sich der Abfrage-Editor, wo die Daten aufgelistet sind:

Die importierte Tabelle im Abfrage-Editor

Die erste Spalte ist automatisch markiert. Klicken Sie in die Spalte Accessory und wählen Sie in der Gruppe Transformieren das Symbol Spalte teilen. Da das Komma die einzelnen Werte trennt und auch mehrere Kommas als Teiler fungieren, wählen Sie im DropDown Nach Trennzeichen und akzeptieren die Vorgaben. – Mit OK bestätigen.

Bis zur maximalen Anzahl der Elemente werden Spalten angelegt und mit dem entsprechenden Wert oder null gefüllt. Die Überschriften werden durch einen Punkt und eine fortlaufende Nummer ergänzt.

Markieren Sie nun die Spalte Artikelnummer. Wählen jetzt im Menü Transformieren, Gruppe Beliebige Spalte das Symbol Spalten entpivotieren. Klicken Sie dort auf den DropDown-Pfeil  und wählen dann den Punkt Andere Spalten entpivotieren. Markieren Sie die Überschrift der mittleren Spalte (Attribut), Rechtsklick und Entfernen oder über das Symbol Spalten entfernen. Die Überschrift der zweiten Spalte (Wert) sollten Sie in Accessory ändern. Menü Datei | Schließen und laden, Power Query erzeugt ein neues Arbeitsblatt und füllt die erzeugten Daten in eine Intelligente Tabelle/Liste.

Damit ist die Aufgabe erfüllt. Die Daten können so bestens mit einer PivotTable ausgewertet werden. Hinweis: Die Artikelnummer 99080 enthielt keine Daten und wurde entsprechend nicht in die Liste übernommen.

▲ nach oben …

Rückmeldungen / Feedback gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,00 € freuen … (← Klick mich!)