Für die Übun­gen eine solide Basis zu haben, wer­den sie mit der gle­ichen Daten­ba­sis arbeit­en wie ich. Zu diesem Zweck laden Sie diese Datei mit den entsprechen­den Muster-Tabellen herunter.

Zwei Spalten (ohne Namen)

Gegeben ist eine Liste, die aus 2 Spal­ten beste­ht: Name der Abteilung und Anzahl der Mitar­beit­er. Das Ganze stellt sich so dar:

Die Aus­gangslage

Das Ziel ist es, je nach einge­tra­gen­er Anzahl der Mitar­beit­er in der 2. Spalte die eigentliche Zeile so oft zu wieder­holen, bis die Anzahl der in der 2. Spalte aufge­führten Zahl erre­icht ist. Sollte in der 2. Spalte ein Leer­ere­in­trag sein, also keine Zahl ste­hen, dann soll den­noch eine Zeile beste­hen bleiben. Im Anschluß soll zusät­zlich eine 3. Spalte gener­iert wer­den, wo als Platzhal­ter das Wort Name ste­ht.

▲ nach oben …

Mit Power Query

Konzen­tri­eren Sie sich erst ein­mal auf das Arbeits­blatt 2 Spal­ten (1). Sie kön­nen die Dat­en als Tabelle bzw. Liste for­matieren, erforder­lich ist es jedoch nicht. Das wird automa­tisch geschehen, wenn sie die Liste in den Abfrage-Edi­tor laden. – Sor­gen Sie dafür, dass eine beliebige Zelle mit Inhalt markiert ist. Aktivieren Sie nun die Pow­er Query Funk­tion­al­ität und laden Sie die Dat­en über den Menüpunkt Aus Tabelle in den Query Edi­tor.

Sie erken­nen, dass mit Aus­nahme der Abteilung 5 jew­eils die Zahl der Mitar­beit­er in der 2. Spalte ver­merkt ist. Bei Abteilung 5 ist der Wert null einge­tra­gen, dem Syn­onym für eine leere Zelle. Das endgültige Ziel ist ja, die entsprechende Anzahl von Zeilen zu gener­ieren und in die 3. Spalte den Platzhal­ter „Name“ zu schreiben. Ein möglich­er Weg beste­ht darin, erst ein­mal eine Benutzerdefinierte Spalte zu schaf­fen, wo das Wort „Name”, gefol­gt von einem Kom­ma in der entsprechen­den Anzahl ste­ht. Dazu wech­seln Sie im Edi­tor in das Reg­is­ter Spalte hinzufü­gen, wählen dort den Punkt Benutzerdefinierte Spalte und geben dort im Dialogfen­ster diese Formel nach dem = ein:

Das Dialogfen­ster mit der Formel

Der Deut­lichkeit wegen hier noch ein­mal die Formel als Text:

Text.Repeat("Name,",[Mitarbeiter])

Beacht­en Sie, dass die Funk­tions-Namen „case sen­si­tiv” sind, sie müssen also exakt so in der hier dargestell­ten Groß- Klein­schrei­bung eingegeben wer­den. Ein klein­er Tipp: Den Feld­na­men, der stets in eck­ige Klam­mern einge­fasst sein muss, kön­nen Sie ganz leicht aus dem recht­en Teil der Liste mit der Über­schrift Ver­füg­bare Spal­ten übernehmen. Dabei wer­den dann auch die [] automa­tisch kor­rekt mit einge­fügt. Nach einem OK ist eine 3. Spalte einge­fügt wor­den:

Die dritte Spalte ist nun einge­fügt wor­den

Auch hier ist die Zeile 5 wieder mal die Aus­nahme von der Regel. Anstatt dass nichts oder null in der Zelle ste­ht, sehen Sie dort die englis­che Beze­ich­nung für einen Fehler. Den soll­ten und wer­den Sie im näch­sten Schritt beseit­i­gen. Wählen Sie dazu das Reg­is­ter Trans­formieren, markieren Sie erforder­lichen­falls die neu erstellte Spalte und im Me­nü­band Klick­en Sie neben dem Sym­bol für Werte erset­zen auf den Drop­downpfeil . Anschließend wählen Sie den 2. Menüpunkt: Fehler erset­zen. Alter­na­tiv kön­nen Sie diese Wahl auch per Recht­sklick in die Spal­tenüber­schrift auf direk­tem Wege tre­f­fen. Im sich auftuen­den Dialogfen­ster tra­gen Sie als Ersatz-Zeichen für den Fehler beispiel­sweise nur ein Minusze­ichen ein und bestäti­gen Sie mit OK. Damit haben sie jet­zt eine gute Basis für die fol­gen­den Schritte.

