Transponieren von Zellen

11.06.2025 17:29:18

itk2000

Ein freundliches Hallo in die Runde,

ich stehe aktuell vor einem Problem, bei dem ich mit meinem Excelwissen nicht weiter weiß und ich hoffe hier Hilfe zu finden.
ich habe sehr große Excelisten, bei denen ich Inhalte transponieren müsste.
Der Aufbau sieht wie folgt aus:

Materialnummer Werk Materialart TextID Zeile Text
4711 DU RES BST 1 Text 1
4711 DU RES BST 2 Text 2
4711 DU RES BST 3 Text 3
4711 DU RES BST 4 Text 4
4712 DU RES BST 1 Text 1
4712 DU RES BST 2 Text 2
4713 DU RES BST 1 Text 1
4713 DU RES BST 2 Text 2
4713 DU RES BST 3 Text 3

Mein Ziel ist es nun die Texte pro Materialnummer in eine Zeile zu transponieren

4711 DU RES BST 1 Text 1 Text 2 Text 3 Text 4
4712 DU RES BST 1 Text 1 Text 2
4713 DU RES BST 1 Text 1 Text 2 Text 3

Die Zeilenanzahl pro Materialnummer ist immer unterschiedlich. Einzig der Zähler oder (besser) der Beginn einer neuen Materialnummer
wäre nutzbar als Zähler.

Meine Frage nun. Wie komme ich mit einfachen Mitteln zu meinem Ziel?

Ich danke für jeden Beitrag und Hinweis und lasse liebe Grüße da.

AW: Transponieren von Zellen

11.06.2025 17:42:27

cysu11

Hi,

kommt für Dich VBA in Frage?

LG, Alexandra

AW: Transponieren von Zellen

11.06.2025 17:48:53

SF

Hola,
das bekommst du mit Mtrans(Filter(.....) hin.
Vorher legst du dir mit Eindeutig() eine Liste der Materialnummern an.
Gruß,
steve1da

AW: Transponieren von Zellen

11.06.2025 17:49:22

Eifeljoi 5

Hallo
Als Tipp:
In deiner Excelversion ein Kinderspiel mit PQ.

AW: Transponieren von Zellen

11.06.2025 18:02:34

UweD

Hallo

so?

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E	F	G	H
1	4711	DU	RES	BST	Text 1	Text 2	Text 3	Text 4
2	4712	DU	RES	BST	Text 1	Text 2
3	4713	DU	RES	BST	Text 1	Text 2	Text 3

Zelle	Formel
A1	=EINDEUTIG(FILTER(Tabelle1!A:D;(Tabelle1!A:A>"")*(ZEILE(Tabelle1!A:A)>1)))
E1	=WENN(A1>"";ZUZEILE(FILTER(Tabelle1!F:F;Tabelle1!A:A=A1));"")

A1 ist selbsterweiternd
E1 nach unten kopieren

LG UweD

Im Anhang ist...

11.06.2025 18:28:14

Case

Moin, :-)

... eine mögliche Lösung mit Power Query: ;-)
https://www.herber.de/bbs/user/177722.xlsx

Wenn du dazu Fragen hast - frage. ;-)

Servus
Case

AW: Transponieren von Zellen

11.06.2025 18:50:31

Piet

Hallo

hier noch mal meine Beispiledatei, diesmal mit funktionierendem Makro.
Viel Spass beim testen. Bin gespannt auf das Ergebnis!
https://www.herber.de/bbs/user/177724.xlsm

mfg Piet

AW: Transponieren von Zellen

11.06.2025 19:23:11

Piet

Hallo

ich habe das Makro auf deine deine Beispieldatei angepasst. Die Formellösung sah auch gut aus!
Aber es geht ja darum, das alle Daten in einer Zeile stehen. Bin gespannt wie dein Text ausfällt.
https://www.herber.de/bbs/user/177726.xlsm

mfg Piet

AW: Transponieren von Zellen

11.06.2025 19:56:36

BoskoBiati

Hallo zusammen,

hier mal noch mit einer Formel, bei der ich leider nicht in der Lage bin, die auftretenden #NV zu eliminieren. Vielleicht hat jemand noch eine Idee, wie man diese Formel verbessern kann:

