Für die Übungen eine solide Basis zu haben, werden sie mit der gleichen Datenbasis arbeiten wie ich. Zu diesem Zweck laden Sie diese Datei mit den entsprechenden Muster-Tabellen herunter.

Zwei Spalten (ohne Namen)

Gegeben ist eine Liste, die aus 2 Spalten besteht: Name der Abteilung und Anzahl der Mitarbeiter. Das Ganze stellt sich so dar:

Die Ausgangslage

Das Ziel ist es, je nach eingetragener Anzahl der Mitarbeiter in der 2. Spalte die eigentliche Zeile so oft zu wiederholen, bis die Anzahl der in der 2. Spalte aufgeführten Zahl erreicht ist. Sollte in der 2. Spalte ein Leerereintrag sein, also keine Zahl stehen, dann soll dennoch eine Zeile bestehen bleiben. Im Anschluß soll zusätzlich eine 3. Spalte generiert werden, wo als Platzhalter das Wort Name steht.

▲ nach oben …

Mit Power Query

Konzentrieren Sie sich erst einmal auf das Arbeitsblatt 2 Spalten (1). Sie können die Daten als Tabelle bzw. Liste formatieren, erforderlich ist es jedoch nicht. Das wird automatisch geschehen, wenn sie die Liste in den Abfrage-Editor laden. – Sorgen Sie dafür, dass eine beliebige Zelle mit Inhalt markiert ist. Aktivieren Sie nun die Power Query Funktionalität und laden Sie die Daten über den Menüpunkt Aus Tabelle in den Query Editor.

Sie erkennen, dass mit Ausnahme der Abteilung 5 jeweils die Zahl der Mitarbeiter in der 2. Spalte vermerkt ist. Bei Abteilung 5 ist der Wert null eingetragen, dem Synonym für eine leere Zelle. Das endgültige Ziel ist ja, die entsprechende Anzahl von Zeilen zu generieren und in die 3. Spalte den Platzhalter „Name“ zu schreiben. Ein möglicher Weg besteht darin, erst einmal eine Benutzerdefinierte Spalte zu schaffen, wo das Wort “Name”, gefolgt von einem Komma in der entsprechenden Anzahl steht. Dazu wechseln Sie im Editor in das Register Spalte hinzufügen, wählen dort den Punkt Benutzerdefinierte Spalte und geben dort im Dialogfenster diese Formel nach dem = ein:

Das Dialogfenster mit der Formel

Der Deutlichkeit wegen hier noch einmal die Formel als Text:

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

Beachten Sie, dass die Funktions-Namen “case sensitiv” sind, sie müssen also exakt so in der hier dargestellten Groß- Kleinschreibung eingegeben werden. Ein kleiner Tipp: Den Feldnamen, der stets in eckige Klammern eingefasst sein muss, können Sie ganz leicht aus dem rechten Teil der Liste mit der Überschrift Verfügbare Spalten übernehmen. Dabei werden dann auch die [] automatisch korrekt mit eingefügt. Nach einem OK ist eine 3. Spalte eingefügt worden:

Die dritte Spalte ist nun eingefügt worden

Auch hier ist die Zeile 5 wieder mal die Ausnahme von der Regel. Anstatt dass nichts oder null in der Zelle steht, sehen Sie dort die englische Bezeichnung für einen Fehler. Den sollten und werden Sie im nächsten Schritt beseitigen. Wählen Sie dazu das Register Transformieren, markieren Sie erforderlichenfalls die neu erstellte Spalte und im Me­nü­band klicken Sie neben dem Symbol für Werte ersetzen auf den Dropdownpfeil . Anschließend wählen Sie den 2. Menüpunkt: Fehler ersetzen. Alternativ können Sie diese Wahl auch per Rechtsklick in die Spaltenüberschrift auf direktem Wege treffen. Im sich auftuenden Dialogfenster tragen Sie als Ersatz-Zeichen für den Fehler beispielsweise nur ein Minuszeichen ein und bestätigen Sie mit OK. Damit haben sie jetzt eine gute Basis für die folgenden Schritte.