Auch wenn die automa­tisch erzeugte Über­schrift dieser Spalte nicht ger­ade „spritzig“ ist, sie kann so bleiben. Sie wer­den diese Spalte später sowieso öschen. Es ste­ht Ihnen aber auch frei, eine beliebige andere Über­schrift einzu­tra­gen. – Für den näch­sten Schritt muss die Spalte markiert sein. Immer noch im Menü Trans­formieren (alter­na­tiv auch Start) Klick­en Sie auf das Sym­bol Spalte teilen und wählen dort Nach Trennze­ichen. Aus­nahm­sweise sind die Vor­gaben hier abso­lut kor­rekt. Das Trennze­ichen ist ein Kom­ma und es soll bei jedem Vorkom­men des Trennze­ichens eine neue Spalte begin­nen.

Sie kön­nten zwar dieser Stelle schon die let­zte Spalte löschen, die ja keine wirk­lichen Werte enthält weil nach dem let­zten Kom­ma ja nichts weit­eres in den Ursprungs­dat­en fol­gte. Aber da bei ein­er Daten­quelle mit extrem vie­len Zeilen nicht unbe­d­ingt immer gewährleis­tet ist, dass nicht doch noch irgend­wo ein sin­nvoller Wert dort ste­ht, belassen Sie es im Moment dabei.

Nun wer­den sie eine der faszinierend­sten Möglichkeit­en des Pow­er Query ein­set­zen. Die ersten bei­den Spal­ten dieser Tabelle sollen ja so wie sie sind an der Posi­tion beste­hen bleiben, allerd­ings bei Bedarf jew­eils nach unten vervielfacht, also kopiert wer­den. Markieren Sie die Spal­ten Abteilung und Mitar­beit­er, Recht­sklick in eine der bei­den Über­schriften und wählen Sie Andere Spal­ten ent­piv­otieren. Die Schnel­ligkeit und Akku­ratesse des Ergeb­niss­es finde ich immer wieder beein­druck­end. – In der Spalte Wert ste­ht ja nun exakt so oft der Text Name, wie als Anzahl bei Mitar­beit­er angegeben ist. Danach fol­gt üblicher­weise eine Leerzeile. Sie wis­sen, dass das let­zte Kom­ma in der drit­ten Spalte dafür ver­ant­wortlich war. – Auch hier ist die Abteilung 5 wieder die Aus­nahme: Es ste­ht „Name“ drin son­dern wegen der Fehlerko­r­rek­tur das Minusze­ichen. Und es fol­gt auch keine Leerzeile.

Klick­en Sie der Spal­tenüber­schrift Wert auf den Drop­down-Pfeil und ent­fer­nen Sie das Häkchen bei (leer). Damit ist das Ziel fast erre­icht. Recht­sklick in die Über­schrift Attrib­ut und die 2. Auswahl Ent­fer­nen anklick­en. Soll­ten in dieser Spalte noch Fehler­w­erte enthal­ten sein, wer­den sie diese auf die gle­iche Weise löschen. Das Ziel ist erre­icht, entwed­er ein Wech­sel zum Menü Start oder Aufruf des Reg­is­ters Datei. In jedem Fall Schließen & laden und die Dat­en wer­den an eine neue Tabelle in einem neu erstell­ten Arbeits­blatt geschrieben.

▲ nach oben …

Lösung per VBA

Los geht es mit dem Blatt 2 Spal­ten (2). Es ist eine Kopie der zuerst ver­wen­de­ten Tabelle, naturgemäß im „unberührten“ Zus­tand. Da dieses nicht der Platz für ein VBA-Sem­i­nar ist, lief­ere ich Ihnen nur den passenden Code, der zugegeben­er­maßen knapp kom­men­tiert ist. Sie soll­ten also genü­gend Gefühl für diese Sprache haben, um Anpas­sun­gen sel­ber vornehmen zu kön­nen. Und wenn alle Stricke reißen, gibt es ja noch unseren Spon­sor, der ihnen in der Rich­tung gerne behil­flich ist.  😉

Option Explicit