=LET(xa;EINDEUTIG(Tabelle1!A:.D);xb;TEXTVERKETTEN("@";0;MAP(INDEX(xa;;1);LAMBDA(a;TEXTVERKETTEN("#";0;FILTER(Tabelle1!F:.F;Tabelle1!A:.A=a)))));HSTAPELN(xa;TEXTTEILEN(xb;"#";"@";1)))

Über Rückmeldungen und Verbesserungen würde ich mich sehr freuen.

Gruß

Edgar

AW: Transponieren von Zellen

13.06.2025 14:46:55

daniel

#NV eleminieren geht mit WENNFEHLER nach dem Textteilen:

=LET(
xa;EINDEUTIG(Tabelle1!A:.D);
xb;TEXTVERKETTEN("@";0;MAP(INDEX(xa;;1);
LAMBDA(a;TEXTVERKETTEN("#";0;FILTER(Tabelle1!F:.F;Tabelle1!A:.A=a)))));HSTAPELN(xa;WENNFEHLER(TEXTTEILEN(xb;"#";"@";1);"")))

Gruß Daniel

AW: Transponieren von Zellen

11.06.2025 20:00:28

daniel

Hi
einfach wären noch folgendes.
hier wird die vorhandene Tabelle mit ein paar Schritten direkt umgebaut (dh die originaldaten bleiben nicht erhalten, wenn man sie nicht sichert)
das funktioniert auch mit älteren Excelversionen und eignet sich für große Datenmengen:

