Excel - Zellen verketten anhand anderer Spalte

[Biertrinker]

Hallo zusammen.

Die Werte links habe ich einer Liste, also in Spalte A sind die Werte mehrfach aufgeführt weil für jeden Eintrag in Spalte C eine eigene Zeile erzeugt wird.

Ich würde es dann gerne so haben wie rechts, dass im Prinzip die Werte aus C verkettet werden bei denen Spalte A gleich ist.
Wie bekomme ich das hin? In meiner Liste habe ich über 22.000 Zeilen, deswegen wäre es am Besten über Power Query. Wenn das nicht geht auch anders, aber auf keinen Fall händisch.

Hintergrund ist, dass ich dann die Spalte F filtern muss, heißt: Zeige mir alle Zeilen bei denen in Spalte F dies und jenes nicht vorkommt, ich aber dann in Spalte E sehen muss welcher Name übrig bleibt.

Ich danke vorab für euren Input.

 

[steve1da]

Hola,
=TEXTVERKETTEN(",";WAHR;MTRANS(FILTER($C$1:$C$6;$A$1:$A$6=E1)))
Gruß,
steve1da

 

[calippo]

Sieht mir nach einem Anwendungsfall für eine Pivottabelle aus.

 

[Biertrinker]

Hola,
=TEXTVERKETTEN(",";WAHR;MTRANS(FILTER($C$1:$C$6;$A$1:$A$6=E1)))
Gruß,
steve1da

Danke für deinen Beitrag.
Textverketten Funktion habe ich noch nicht, ich bin noch auf 2016er Excel unterwegs. Andere Idee vielleicht?

Sieht mir nach einem Anwendungsfall für eine Pivottabelle aus.

Wüsste ich jetzt nicht wie, obwohl ich jeden Tag viel mit Pivots mache.
Pivots fassen zusammen was sie bekommen, die sind eher weniger dafür da, Daten zu ändern oder zu verketten. Wenn doch: gerne einen Hinweis wie.

 

[duAffentier]

Evtl das probieren:

• Daten nach Power Query laden:
  • Wähle eine Zelle in deinem Datenbereich aus.
  • Gehe zu „Daten“ in der Menüleiste und dann zu „Aus Tabelle/ Bereich abrufen“.
• Daten transformieren:
  • In Power Query angekommen, markiere die Spalte A.
  • Gehe zu „Transformieren“ und wähle „Gruppieren nach“.
  • Im „Gruppieren nach“-Fenster:
    • Wähle als „Gruppieren nach“-Schlüssel die Spalte A.
    • Gib einen Namen für die neue Spalte ein, z.B. „Verkettete Werte“.
    • Wähle als „Operation“ „Alle Zeilen“.
• Benutzerdefinierte Spalte für verkettete Werte hinzufügen:
  • Nachdem du die Daten gruppiert hast, hast du eine Spalte mit Tabellen als Werte. Du musst jetzt eine neue Spalte hinzufügen, um die Werte aus Spalte C zu verkettet.
  • Klicke auf „Benutzerdefinierte Spalte hinzufügen“.
  • Verwende folgenden Code in der Formel:
    powerquery
    • Code
  • Text.Combine([Verkettete Werte][Spalte C], ", ")

• • Ersetze „Spalte C“ mit dem tatsächlichen Namen deiner Spalte C.
• Unnötige Spalten entfernen und die Tabelle schließen:
  • Entferne alle Spalten, die du nicht brauchst, indem du sie rechtsklickst und „Entfernen“ wählst.
  • Klicke dann auf „Schließen & Laden“, um deine transformierte Tabelle in Excel zu laden.
• Filtern in Excel:
  • Nachdem deine Daten zurück in Excel geladen wurden, kannst du die Spalte F filtern, wie du es benötigst.

 

[tollertyp]

Wenn es einmalig gebraucht wird, könnte man es auch via VBA machen.

 

[Biertrinker]

Gerne, wie?

Also in meiner konkreten Datei stehen die Nachnamen in Spalte AC was dann verketten werden soll auf Basis derer steht dann in Spalte AL.

Ich bin ganz okay mit pivots aber bei VBA nicht ganz so fit.

 

[tollertyp]

Pivot geht in Excel nicht textbasiert, so weit ich weiß.

Man könnte Chatgpt fragen, wie ein VBA-Makro dafür aussehen könnte

Die Spaltennnummern passen nicht, hier wird A, B und C verwendet:

Sub Zusammenfassen()
    Dim lastRow As Long
    Dim dict As Object
    Dim rng As Range
    Dim cell As Range
    Dim name As String
    Dim text As String
 
    ' Spaltenbereich festlegen (annimm, dass deine Daten in Spalte A und B sind)
    Set rng = Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
 
    ' Letzte Zeile in Spalte A finden
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
 
    ' Ein neues Dictionary erstellen
    Set dict = CreateObject("Scripting.Dictionary")
 
    ' Durch jede Zeile in Spalte A laufen und Daten sammeln
    For Each cell In rng.Cells
        name = cell.Value
        text = cell.Offset(0, 1).Value
  
        If Not dict.exists(name) Then
            dict.Add name, text
        Else
            dict(name) = dict(name) & ", " & text
        End If
    Next cell
 
    ' Ergebnis in Spalte C schreiben
    For i = 1 To lastRow
        name = Cells(i, 1).Value
        If dict.exists(name) Then
            Cells(i, 3).Value = dict(name)
        End If
    Next i
End Sub

