Office: (Office 365) Zeilen gruppieren und zugehörige Spalte aufteilen

Hallo zusammen,
das ist mein erster (aber vermutlich nicht letzter ;) ) Beitrag hier.
Ich verzweifel gerade ein bisschen...
Folgendes:
Ich habe eine Tabelle mit 2 Spalten. In der ersten Spalte stehen Produktgruppen, in der zweiten einzelne Produkte dazu.
Ein Bsp. habe ich als Bild hochgeladen.
Ich habe in der ersten Spalte also quasi Zeilen mit Produktgruppen A, B, C, ... und in der zweiten Spalte Text dazu.
Ich möchte nun die Zeilen nach den Produktgruppen gruppieren, dass jede Produktgruppe nur einmal vorhanden ist. Die entsprechenden Texte sollen entweder in separate Spalten oder als Text mit Trennzeichen getrennt in eine Zelle.
Ich hab mich etwas mit Power Query und Spalten pivotieren versucht, allerdings bekomm ich dann die Textinhalte in der zweiten Spalte nur als neue Spaltennamen und kann die dann nicht mehr in eine gemeinsame Zelle packen.
Über TEXTVERKETTEN kann ich ja einzelne Zellen verbinden, aber mir fehlt das richtige Vorgehen davor dazu ...
Hoffe, das ist nicht zu unverständlich geschrieben ...

 

Hi,

falls du es mit einem Makro lösen willst:

Code:

Sub Zusammenfassen()
    Dim lngZeile As Long
    Dim lngZiel As Long
    Dim lngZaehler As Long
    Dim strWerte
    lngZiel = 1
    For lngZeile = 1 To IIf(IsEmpty(Cells(Rows.Count, 1)), Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)
        lngZaehler = Application.CountIf(Range(Cells(1, 1), Cells(lngLetzte, 1)), Cells(lngZeile, 1))
        Cells(lngZiel, 4) = Cells(lngZeile, 1)
        strWerte = Range(Cells(lngZeile, 2), Cells(lngZeile + lngZaehler - 1, 2))
        Cells(lngZiel, 5) = Join(Application.Transpose(strWerte), "; ")
        lngZiel = lngZiel + 1
        lngZeile = lngZeile + lngZaehler - 1
    Next lngZeile
End Sub

Bedingung ist, dass deine Tabelle nach Spalte A sortiert ist - so wie in der hochgeladenen Arbeitsmappe vorgegeben.

---

 

Hola,
du brauchst Eindeutig() für die Gruppen und Textverketten() mit Filter() für die Auflistung.
Mit einer Exceldatei könnte man mehr anfangen.
Gruß,
steve1da

 

Hallo,

ich habe die Fleißaufgabe gemacht und in Spalte D und E die von steve1da erwähnten Formeln eingefügt.

 

Wow, vielen Dank euch!
Das nächste Mal lad ich die Excel gleich hoch, danke für die Fleißaufgabe! :)
Ist genau das Ergebnis, was ich gebraucht habe, jetzt muss ich mich da aber fast noch bisschen damit beschäftigen, dass ich es auch komplett verstehe und ähnliche Sachen selber lösen kann.
Danke auch Beverly für das Makro, ich löse es aber doch lieber über die Formel!

 

Was mir jetzt noch auffällt: Ich kann mein Ergebnis dann nicht in eine Tabelle überführen, richtig? Dann kann ich damit leider auch nicht im PowerQuery weiterarbeiten?
Ich kann natürlich wieder eine weiteres Hilfsblatt erstellen, aber würde ich natürlich, wenn möglich, vermeiden ...

 

Hi,

was meinst du damit - dass du das Ergebnis nicht in einem anderen Zellbereich sondern anstelle der Originaldaten in Spalte A und B erhalten möchtest? In diesem Fall bleibt nur VBA - hier der angepasste Code:

Code:

Sub Zusammenfassen()
    Dim lngZeile As Long
    Dim lngZaehler As Long
    Dim lngLetzte As Long
    Dim strWerte
    lngLetzte = IIf(IsEmpty(Cells(Rows.Count, 1)), Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)
    For lngZeile = 1 To lngLetzte
        lngZaehler = Application.CountIf(Range(Cells(1, 1), Cells(lngLetzte, 1)), Cells(lngZeile, 1))
        strWerte = Range(Cells(lngZeile, 2), Cells(lngZeile + lngZaehler - 1, 2))
        Cells(lngZeile, 2) = Join(Application.Transpose(strWerte), "; ")
        Range(Cells(lngZeile + 1, 1), Cells(lngZeile + lngZaehler - 1, 2)).ClearContents
        lngZeile = lngZeile + lngZaehler - 1
    Next lngZeile
    Range(Cells(1, 1), Cells(lngZeile, 2)).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub

---

 

Hallo,

wenn ich gewusst hätte, dass du mit strukturierten Tabellen weiterarbeiten möchtest, hätte ich die Formel gleich anders angelegt.

Im Arbeitsblatt "TabelleAlt" sind die bisherigen Formeln enthalten - in mehreren Varianten.

Im Arbeitsblatt "TabelleNeu" ist eine Lösung enthalten, die für die Ermittlung der (eindeutigen) Gruppen A, B, C, ... PowerQuery verwendet.
Sobald du in der Tabelle "tbDaten" eine weitere Zeile zB. am Ende anfügst (zB. habe ich D / Text12 angefügt) und einen Rechtsklick in die rechte Tabelle "tbGruppierteListen" machst, wobei du den Befehl "Aktualisieren" ausführst, so aktualisiert sich die rechte Tabelle "tbGruppierteListen" so, wie du es erwartest - mit Hilfe von PowerQuery.

Was du damit meinst, weiß ich nicht.