1. sicherstellen, dass die Liste nach Mat-Nr und Zeile sortiert ist
2. in Zelle G1 folgende Formel einfügen und runter kopieren:
=F1&Wenn(A1=A2;"|"&F2;"")
3. Spalte G kopieren und an gleicher stelle als Wert einfügen
4. mit der ganzen Tabelle die Menüfunktion DATEN - DATENTOOLS - DUPLIKATE ENFERNEN ausführen, mit Spalte A als Kriterium
5. mit der Spalte G die Menüfunktion DATEN - DATENTOOLS - TEXT IN SPALTEN ausführen, getrennt mit der Pipe "|" als Trennzeichen (oder was immer man auch in der Formel verwendet hat
6. Spalte E und F löschen.

das ganze auch als Makro:
(der Recorder hilft beim Erstellen, insbesondere für das TEXT IN SPALTEN)

Sub Makro2()

With Cells(1, 1).CurrentRegion

    .Sort Key1:=.Cells(1, 1), order1:=xlAscending, key2:=.Cells(1, 5), order2:=xlAscending, Header:=xlYes

    With .Columns(.Columns.Count + 1)

        .FormulaR1C1 = "=RC6&if(R[1]C1=RC1,""|""&R[1]C,"""")"

        .Formula = .Value

    End With

End With

With Cells(1, 1).CurrentRegion

    .RemoveDuplicates 1, xlYes

    .Columns(.Columns.Count).TextToColumns _

        Destination:=.Cells(1, .Columns.Count), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _

        Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _

        Other:=True, OtherChar:="|"

    .Columns(5).Resize(, 2).Delete shift:=xlToLeft

End With

End Sub

wenn man die Daten an anderer stelle benötigt, dann würde ich ein Makro vorschlagen, dass die Formeln verwendet:
her wird die Liste in ab H1 erstellt.
wer die Formeln behalten will, lässt das .Formula = .Value weg:

Sub Makro1()

Dim x As Long

With Range("H1")

    x = .Column

    .CurrentRegion.ClearContents

    .Formula2R1C1 = "=UNIQUE(C1.:.C4)"

    With .SpillingToRange

        With .Columns(1).Offset(0, .Columns.Count)

            .Formula2R1C1 = "=TOROW(FILTER(C6.:.C6,C1.:.C1=RC" & x & "))"

        End With

    End With

    With .CurrentRegion

        .Formula = .Value

    End With

End With

End Sub

beide Codes passen sich automatisch an unterschiedliche Tabellenlängen an, ändern sich spalten muss ggf der Code angepasst werden.

Gruß Daniel

AW: Im Anhang ist...

11.06.2025 18:31:54

itk2000

Hallo Case,

wenn ich meine Daten in diese Liste einfüge und "Enter" drücke" kopiert er sie, aber transponiert nicht

Du musst deine...

11.06.2025 18:52:25

Case

Moin, :-)

... Tabelle (eine Zelle der Daten ist markiert) mit STRG+T in eine sogenannte "intelligente Tabelle" umwandeln (Haken bei Tabelle hat Überschrift setzen - wenn nicht schon vom System erkannt). ;-)

ODER - du gehst auf "Daten - Daten abrufen und transformieren - Aus Tabelle/Bereich". ;-)

Dann kommt das gleiche Fenster. ;-)

Nun bist du im Power-Query-Editor. Dort kannst du bei "Start - Erweiterter Editor" das was da steht, mit folgendem M-Code überschreiben und OK klicken:

let

    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],

    ZAT = Table.TransformColumnTypes(Quelle, {{"Zeile", type text}}),

    SpalteU = Table.RenameColumns(Table.Pivot(ZAT, List.Distinct(ZAT[Zeile]), "Zeile", "Text"), List.Transform(List.Distinct(ZAT[Zeile]), each {_, "Text" & _}))

in

    SpalteU

Achte vorher darauf, ob deine Tabelle auch "Tabelle1" heisst, sonst anpassen. ;-)

Dann bei "Start - Schließen & Laden - Schließen & laden in..." dein Ziel auswählen.

Wenn du das nicht hinbekommst, lade eine Beispieldatei hoch, dann bau ich dir das ein., ;-)

Servus
Case

AW: Transponieren von Zellen

11.06.2025 18:14:35

itk2000

dann verliert die Zelle A1 aber ihren Wert, sprich die Materialnummer und wird "0"

AW: Transponieren von Zellen

11.06.2025 18:28:51

UweD

Verstehe ich nicht.

Wie du siehst klapp das bei mir tadellos

Deine Daten befinden sich auf Blatt Tabelle1
Das Ergebnis mit den Formeln auf Blatt Tabelle2

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F
1	Materialnummer	Werk	Materialart	TextID	Zeile	Text
2	4711	DU	RES	BST	1	Text 1
3	4711	DU	RES	BST	2	Text 2
4	4711	DU	RES	BST	3	Text 3
5	4711	DU	RES	BST	4	Text 4
6	4712	DU	RES	BST	1	Text 1
7	4712	DU	RES	BST	2	Text 2
8	4713	DU	RES	BST	1	Text 1
9	4713	DU	RES	BST	2	Text 2
10	4713	DU	RES	BST	3	Text 3

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E	F	G	H
1	Materialnummer	Werk	Materialart	TextID	Text
2	4711	DU	RES	BST	Text 1	Text 2	Text 3	Text 4
3	4712	DU	RES	BST	Text 1	Text 2
4	4713	DU	RES	BST	Text 1	Text 2	Text 3

Zelle	Formel
A1	=EINDEUTIG(FILTER(Tabelle1!A:D;Tabelle1!A:A>""))
E1	=ZUZEILE(FILTER(Tabelle1!F:F;Tabelle1!A:A=A1))

AW: Transponieren von Zellen

11.06.2025 18:38:34

itk2000

@UweD

jetzt transponiert er bei mir auch, allerdings erhalte ich die gleiche Anzahl Zeilen der Materialnummern, aber...hinten dran die Texte :-)

Zellbezug ganze Spalte, aber trotzdem nur Daten.

11.06.2025 20:27:27

daniel

HI Uwe
kleiner Tipp am Rande:
mittlerweile kann man Leerzellen am Anfang und Ende auch direkt über den Zellbezug ausschließen (Punkt vor bzw nach dem Spaltenbuchstaben)
ein: =A.:.D liefert nur die Zellen mit Daten aus diesem Bereich, Leerzellen am Anfang und Ende werden so automatisch ausgeschlossen (Leerzellen zwischendrin bleiben erhalten)
das erspart den Filter: =Filter(A:D;A:A>"") und reduziert vermutlich so den Rechenaufwand.
kleine, aber feine Verbesserung in Excel.
Gruß Daniel