Füge diesen Code in den Visual Basic Editor ein (drücke Alt + F11 in Excel), erstelle ein neues Modul (Rechtsklick auf "Module" > "Insert" > "Module") und füge den Code ein. Dann kannst du das Makro ausführen, und die Daten in Spalte C werden entsprechend zusammengefasst. Stelle sicher, dass du vor dem Ausführen des Makros eine Sicherungskopie deiner Datei hast, falls etwas schief geht.

Edit:
Hat bei mir funktioniert, allerdings tauchen die mehrmals auf. Habe die Frage nicht gut gestellt.

Ergänzung (23. April 2024)

So, nochmal selbst mit ChatGPT Hand angelegt:

Ergebnis:

Der Code:

Sub Zusammenfassen()
    Dim lastRow As Long
    Dim dict As Object
    Dim rng As Range
    Dim cell As Range
    Dim name As String
    Dim text As String
  
    ' Spaltenbereich festlegen (annimm, dass deine Daten in Spalte A und B sind)
    Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
  
    ' Letzte Zeile in Spalte A finden
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
  
    ' Ein neues Dictionary erstellen
    Set dict = CreateObject("Scripting.Dictionary")
  
    ' Durch jede Zeile in Spalte A laufen und Daten sammeln
    For Each cell In rng.Cells
        name = cell.Value
        text = cell.Offset(0, 1).Value
      
        If Not dict.exists(name) Then
            dict.Add name, text
        Else
            dict(name) = dict(name) & ", " & text
        End If
    Next cell
  
    ' Ergebnis in Spalte E und F schreiben
    Range("E1").Value = "Name"
    Range("F1").Value = "Werte"
  
    Dim i As Long
    i = 2 ' Startzeile für Ergebnisse
  
    For Each n In dict.keys
        Cells(i, 5).Value = n ' Name in Spalte E schreiben
        Cells(i, 6).Value = dict(n) ' Werte in Spalte F schreiben
        i = i + 1 ' Zeilenindex erhöhen
    Next n
End Sub

5 bedeutet Spalte E, 6 bedeutet Spalte F bei "Cells(i, ...)", d.h. du kannst da die Zielspalten anpassen.

 

[Biertrinker]

Okay danke schon ma für deine Hilfe.
Jetzt muss noch noch meine Abfrage in PQ bearbeiten und das einfach in 2 Spalten machen.
Spalte A für den Nachnamen, Spalte B für die Buchstahbe und in Spalte C soll dann alles verkettet als Spalte B rein. Mal schauen ob ich es schaffe deinen Code zu antizipieren

 

[tollertyp]

Habe meinen Beitrag oben nochmal aktualisiert.

Edit:
"text = cell.Offset(0, 1).Value" holt den Wert rechts von der aktuellen Zelle würde ich sagen, d.h. wenn du den Bereich der Range anpasst, wo "A" steht, müsste das mit dem Offset schon korrekt sein.

Und wichtig halt: Vorher ein Backup der Daten haben :-)

Edit:
Und die Zeile beschreibt, wie die Zusammenführung aussehen soll:

dict(name) = dict(name) & ", " & text

Hier also getrennt mit Komma und Leerzeichen.

 

[Biertrinker]

Alter Falter, LMAA! Es geht! Wow toll... sooo vielen herzlichen Dank dir!
Jetzt muss ich noch schauen wie ich das statt in Spalte C in ein eigenes Blatt bekomme, damit ich mir einen Arbeitsschritt spare. Wow, vielen vielen Dank!

 

[tollertyp]

Sind die beiden Zeilen:

Cells(i, 5).Value = n ' Name in Spalte E schreiben
Cells(i, 6).Value = dict(n) ' Werte in Spalte F schreiben

Da kann man z.B. das machen:

Worksheets("Tabelle3").Cells(i, 5).Value = n ' Name in Spalte E schreiben
Worksheets("Tabelle3").Cells(i, 6).Value = dict(n) ' Werte in Spalte F schreiben

sinnvoller wäre es aber, den Worksheet einmal zu bestimmen:

Hab hier nur mal die wesentlichen Stellen drin:

Sub Zusammenfassen()
    ...
    Dim targetSheet As Object
   
    Set targetSheet = Worksheets("Tabelle3")
   
    ...

    For Each n In dict.keys
        targetSheet.Cells(i, 5).Value = n ' Name in Spalte E schreiben
        targetSheet.Cells(i, 6).Value = dict(n) ' Werte in Spalte F schreiben
        i = i + 1 ' Zeilenindex erhöhen
    Next n
End Sub

Statt "Tabelle3" halt der Name des Arbeitsblatts.

 

[Biertrinker]

Ich habe es anpassen können.
Konnte es dann mittels deines tollen VBA in ein neues Blatt schreiben wie ich es gebraucht habe, und dieses Blatt habe ich dann als neue Abfrage in Power Query hinterlegen können, in dem ich dann Filtern konnte, welche Zeilen ich mit bestimmten Wörtern in der Kette nicht mehr haben wollte.

Geht jetzt auch 10 Sek was vorher Stunden gedauert hat. Ich danke dir vielmals für deine tolle Hilfe!

 

[ohmsl]

Zu PowerQuery noch ein Hinweis:
https://svens-excel-welt.blogspot.com/2017/01/power-query-mehrere-werte-in-einer.html

 

[Biertrinker]

Oh Mann, einfacher gehts ja nicht und alles direkt in Query, super! Danke für den Link. Toller Blog ist das.