Sub SpaltenKopieren()
   Dim wksSrc As WorkSheet, wksDst As WorkSheet
   Dim lRowS As Long, lRowD As Long  'LastRow Source/Destination
   Dim wks As WorkSheet, DstWksExists As Boolean   'Ziel-Sheet
   Dim wksName2 As String
   Dim ZeS As Long, ZeD As Long, AnzMA As Variant
  
   Set wksSrc = Sheets("2 Spalten (2)")
   wksName2 = "2 Spalten (2A)"
   For Each wks In ThisWorkbook.Sheets
      If wks.Name = wksName2 Then
         DstWksExists = True
         Exit For
      End If
   Next wks
   If Not DstWksExists Then
      Sheets.Add after:=Sheets(ThisWorkbook.Sheets.Count)
      ActiveSheet.Name = wksName2
      Set wksDst = Sheets(wksName2)
   Else
      Set wksDst = Sheets(wksName2)
      wksDst.Cells.Delete
   End If
   'Im Ziel-Blatt die Überschriften schreiben
   With wksDst
      .Cells(1, 1) = wksSrc.Cells(1, 1)
      .Cells(1, 2) = wksSrc.Cells(1, 2)
      .Cells(1, 3) = "MA-Name"
   End With
   lRowS = LetzteZeile(wksSrc, 1)
   For ZeS = 2 To lRowS
      AnzMA = wksSrc.Cells(ZeS, 2)
      If AnzMA = "" Then AnzMA = 1
      lRowD = LetzteZeile(wksDst, 1)
      For ZeD = lRowD + 1 To lRowD + AnzMA
         With wksDst
            .Cells(ZeD, 1) = wksSrc.Cells(ZeS, 1)
            .Cells(ZeD, 2) = wksSrc.Cells(ZeS, 2)
            .Cells(ZeD, 3) = IIf(.Cells(ZeD, 2) = "", "-", "Name")
         End With
      Next ZeD
   Next ZeS
End Sub

Function LetzteZeile(wks As WorkSheet, Sp As Long) As Long
   LetzteZeile = wks.Cells(Rows.Count, Sp).End(xlUp).Row
End Function

Zugegeben, der Code lässt sich straf­fen. Aber dann lei­det die Über­sichtlichkeit und ich möchte auch Ein­steigern die Chance geben, auf dem hier Gel­ern­ten aufzubauen. Wichtig ist, dass Sie diesen Code in das Mod­ul des entsprechen­den Blattes mit den Ursprungs­dat­en, also Sheet 2 | Spal­ten (2) ein­fü­gen.

▲ nach oben …

3 Spalten (mit Namen)

Auf den ersten Blick scheint das ja viel ein­fach­er zu sein, es brauchen ja keine Platzhal­ter gener­iert wer­den und die dritte Spalte existiert ja auch schon. Aber sehen Sie sich doch ein­mal das Reg­is­ter 3 Spal­ten (1) an…

die 3. Spalte mit den Namen

Hier gilt die Grun­dregel: Entwed­er stimmt die Zahl der Namen (ein­schließlich Platzhal­ter) mit der Zahl in der Spalte Mitar­beit­er übere­in oder es wer­den die Namen der Mitar­beit­er als Anzahl gew­ertet. Es geht zwar auch anders, aber hier ist aus rein prag­ma­tis­chen Grün­den die Entschei­dung nun ein­mal so gefall­en.

▲ nach oben …

Lösung mit Power Query

Wie gehabt wer­den sie die Dat­en zu Beginn in den Abfrage-Edi­tor des Pow­er Query laden. Bis dahin gibt es keine Beson­der­heit zu beacht­en. Per Def­i­n­i­tion ist für jeden Mitar­beit­er in der jew­eili­gen Abteilung ein Name oder Platzhal­ter einge­tra­gen. Das ist gut in Abteilung 7 zu erken­nen, wo Frau oder Herr Petersen und eine derzeit nicht benan­nte Per­son arbeit­en. Und die Abteilung 5 ist natür­lich leer, hier ist der Wert null einge­tra­gen.

Bei den Namen fällt auf, dass nach dem let­zten Ein­trag kein (über­flüs­siges) Kom­ma ste­ht. Das ist schon ein­mal pos­i­tiv. Für die eigentliche Auswer­tung brauchen Sie die Zeilen aus der 2. Spalte nicht. Sie kön­nten die Spalte löschen, aber unter Umstän­den kann sie in der endgülti­gen Tabelle doch noch gewün­scht wer­den. Darum belassen wir diese Spalte so, wie sie ist.

Auf den ersten Blick scheint nun zu dem Zwis­chen­stand bei der vorherge­hen­den Übung kein prinzip­ieller Unter­schied in der Spalte mit den Namen zu beste­hen. Dort wurde der Text „Name” in entsprechende Anzahl geschrieben, hier sind es die realen Namen. Auf den zweit­en Blick jedoch gibt es einen wichti­gen Unter­schied: Die Namen sind nicht nur durch ein Kom­ma son­dern durch ein Kom­ma mit einem nach­fol­gen­den Leerze­ichen getren­nt. Und das müssen Sie beim split­ten in mehrere Spal­ten berück­sichti­gen.