Datei liegt bei.

 

Ein Shorty für XL365/XLWeb (eine einzige dynamische Formel):

D1: =LET(aa;A1:B11;nn;EINDEUTIG(INDEX(aa;;1));HSTAPELN(nn;NACHZEILE(nn;LAMBDA(a;TEXTVERKETTEN(";";;WENN(INDEX(aa;;1)=a;INDEX(aa;;2);""))))))

 

Hallo @lupo1,

eine vom Prinzip her gleiche Formel hatte ich dem User auch schon vorgeschlagen:

Code:

=LET(A;$A$1:$A$11; B;$B$1:$B$11; gr;EINDEUTIG(A);
WAHL({1.2};
     gr;
     NACHZEILE(gr; LAMBDA(zl;TEXTVERKETTEN("; ";WAHR;FILTER(B;A=zl))))
))

Sein Problem war/ist, dass er eine dyn.Arrayformel, deren Resultatsbereich sich über mehrere Zeilen erstreckt, nicht in einer strukturierten Tabelle einsetzen kann. Man erhält dann die Fehlermeldung #ÜBERLAUF!.
Aber in PowerQuery kann man dyn. struktur. Tabellen erzeugen. Ich habe PowerQuery als Ersatz für EINDEUTIG(A) eingesetzt. Darüber hinaus wollte der User PQ ohnehin im Weiteren verwenden.

 

Hallo @Benutzer999,

habe in der Zwischenzeit im Arbeitsblatt "TabelleNeu" die Transformation der ursprünglichen Tabelle (tbDaten) in die Tabelle "tbGruppierteListen" zur Gänze in PowerQuery durchgeführt. Ich musste mich selber erst schlau machen, wie man solche Spezialgruppierungen durchführt in PowerQuery.

Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="tbDaten"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte1", type text}, {"Spalte2", type text}}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ",{{"Spalte1", "Gruppen"}, {"Spalte2", "Texte"}}),
    #"Gruppierte Zeilen" = Table.Group(#"Umbenannte Spalten", {"Gruppen"}, {{"Spalte2", each Text.Combine([Texte],"; "), type nullable text}})
in
    #"Gruppierte Zeilen"

 

Hallo Exl,

ich hatte nur Deine 1. Tabelle geöffnet. Wenn ich alle Tabellen öffnen muss, wird mir das zuviel. :) Außerdem würde ich plädieren, nicht für jede kleine Aufgabe eine Datei anzuhängen ... bzw. bei mehreren Antworten dann nur einmal. Sonst ermattet das den Lesefluss in einem Forum. Übrigens konkurrieren auch ca. 200 Inline-Byte mit 12.000 Anhang-Byte Forumsplatzverbrauch, aber das interessiert heute niemanden mehr. Aber vielleicht interessiert, dass JEDER Anhang ein Medienbruch ist.

Ich würde außerdem geschmacklich die Sache auf die Spitze treiben und nicht unnötig aus A1:B11 zwei Argumente machen.

Außerdem bin ich gespannt, ob Du den rechten Teil gesamtdynamisch (in nur einer Formel, so wie zuletzt jetzt hier) auch auf EinzelZELLEN pro Zeile verteilen kannst (das war NICHT die Aufgabenstellung), statt sie in einer Zelle pro Zeile zu verketten.

Danke für das PQ-Beispiel.

 

D1: =LET(nn;A1:B11;m;INDEX(nn;;1);n;INDEX(nn;;2);o;EINDEUTIG(m);
HSTAPELN(o;TEXTTEILEN.2D(NACHZEILE(o;LAMBDA(a;TEXTVERKETTEN(" ";;WENN(a=m;n;"")))))))

mit der Lambda-UDF TEXTTEILEN.2D (maßgeblich von maninweb!)

 

Hallo @lupo1,

meine Lösung:

Code:

=LET(m;$A$1:$B$11; Leer;""; grpA;INDEX(m;;1); grp;EINDEUTIG(grpA);  mZll;SEQUENZ(ZEILEN(m);1;1);
Zll;ZEILEN(grp); Spp;MAX(ZÄHLENWENN(grpA;grp))+1;
MATRIXERSTELLEN(Zll;Spp; LAMBDA(zl;sp;
     LET(grpm;INDEX(grp;zl;1);WENNFEHLER(WENN(sp=1;grpm;INDEX(m;AGGREGAT(15;6;mZll/(grpA=grpm);sp-1);2));Leer)))
))

ohne einer anderen LAMBDA-UDF jedoch mit der neuen MATRIXERSTELLEN(..)-Funktion

 

5.000 Datensätze: Meine Lösung 1,5 bzw. 4 Sekunden, Deine Lösung 15 Sekunden
20.000 Datensätze: Meine Lösung 24 bzw. 38 Sekunden, Deine Lösung 214 Sekunden
50.000 Datensätze: Meine Lösung 150 bzw. xx Sekunden, Deine Lösung >1200 Sekunden (nicht mehr abgewartet)

Die zeitlich meiner Lösungen (ohne TEXTTEILEN.2D) ist so:
=LET(nn;A1:B11;m;INDEX(nn;;1);n;INDEX(nn;;2);o;EINDEUTIG(m);
HSTAPELN(o;GLÄTTEN(TEIL(WECHSELN(NACHZEILE(o;LAMBDA(a;TEXTVERKETTEN(" ";;WENN(a=m;n;""))));" ";WIEDERHOLEN(" ";299));SEQUENZ(;70;1;299);299))))

Nachteil: Beschränkt in der Breite.

Verwendet man nun noch EINDEUTIG(m) mit VERGLEICH als (interner) Hilfsspalte, dürfte das Ergebnis bei fast allen Umfängen nahezu sofort da sein.