Die Auf­ga­be

Zu­ge­ge­ben, die Über­schrift ist kryp­tisch. Aber ei­nen kom­plex­en Sachver­halt in we­ni­ge Wor­te zu fas­sen, ist nicht im­mer leicht …  🙄 Aber jet­zt fol­gt eine et­was aus­führlichere Beschrei­bung des Prob­lems, welch­es ich in ei­nem Fo­rum gele­sen habe:

Ein Liefer­ant schickt ei­nem Händ­ler Lis­ten in ei­nem For­mat, welch­es zwar über­sichtlich ist, aber für für ei­ge­ne Auswer­tun­gen und Lis­ten-Erstel­lun­gen  in der Form nicht ge­nutzt wer­den kann.

Die Excel-Auf­stel­lung sieht so aus, wie in die­ser Da­tei (es sind übri­gens die Orig­i­nal-Dat­en aus dem Netz). Ge­braucht wird aber eine Lis­te, wo jede Artikel­num­mer so oft aufge­führt wird, wie Acces­so­rys vorhan­den sind. Das be­deu­tet 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 Fo­rum:

IST und SOLL im Bild dar­ge­stellt (aus Fo­rum ko­piert)

Die Spal­te A:B (rosé mar­kiert) stel­len den IST-Zus­tand dar, Spal­te D:E das SOLL, also das Wun­schziel; der Ziel­bere­ich kann natür­lich in ei­nem getren­nten Blatt sein.

▲ nach oben …

 Lö­sung per Ma­kro/VBA

Eine mög­li­che Lö­sung ist ein Ma­kro, eine VBA-Rou­tine. Die Basis-Dat­en, wel­che im­por­tiert oder ge­la­den wur­den, ste­hen im Tabel­len­blatt Roh­Dat­en. Es exis­tiert ein weit­eres Tabel­len­blatt, wo die ges­plit­teten Dat­en hin­ein ge­schrie­ben wer­den: Split­Da­ta. Um das Ma­kro schlank zu hal­ten, habe ich auf eine Über­prü­fung der Exis­tenz der Ziel-Da­tei ver­zich­tet. Die muss vorhan­den sein. Die­ser Code führt zum Er­folg:

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 an­de­re Blat­tna­men bevorzu­gen, pas­sen Sie den Code bit­te an.

▲ nach oben …

Lö­sung per Power Que­ry

Wenn Ihr Ex­cel Pow­er Que­ry nut­zen kann, dann bie­tet sich für sol­che Aktio­nen die­ses Add-In bzw. die­se Funk­tion­al­ität an. Zu Be­ginn ist das Vorge­hen in ²⁰¹⁰⁄₁₃ und 2016 unter­schiedlich, im wichtig­sten Teil je­doch gle­ich.

Ex­cel ²⁰¹⁰⁄₂₀₁₃

Voraus­set­zung ist natür­lich, dass Sie das Add-In auch instal­liert ha­ben. Klick­en Sie auf den Me­nü­punkt Pow­er Que­ry und

Ex­cel 2016

In die­ser Ver­sion ist Pow­er Que­ry bere­its inte­gri­ert. Acht­en Sie dar­auf, dass eine be­lie­bi­ge Zel­le im Bere­ich der Dat­en mar­kiert ist. Klick­en Sie auf den Me­nü­punkt Dat­en und in der Grup­pe Ab­ru­fen und trans­formieren wäh­len Sie Aus Ta­bel­le. Kon­trol­lieren Sie die An­ga­ben des Dialogfen­sters und bestäti­gen Sie mit OK.

▲ nach oben …

Alle Ver­sio­nen

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

Die im­por­tier­te Ta­bel­le im Abfrage-Edi­tor

Die ers­te Spal­te ist automa­tisch mar­kiert. Klick­en Sie in die Spal­te Acces­so­ry und wäh­len Sie in der Grup­pe Trans­formieren das Sym­bol Spal­te tei­len. Da das Kom­ma die einzel­nen Wer­te tren­nt und auch meh­re­re Kom­mas als Teil­er fun­gie­ren, wäh­len Sie im Drop­Down Nach Trennze­ichen und akzep­tieren die Vor­gaben. – Mit OK bestäti­gen.

Bis zur max­i­malen An­zahl der Ele­mente wer­den Spal­ten an­ge­legt und mit dem entsprechen­den Wert oder null ge­füllt. Die Über­schriften wer­den durch ei­nen Punkt und eine fort­laufende Num­mer er­gänzt.

Mar­kie­ren Sie nun die Spal­te Artikel­num­mer. Wäh­len jet­zt im Menü Trans­formieren, Grup­pe Be­lie­bi­ge Spal­te das Sym­bol Spal­ten ent­piv­otieren. Klick­en Sie dort auf den Drop­Down-Pfeil  und wäh­len dann den Punkt An­de­re Spal­ten ent­piv­otieren. Mar­kie­ren Sie die Über­schrift der mit­tleren Spal­te (Attrib­ut), Recht­sklick und Ent­fer­nen oder über das Sym­bol Spal­ten ent­fer­nen. Die Über­schrift der zweit­en Spal­te (Wert) soll­ten Sie in Acces­so­ry än­dern. Menü Da­tei | Schlie­ßen und la­den, Pow­er Que­ry er­zeugt ein neu­es Arbeits­blatt und füllt die er­zeug­ten Dat­en in eine Intel­li­gente Ta­bel­le/Lis­te.

Da­mit ist die Auf­gabe er­füllt. Die Dat­en kön­nen so bes­tens mit ein­er Piv­ot­Table aus­gew­ertet wer­den. Hin­weis: Die Artikel­num­mer 99080 ent­hielt kei­ne Dat­en und wur­de ent­spre­chend nicht in die Lis­te über­nom­men.

▲ nach oben …

Rück­mel­dun­gen / Feed­back ger­ne per Mail an mich (G.​Mumme@​Excel-​ist-​sexy.​de)

Hat Ih­nen der Bei­trag ge­fal­len?
Er­leich­tert die­ser Bei­trag Ihre Ar­beit?

Dann wür­de ich mich über ei­nen Bei­trag Ihrer­seits z.B. 2,00 € freu­en … (← Klick mich!)