Excel: Mehrere Zeilen in Spalten zusammenfassen

[mada12345]

Hallo,

sorry für den merkwürdigen Betreff, wusste aber nicht wie ich es besser beschreiben sollte.

Hier ein Beispiel Bild, der Vorgabe/Ausgangs Excel. Die farbliche Markierung ist nur wegen der besseren Unterscheidung. Das sind insgesamt über ca. 10000 Zeilen, mit ca. 100 verschieden NAMENx.

Ziel ist in einem neuem Tabellen Blatt, automatisiert die jeweiligen AktionX zu den NAMENx, Spaltenweise zuzuordnen, siehe Beispiel Bild.

Spalte A aus der Vorgabe/Ausgangs Excel habe ich über ‚Doppelte Einträge entfernen‘ in das neue Tabellen Blatt über Transponieren als Spaltenüberschrift (Zeile 1) eingefügt.
Jetzt fehlt mir halt eine Formel um die Zuordnung der AktionX zu der Spaltenüberschrift zu NAMEx vom Vorgabe/Ausgangs Excel hinzubekommen.
Pivot Tabelle habe ich auch schon probiert, hat aber nicht das gewünschte Ergebnis geliefert.
Habe schon Google bemüht, aber entweder mit den falschen Suchbegriffen oder ich habe die Lösungsvorschläge nicht verstanden.

Bin für jede Anregung dankbar.
Vielen Dank und Gruß

 

[DeusoftheWired]

Klassischer Fall für die Funktion Transponieren.

 

[cumulonimbus8]

Für mich ist das eine VBA-Übung.

Transponieren - das kommt Uhrzetilich dumm - ist ein Spiegeln um eine DIaginale. Aber hier sind die Quellen »asymmetrisch«, da sehr ich keine reelle Chance.

CN8

 

[mada12345]

Hatte ich vergessen zu erwähnen, das ich das auch schon probiert habe.
Wenn ich das über Transponieren mache, kommt folgendes.

 

[DeusoftheWired]

Hrm, ihr habt recht.

Die Spaltenüberschriften aus dem SOLL-Screenshot in #1 (also NAME1, NAME2, NAME3 usw. in Zeile 1) bekommt man durch ein Kopieren von Spalte A in eine Hilfsspalte, auf die man dann Duplikate entfernen losläßt. Was dann übrigbleibt, wird transponiert. Das ist aber nur die erste Hälfte der Geschichte. Ich komme nicht drauf, wie man dann die Zuordnung der Aktionen zu den Namen herstellt. SVERWEIS fällt aus, weil es keine einzigartigen Werte sind. Der Zusammenhang zwischen Name und Aktion besteht ja nur in ihrer Zellposition. Vielleicht könnte man da etwas mit einer Hilfsspalte basteln, in die man eine Kombination aus Spalte A und Spalte B schreibt (=A2&"-"&B2) und dann auf dem Tabellenblatt mit den transponierten Spaltenüberschriften etwas über FINDEN oder SUCHEN anstellt, in das man NAME1 usw. einfließen läßt.

 

[grapple]

Pivot-Tabelle geht. Man braucht aber mehrere und muss jeweils entsprechend filtern.

 

[mada12345]

Danke für den Hinweis.
Aber muss man dann je Spalte manuell individuell filtern?
Ist ja bei ca. 100 Spalten, also von NAME1-NAME100, sehr aufwendig.

 

[ohmsl]

Hallo,
das geht mit PowerQuery.

 

[mada12345]

Danke für die Beispiel Datei.
Mit PowerQuery kenn ich mich nicht wirklich aus, muss ich mir mal genauer anschauen.

 

[ohmsl]

Hallo,
Die Tabelle erstmal in diese Form bringen.
https://svens-excel-welt.blogspot.com/2017/01/power-query-mehrere-werte-in-einer.html

Anschließend nur noch die zweite Spalte aufteilen (Text in Spalten) und die Tabelle anschließend transponieren

 

[mada12345]

Nochmal Danke,
hoffe das ich das dann hinbekomme.

 

[Janush]