Auch wenn die automatisch erzeugte Überschrift dieser Spalte nicht gerade „spritzig“ ist, sie kann so bleiben. Sie werden diese Spalte später sowieso öschen. Es steht Ihnen aber auch frei, eine beliebige andere Überschrift einzutragen. – Für den nächsten Schritt muss die Spalte markiert sein. Immer noch im Menü Transformieren (alternativ auch Start) klicken Sie auf das Symbol Spalte teilen und wählen dort Nach Trennzeichen. Ausnahmsweise sind die Vorgaben hier absolut korrekt. Das Trennzeichen ist ein Komma und es soll bei jedem Vorkommen des Trennzeichens eine neue Spalte beginnen.

Sie könnten zwar dieser Stelle schon die letzte Spalte löschen, die ja keine wirklichen Werte enthält weil nach dem letzten Komma ja nichts weiteres in den Ursprungsdaten folgte. Aber da bei einer Datenquelle mit extrem vielen Zeilen nicht unbedingt immer gewährleistet ist, dass nicht doch noch irgendwo ein sinnvoller Wert dort steht, belassen Sie es im Moment dabei.

Nun werden sie eine der faszinierendsten Möglichkeiten des Power Query einsetzen. Die ersten beiden Spalten dieser Tabelle sollen ja so wie sie sind an der Position bestehen bleiben, allerdings bei Bedarf jeweils nach unten vervielfacht, also kopiert werden. Markieren Sie die Spalten Abteilung und Mitarbeiter, Rechtsklick in eine der beiden Überschriften und wählen Sie Andere Spalten entpivotieren. Die Schnelligkeit und Akkuratesse des Ergebnisses finde ich immer wieder beeindruckend. – In der Spalte Wert steht ja nun exakt so oft der Text Name, wie als Anzahl bei Mitarbeiter angegeben ist. Danach folgt üblicherweise eine Leerzeile. Sie wissen, dass das letzte Komma in der dritten Spalte dafür verantwortlich war. – Auch hier ist die Abteilung 5 wieder die Ausnahme: Es steht „Name“ drin sondern wegen der Fehlerkorrektur das Minuszeichen. Und es folgt auch keine Leerzeile.

Klicken Sie der Spaltenüberschrift Wert auf den Dropdown-Pfeil und entfernen Sie das Häkchen bei (leer). Damit ist das Ziel fast erreicht. Rechtsklick in die Überschrift Attribut und die 2. Auswahl Entfernen anklicken. Sollten in dieser Spalte noch Fehlerwerte enthalten sein, werden sie diese auf die gleiche Weise löschen. Das Ziel ist erreicht, entweder ein Wechsel zum Menü Start oder Aufruf des Registers Datei. In jedem Fall Schließen & laden und die Daten werden an eine neue Tabelle in einem neu erstellten Arbeitsblatt geschrieben.

▲ nach oben …

Lösung per VBA

Los geht es mit dem Blatt 2 Spalten (2). Es ist eine Kopie der zuerst verwendeten Tabelle, naturgemäß im „unberührten“ Zustand. Da dieses nicht der Platz für ein VBA-Seminar ist, liefere ich Ihnen nur den passenden Code, der zugegebenermaßen knapp kommentiert ist. Sie sollten also genügend Gefühl für diese Sprache haben, um Anpassungen selber vornehmen zu können. Und wenn alle Stricke reißen, gibt es ja noch unseren Sponsor, der ihnen in der Richtung gerne behilflich 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 straffen. Aber dann leidet die Übersichtlichkeit und ich möchte auch Einsteigern die Chance geben, auf dem hier Gelernten aufzubauen. Wichtig ist, dass Sie diesen Code in das Modul des entsprechenden Blattes mit den Ursprungsdaten, also Sheet 2 | Spalten (2) einfügen.

▲ nach oben …

3 Spalten (mit Namen)

Auf den ersten Blick scheint das ja viel einfacher zu sein, es brauchen ja keine Platzhalter generiert werden und die dritte Spalte existiert ja auch schon. Aber sehen Sie sich doch einmal das Register 3 Spalten (1) an…

