Die Aufgabe

Zugegeben, die Über­schrift ist kryp­tisch. Aber einen kom­plex­en Sachver­halt in wenige Worte zu fassen, ist nicht immer leicht …  🙄 Aber jet­zt fol­gt eine etwas aus­führlichere Beschrei­bung des Prob­lems, welch­es ich in einem Forum gele­sen habe:

Ein Liefer­ant schickt einem Händler Lis­ten in einem For­mat, welch­es zwar über­sichtlich ist, aber für für eigene Auswer­tun­gen und Lis­ten-Erstel­lun­gen  in der Form nicht genutzt wer­den kann.

Die Excel-Auf­stel­lung sieht so aus, wie in dieser Datei (es sind übri­gens die Orig­i­nal-Dat­en aus dem Netz). Gebraucht wird aber eine Liste, wo jede Artikel­num­mer so oft aufge­führt wird, wie Acces­so­rys vorhan­den sind. Das bedeutet auch, dass eine Artikel­num­mer ohne Acces­so­ry nicht gelis­tet wer­den soll.

Hier ein Auss­chnitt aus der Darstel­lung im Forum:

IST und SOLL im Bild dargestellt (aus Forum kopiert)

Die Spalte A:B (rosé markiert) stellen den IST-Zus­tand dar, Spalte D:E das SOLL, also das Wun­schziel; der Ziel­bere­ich kann natür­lich in einem getren­nten Blatt sein.

▲ nach oben …

 Lösung per Makro/VBA

Eine mögliche Lösung ist ein Makro, eine VBA-Rou­tine. Die Basis-Dat­en, welche importiert oder geladen wur­den, ste­hen im Tabel­len­blatt Roh­Dat­en. Es existiert ein weit­eres Tabel­len­blatt, wo die ges­plit­teten Dat­en hinein geschrieben wer­den: Split­Da­ta. Um das Makro schlank zu hal­ten, habe ich auf eine Über­prü­fung der Exis­tenz der Ziel-Datei verzichtet. Die muss vorhan­den 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 Blat­tna­men bevorzu­gen, passen Sie den Code bitte an.

▲ nach oben …

Lösung per Power Query

Wenn Ihr Excel Pow­er Query nutzen kann, dann bietet sich für solche Aktio­nen dieses Add-In bzw. diese Funk­tion­al­ität an. Zu Beginn ist das Vorge­hen in ²⁰¹⁰⁄₁₃ und 2016 unter­schiedlich, im wichtig­sten Teil jedoch gle­ich.

Excel ²⁰¹⁰⁄₂₀₁₃

Voraus­set­zung ist natür­lich, dass Sie das Add-In auch instal­liert haben. Klick­en Sie auf den Menüpunkt Pow­er Query und

Excel 2016

In dieser Ver­sion ist Pow­er Query bere­its inte­gri­ert. Acht­en Sie darauf, dass eine beliebige Zelle im Bere­ich der Dat­en markiert ist. Klick­en Sie auf den Menüpunkt Dat­en und in der Gruppe Abrufen und trans­formieren wählen Sie Aus Tabelle. Kon­trol­lieren Sie die Angaben des Dialogfen­sters und bestäti­gen Sie mit OK.

▲ nach oben …

Alle Versionen

Es öffnet sich der Abfrage-Edi­tor, wo die Dat­en aufge­lis­tet sind:

Die importierte Tabelle im Abfrage-Edi­tor

Die erste Spalte ist automa­tisch markiert. Klick­en Sie in die Spalte Acces­so­ry und wählen Sie in der Gruppe Trans­formieren das Sym­bol Spalte teilen. Da das Kom­ma die einzel­nen Werte tren­nt und auch mehrere Kom­mas als Teil­er fungieren, wählen Sie im Drop­Down Nach Trennze­ichen und akzep­tieren die Vor­gaben. – Mit OK bestäti­gen.

Bis zur max­i­malen Anzahl der Ele­mente wer­den Spal­ten angelegt und mit dem entsprechen­den Wert oder null gefüllt. Die Über­schriften wer­den durch einen Punkt und eine fort­laufende Num­mer ergänzt.

Markieren Sie nun die Spalte Artikel­num­mer. Wählen jet­zt im Menü Trans­formieren, Gruppe Beliebige Spalte das Sym­bol Spal­ten ent­piv­otieren. Klick­en Sie dort auf den Drop­Down-Pfeil  und wählen dann den Punkt Andere Spal­ten ent­piv­otieren. Markieren Sie die Über­schrift der mit­tleren Spalte (Attrib­ut), Recht­sklick und Ent­fer­nen oder über das Sym­bol Spal­ten ent­fer­nen. Die Über­schrift der zweit­en Spalte (Wert) soll­ten Sie in Acces­so­ry ändern. Menü Datei | Schließen und laden, Pow­er Query erzeugt ein neues Arbeits­blatt und füllt die erzeugten Dat­en in eine Intel­li­gente Tabelle/Liste.

Damit ist die Auf­gabe erfüllt. Die Dat­en kön­nen so bestens mit ein­er Piv­ot­Table aus­gew­ertet wer­den. Hin­weis: Die Artikel­num­mer 99080 enthielt keine Dat­en und wurde entsprechend nicht in die Liste über­nom­men.

▲ nach oben …

Rück­mel­dun­gen / Feed­back 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 Ihrer­seits z.B. 2,00 € freuen … (← Klick mich!)