Kommt sowas öfter vor? Dann, wie CN8 schon geschrieben hat, mit VBA ein Makro basteln, welches sich auf Knopfdruck darum kümmert...Easy :-)

Edit: Wenn die Liste am Anfang nach Namen sortiert wird, ist es noch einfacher.

Edit2: Ich habe das mal schnell zusammen geschustert. Funktioniert nur auf einer sortieren Liste, aber das sollte ja kein Problem sein.

Im VBA Editor ein neues Modul hinzufügen und diesen Code reinklatschen. Dann Sheet1 mit deinem Worksheetnamen ersetzen. Dann einfach auf Play drücken, wenn sich der Coursor in dieser Sub befindet.

Option Explicit

Public Sub Umsortieren()

Dim x As Long
Dim y As Integer
Dim z As Integer

Dim currentName As String

Dim newSheet As Worksheet

Set newSheet = ThisWorkbook.Worksheets.Add

x = 2 'Wir starten in Zeile 2 denn wir ignorieren die Überschriften
y = 0

With Tabelle1 'Hier den Namen deines Quellsheets hinschreiben

    Do
        If Not StrComp(currentName, .Cells(x, 1).Value, vbTextCompare) = 0 Then
            y = y + 1
            z = 1
            currentName = .Cells(x, 1).Value
            newSheet.Cells(z, y).Value = .Cells(x, 1).Value
        End If
      
        z = z + 1
       
        newSheet.Cells(z, y).Value = .Cells(x, 2).Value

        x = x + 1
   
    Loop Until StrComp(.Cells(x, 1).Value, vbNullString, vbBinaryCompare) = 0

End With

End Sub

Viel Glück :-)

 

[mada12345]

Hallo Janush,
vielen Dank dafür, sowas habe ich gesucht.
Allerdings passt das noch nicht so ganz. Das VBA gibt im Ergebnis-Tabellenblatt die Werte erst ab Zeile 3 aus.

Denke ich habe bei der Umsetzung keine Fehler gemacht.
Ich habe jetzt schon etwas im VBA mit den Variablen gespielt aber ich bekomme es nicht hin.
Wäre super nett, wenn jemand noch mal drüber schauen könnte.

Danke und Gruß

 

[cumulonimbus8]

Ich rate mal: x=2 und diesez y das nicht auf 1 oder so definiert ist sondern nach dem DIM einfach gezählt wid.
Auch currentName wird nicht mit einem Startwert gefüllt.
CN8

 

[Janush]

Aaahhh, so knapp. Es macht natürlich keinen Sinn x hochzuzählen bevor der Wert geschrieben wird.

x = x + 1 muss eine Etage tiefer, als letzter Aufruf in der Schleife.

Eben doch nur geschustert

 

[mada12345]

Ihr seid so gut, Danke jetzt klappt es.

 

[Janush]

Ich rate mal: x=2 und diesez y das nicht auf 1 oder so definiert ist sondern nach dem DIM einfach gezählt wid.
Auch currentName wird nicht mit einem Startwert gefüllt.
CN8

Im Grunde gebe ich dir Recht, das y sollte man eigentlich mit 0 initialisieren. Allerdings habe ich in VBA noch nie einen Müll-Wert bekommen, zumindest nicht für die einfachen Typen. Dasselbe gilt für currentName, obwohl hier ja am Anfang nur etwas anderes als der erste Name in der Liste drinstehen muss.

Ich passe den Code ein wenig an, damit er auch funktioniert, wenn jemand über die Suchfunktion hier landet.

 

[mada12345]

Nochmal vielen Dank für deine Unterstützung.
Angepasstes VBA funktioniert.

 

[Janush]

Ist doch super wenn es geht :-)

Wie lange läuft das "Programm" bei deinen ~10k Zeilen?

 

[cumulonimbus8]

Allerdings habe ich in VBA noch nie einen Müll-Wert bekommen

Erschlage mich, aber wenn du mal nach Excel nevrt googlest müssten da VBA-Pleiten notiert sein die auf mangelnde Initialisierung zurückzuführen sind. Meine wenigstens dies dort aufgeschnappt zu haben.
CN8