die 3. Spalte mit den Namen

Hier gilt die Grundregel: Entweder stimmt die Zahl der Namen (einschließlich Platzhalter) mit der Zahl in der Spalte Mitarbeiter überein oder es werden die Namen der Mitarbeiter als Anzahl gewertet. Es geht zwar auch anders, aber hier ist aus rein pragmatischen Gründen die Entscheidung nun einmal so gefallen.

▲ nach oben …

Lösung mit Power Query

Wie gehabt werden sie die Daten zu Beginn in den Abfrage-Editor des Power Query laden. Bis dahin gibt es keine Besonderheit zu beachten. Per Definition ist für jeden Mitarbeiter in der jeweiligen Abteilung ein Name oder Platzhalter eingetragen. Das ist gut in Abteilung 7 zu erkennen, wo Frau oder Herr Petersen und eine derzeit nicht benannte Person arbeiten. Und die Abteilung 5 ist natürlich leer, hier ist der Wert null eingetragen.

Bei den Namen fällt auf, dass nach dem letzten Eintrag kein (überflüssiges) Komma steht. Das ist schon einmal positiv. Für die eigentliche Auswertung brauchen Sie die Zeilen aus der 2. Spalte nicht. Sie könnten die Spalte löschen, aber unter Umständen kann sie in der endgültigen Tabelle doch noch gewünscht werden. Darum belassen wir diese Spalte so, wie sie ist.

Auf den ersten Blick scheint nun zu dem Zwischenstand bei der vorhergehenden Übung kein prinzipieller Unterschied in der Spalte mit den Namen zu bestehen. Dort wurde der Text “Name” in entsprechende Anzahl geschrieben, hier sind es die realen Namen. Auf den zweiten Blick jedoch gibt es einen wichtigen Unterschied: Die Namen sind nicht nur durch ein Komma sondern durch ein Komma mit einem nachfolgenden Leerzeichen getrennt. Und das müssen Sie beim splitten in mehrere Spalten berücksichtigen.

Markieren Sie die Spalte Namen, Spalte teilen | Nach Trennzeichen. Im Dialog wählen Sie statt des Kommas die Auswahl –Benutzerdefiniert–. In dem nun sichtbaren Feld unterhalb der Auswahl tragen Sie genau die Zeichenfolge ein, welche die Spalten trennen soll: ,  Ein Komma gefolgt von einem Leerzeichen. Die anderen Möglichkeiten können so bleiben. Nach einem OK stellt sich die Abfrage so dar, wie sie es gewiss erwartet haben.

Das weitere Vorgehen ist identisch mit dem, welches sie bereits geübt haben: Die beiden ersten Spalten markieren, den Punkt Andere Spalten entpivotieren auswählen und anschließend die Spalte Attribut löschen. Damit ist diese Aufgabe erledigt.

▲ nach oben …

Lösung per VBA

Vom Grundsatz her ist diese VBA-Lösung nur ähnlich, weil wichtige Teile des Codes nicht übernommen werden könnten. Aber sehen Sie selbst …

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 obigen beispiels folgend in das Modul des zweiten Blattes für die 3-spaltigen Werte: 3 Spalten (2A).

Hinweise: Im “richtigen Leben” werden Sie aus Geschwindigkeits-Gründen und damit der Bildschirm nicht flackert für den Ablauf des Makros die Bildschirm-Aktualisierung (ScreenUpdating) abschalten. Und auch eine Fehlerbahandlung ist hier nicht eingebaut. Als Einsteiger werden Sie den Code erst einmal analysieren und sich entweder in Geduld üben oder etwas dazulernen, um diese Features einzubauen.

Es gibt für solche Fälle gewiss auch eine Formel-Lösung. Zugegeben, ich mag keine langen und intransparenten Formeln, die erst nach mehreren Minuten Grübeln über die Funktionsweise einer neuen Situation angepasst werden können. Darum verzichte ich hier auch auf die Lösung. 😉 

▲ nach oben …