Markieren Sie die Spalte Namen, Spalte teilen | Nach Trennze­ichen. Im Dia­log wählen Sie statt des Kom­mas die Auswahl –Benutzerdefiniert–. In dem nun sicht­baren Feld unter­halb der Auswahl tra­gen Sie genau die Zeichen­folge ein, welche die Spal­ten tren­nen soll: ,  Ein Kom­ma gefol­gt von einem Leerze­ichen. Die anderen Möglichkeit­en kön­nen so bleiben. Nach einem OK stellt sich die Abfrage so dar, wie sie es gewiss erwartet haben.

Das weit­ere Vorge­hen ist iden­tisch mit dem, welch­es sie bere­its geübt haben: Die bei­den ersten Spal­ten markieren, den Punkt Andere Spal­ten ent­piv­otieren auswählen und anschließend die Spalte Attrib­ut löschen. Damit ist diese Auf­gabe erledigt.

▲ nach oben …

Lösung per VBA

Vom Grund­satz her ist diese VBA-Lösung nur ähn­lich, weil wichtige Teile des Codes nicht über­nom­men wer­den kön­nten. Aber sehen Sie selb­st …

Option Explicit

Sub SpaltenKopieren2()
   Dim wksSrc As WorkSheet, wksDst As WorkSheet
   Dim lRowS As Long, lRowD As Long  'LastRow Source/Destination
   Dim wks As WorkSheet, DstWksExists As Boolean   'Ziel-Sheet
   Dim wksName2 As String
   Dim ZeS As Long, ZeD As Long, AnzMA As Variant
   Dim aNamen
   
   Set wksSrc = Sheets("3 Spalten (2)")
   wksName2 = "3 Spalten (2A)"
   For Each wks In ThisWorkbook.Sheets
      If wks.Name = wksName2 Then
         DstWksExists = True
         Exit For
      End If
   Next wks
   If Not DstWksExists Then
      Sheets.Add after:=Sheets(ThisWorkbook.Sheets.Count)
      ActiveSheet.Name = wksName2
      Set wksDst = Sheets(wksName2)
   Else
      Set wksDst = Sheets(wksName2)
      wksDst.Cells.Delete
   End If
   'Im Ziel-Blatt die Überschriften schreiben
   With wksDst
      .Cells(1, 1) = wksSrc.Cells(1, 1)
      .Cells(1, 2) = wksSrc.Cells(1, 2)
      .Cells(1, 3) = "MA-Name"
   End With
   
   lRowS = LetzteZeile(wksSrc, 1)
   For ZeS = 2 To lRowS
      With wksSrc
         If Len(.Cells(ZeS, 3)) > 0 Then
            aNamen = Split(.Cells(ZeS, 3), ", ")
            AnzMA = UBound(aNamen) + 1
         Else
            aNamen(0) = "-"
            AnzMA = 1
         End If
         lRowD = LetzteZeile(wksDst, 1)
      End With
      With wksDst
         For ZeD = lRowD + 1 To lRowD + AnzMA
            .Cells(ZeD, 1) = wksSrc.Cells(ZeS, 1)
            .Cells(ZeD, 2) = wksSrc.Cells(ZeS, 2)
            .Cells(ZeD, 3) = aNamen(ZeD - lRowD - 1)
         Next ZeD
      End With
   Next ZeS
End Sub

Function LetzteZeile(wks As WorkSheet, Sp As Long) As Long
   LetzteZeile = wks.Cells(Rows.Count, Sp).End(xlUp).Row
End Function

Dieser Code gehört der Logik des obi­gen beispiels fol­gend in das Mod­ul des zweit­en Blattes für die 3‑spaltigen Werte: 3 Spal­ten (2A).

Hin­weise: Im „richti­gen Leben” wer­den Sie aus Geschwindigkeits-Grün­den und damit der Bild­schirm nicht flack­ert für den Ablauf des Makros die Bild­schirm-Aktu­al­isierung (ScreenUp­dat­ing) abschal­ten. Und auch eine Fehler­ba­hand­lung ist hier nicht einge­baut. Als Ein­steiger wer­den Sie den Code erst ein­mal analysieren und sich entwed­er in Geduld üben oder etwas dazuler­nen, um diese Fea­tures einzubauen.

Es gibt für solche Fälle gewiss auch eine Formel-Lösung. Zugegeben, ich mag keine lan­gen und intrans­par­enten Formeln, die erst nach mehreren Minuten Grü­beln über die Funk­tion­sweise ein­er neuen Sit­u­a­tion angepasst wer­den kön­nen. Darum verzichte ich hier auch auf die Lösung. 😉 

▲ nach oben …