Hallo.
Ich habe z.B. 2 Spalten in denen jeweils in die ersten 10 Zeilen Werte eingetragen sind. Ich möchte jetzt eine dritte Spalte, in die Werte der ersten Spalte stehen, gefolgt von einer Leerzeile und anschließend die Werte der zweiten Spalte darunter.
Eigentliches Ziel ist, Bereiche von n Spalten untereinander in eine Spalte zu schreiben, jeweils getrennt durch eine leere Zelle.
Vielen Dank im Voraus
Hallo
du kannst entweder die Funktion =VERKETTEN() wählen oder die Zellen mit dem kaufmännischen & zusammenfügen
Beispiel
=verketten(a1;" „;A2)
=A1&“ "&A2
Gruß Holger
Eigentliches Ziel ist, Bereiche von n Spalten untereinander in
eine Spalte zu schreiben, jeweils getrennt durch eine leere
Zelle.
Hallo Manfred,
soll das bei n = 4 so aussehen:?
Tabellenblatt: [Mappe1]!Tabelle1
│ A │ B │ C │ D │ E │
───┼────┼────┼────┼────┼────┤
1 │ A1 │ B1 │ C1 │ D1 │ A1 │
───┼────┼────┼────┼────┼────┤
2 │ A2 │ B2 │ C2 │ D2 │ B1 │
───┼────┼────┼────┼────┼────┤
3 │ A3 │ B3 │ C3 │ D3 │ C1 │
───┼────┼────┼────┼────┼────┤
4 │ A4 │ B4 │ C4 │ D4 │ D1 │
───┼────┼────┼────┼────┼────┤
5 │ A5 │ B5 │ C5 │ D5 │ │
───┼────┼────┼────┼────┼────┤
6 │ │ │ │ │ A2 │
───┼────┼────┼────┼────┼────┤
7 │ │ │ │ │ B2 │
───┼────┼────┼────┼────┼────┤
8 │ │ │ │ │ C2 │
───┼────┼────┼────┼────┼────┤
9 │ │ │ │ │ D2 │
───┼────┼────┼────┼────┼────┤
10 │ │ │ │ │ │
───┼────┼────┼────┼────┼────┤
11 │ │ │ │ │ A3 │
───┼────┼────┼────┼────┼────┤
12 │ │ │ │ │ B3 │
───┼────┼────┼────┼────┼────┤
13 │ │ │ │ │ C3 │
───┼────┼────┼────┼────┼────┤
14 │ │ │ │ │ D3 │
───┼────┼────┼────┼────┼────┤
15 │ │ │ │ │ │
───┼────┼────┼────┼────┼────┤
16 │ │ │ │ │ A4 │
───┼────┼────┼────┼────┼────┤
17 │ │ │ │ │ B4 │
───┼────┼────┼────┼────┼────┤
18 │ │ │ │ │ C4 │
───┼────┼────┼────┼────┼────┤
19 │ │ │ │ │ D4 │
───┴────┴────┴────┴────┴────┘
A1:E19
haben das Zahlenformat: Standard
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Gruß
Reinhard
Hallo Reinhard. Vielen Dank für die Antwort.
Nein. Eigentlich so.
Tabellenblatt: [Mappe1]!Tabelle1
> │ A │ B │ C │ D │ E │
> ───┼────┼────┼────┼────┼────┤
> 1 │ A1 │ B1 │ C1 │ D1 │ A1 │
> ───┼────┼────┼────┼────┼────┤
> 2 │ A2 │ B2 │ C2 │ D2 │ A2 │
> ───┼────┼────┼────┼────┼────┤
> 3 │ A3 │ B3 │ C3 │ D3 │ A3 │
> ───┼────┼────┼────┼────┼────┤
> 4 │ A4 │ B4 │ C4 │ D4 │ A4 │
> ───┼────┼────┼────┼────┼────┤
> 5 │ A5 │ B5 │ C5 │ D5 │ │
> ───┼────┼────┼────┼────┼────┤
> 6 │ │ │ │ │ B1 │
> ───┼────┼────┼────┼────┼────┤
> 7 │ │ │ │ │ B2 │
> ───┼────┼────┼────┼────┼────┤
> 8 │ │ │ │ │ B3 │
> ───┼────┼────┼────┼────┼────┤
> 9 │ │ │ │ │ B4 │
> ───┼────┼────┼────┼────┼────┤
> 10 │ │ │ │ │ │
> ───┼────┼────┼────┼────┼────┤
> 11 │ │ │ │ │ C1 │
> ───┼────┼────┼────┼────┼────┤
> 12 │ │ │ │ │ C2 │
> ───┼────┼────┼────┼────┼────┤
> 13 │ │ │ │ │ C3 │
> ───┼────┼────┼────┼────┼────┤
> 14 │ │ │ │ │ C4 │
> ───┼────┼────┼────┼────┼────┤
> 15 │ │ │ │ │ │
> ───┼────┼────┼────┼────┼────┤
> 16 │ │ │ │ │ D1 │
> ───┼────┼────┼────┼────┼────┤
> 17 │ │ │ │ │ D2 │
> ───┼────┼────┼────┼────┼────┤
> 18 │ │ │ │ │ D3 │
> ───┼────┼────┼────┼────┼────┤
> 19 │ │ │ │ │ D4 │
> ───┴────┴────┴────┴────┴────┘
> A1:E19
> haben das Zahlenformat: Standard
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Ich vermute, das wird recht ch sei, aber ich krieg´s nicht hin.
Danke und Gruß
Hallo Manfred,
Tabellenblatt: [Mappe1]!Tabelle1
│ A │ B │ C │ D │ E │
───┼─────┼─────┼─────┼─────┼─────┤
1 │ ZA1 │ ZB1 │ ZC1 │ ZD1 │ ZA1 │
───┼─────┼─────┼─────┼─────┼─────┤
2 │ ZA2 │ ZB2 │ ZC2 │ ZD2 │ ZA2 │
───┼─────┼─────┼─────┼─────┼─────┤
3 │ ZA3 │ ZB3 │ ZC3 │ ZD3 │ ZA3 │
───┼─────┼─────┼─────┼─────┼─────┤
4 │ ZA4 │ ZB4 │ ZC4 │ ZD4 │ ZA4 │
───┼─────┼─────┼─────┼─────┼─────┤
5 │ ZA5 │ ZB5 │ ZC5 │ ZD5 │ ZA5 │
───┼─────┼─────┼─────┼─────┼─────┤
6 │ │ │ │ │ │
───┼─────┼─────┼─────┼─────┼─────┤
7 │ │ │ │ │ ZB1 │
───┼─────┼─────┼─────┼─────┼─────┤
8 │ │ │ │ │ ZB2 │
───┼─────┼─────┼─────┼─────┼─────┤
9 │ │ │ │ │ ZB3 │
───┼─────┼─────┼─────┼─────┼─────┤
10 │ │ │ │ │ ZB4 │
───┼─────┼─────┼─────┼─────┼─────┤
11 │ │ │ │ │ ZB5 │
───┼─────┼─────┼─────┼─────┼─────┤
12 │ │ │ │ │ │
───┼─────┼─────┼─────┼─────┼─────┤
13 │ │ │ │ │ ZC1 │
───┼─────┼─────┼─────┼─────┼─────┤
14 │ │ │ │ │ ZC2 │
───┼─────┼─────┼─────┼─────┼─────┤
15 │ │ │ │ │ ZC3 │
───┼─────┼─────┼─────┼─────┼─────┤
16 │ │ │ │ │ ZC4 │
───┼─────┼─────┼─────┼─────┼─────┤
17 │ │ │ │ │ ZC5 │
───┼─────┼─────┼─────┼─────┼─────┤
18 │ │ │ │ │ │
───┴─────┴─────┴─────┴─────┴─────┘
Benutzte Formeln:
E1 : =WENN(REST(ZEILE();6)=0;"";INDIREKT(ZEICHEN(64+(ZEILE()-1)/6+1)&REST(ZEILE();6)))
E2 : =WENN(REST(ZEILE();6)=0;"";INDIREKT(ZEICHEN(64+(ZEILE()-1)/6+1)&REST(ZEILE();6)))
E3 : =WENN(REST(ZEILE();6)=0;"";INDIREKT(ZEICHEN(64+(ZEILE()-1)/6+1)&REST(ZEILE();6)))
usw. in E
A1:E18
haben das Zahlenformat: Standard
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Gruß
Reinhard
kürzere Formeln
E1 :
=WENN(REST(ZEILE();6)=0;"";INDIREKT(ZEICHEN(64+(ZEILE()-1)/6+1)
&REST(ZEILE();6)))
E2 :
=WENN(REST(ZEILE();6)=0;"";INDIREKT(ZEICHEN(64+(ZEILE()-1)/6+1)
&REST(ZEILE();6)))
E3 :
=WENN(REST(ZEILE();6)=0;"";INDIREKT(ZEICHEN(64+(ZEILE()-1)/6+1)
&REST(ZEILE();6)))
usw. in E
Hallo Manfred,
kannst die Formeln in E oder F nehmen. Bei E kannst du die Nullwerte durch Optionen ausblenden lassen.
Tabellenblatt: [Mappe1]!Tabelle1
│ A │ B │ C │ D │ E │ F │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
1 │ ZA1 │ ZB1 │ ZC1 │ ZD1 │ ZA1 │ ZA1 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
2 │ ZA2 │ ZB2 │ ZC2 │ ZD2 │ ZA2 │ ZA2 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
3 │ ZA3 │ ZB3 │ ZC3 │ ZD3 │ ZA3 │ ZA3 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
4 │ ZA4 │ ZB4 │ ZC4 │ ZD4 │ ZA4 │ ZA4 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
5 │ ZA5 │ ZB5 │ ZC5 │ ZD5 │ ZA5 │ ZA5 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
6 │ │ │ │ │ 0 │ │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
7 │ │ │ │ │ ZB1 │ ZB1 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
8 │ │ │ │ │ ZB2 │ ZB2 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
9 │ │ │ │ │ ZB3 │ ZB3 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
10 │ │ │ │ │ ZB4 │ ZB4 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
11 │ │ │ │ │ ZB5 │ ZB5 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
12 │ │ │ │ │ 0 │ │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
13 │ │ │ │ │ ZC1 │ ZC1 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
14 │ │ │ │ │ ZC2 │ ZC2 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
15 │ │ │ │ │ ZC3 │ ZC3 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
16 │ │ │ │ │ ZC4 │ ZC4 │
───┼─────┼─────┼─────┼─────┼─────┼─────┤
17 │ │ │ │ │ ZC5 │ ZC5 │
───┴─────┴─────┴─────┴─────┴─────┴─────┘
Benutzte Formeln:
E1 : =INDEX(A:smiley:;REST(ZEILE()-1;6)+1;(ZEILE()-1)/6+1)
E2 : =INDEX(A:smiley:;REST(ZEILE()-1;6)+1;(ZEILE()-1)/6+1)
E3 : =INDEX(A:smiley:;REST(ZEILE()-1;6)+1;(ZEILE()-1)/6+1)
usw. in E
F1 : =WENN(REST(ZEILE();6)=0;"";INDEX(A:smiley:;REST(ZEILE()-1;6)+1;(ZEILE()-1)/6+1))
F2 : =WENN(REST(ZEILE();6)=0;"";INDEX(A:smiley:;REST(ZEILE()-1;6)+1;(ZEILE()-1)/6+1))
F3 : =WENN(REST(ZEILE();6)=0;"";INDEX(A:smiley:;REST(ZEILE()-1;6)+1;(ZEILE()-1)/6+1))
usw. in F
A1:F17
haben das Zahlenformat: Standard
Tabellendarstellung erreicht mit dem Code in FAQ:2363
Gruß
Reinhard
Hallo Reinhard.
Vielen Dank. Das ist genau das, was ich wollte.
Falls Du noch Lust hast, kannst Du mir vielleicht zeigen, wie ich das Ganze in ein Makro packe? 
Ansonsten kann ich damit erst einmal super arbeiten.
Danke und beste Grüße
kannst Du mir vielleicht zeigen, wie
ich das Ganze in ein Makro packe?
Hallo Manfred,
wie meinst du das genau?
Soll das Makro automatisch „n“ (hier n = 4) erkennen und dann Spalte
n +1 (also hier E) mit den Formeln befüllen?
Oder anstelle der Formeln gleich den entsprechenden Wert in E eintragen?
Gruß
Reinhard
Hallo Reinhard.
Das erste. Automatisch erkennen und in n+1 einfügen.
Danke
Manfred
Das erste. Automatisch erkennen und in n+1 einfügen.
Hallo Manfred,
Option Explicit
Sub Liste()
Dim N As Integer, Zei As Long, Formel As String
With Worksheets("Tabelle1")
N = .Cells(1, Columns.Count).End(xlToLeft).Column
Zei = .Cells(Rows.Count, 1).End(xlUp).Row
Formel = "=IF(MOD(ROW()," & Zei + 1 & ")=0,"""",INDEX(A:" & Chr(64 + N) \_
& ",MOD(ROW()-1," & Zei + 1 & ")+1,(ROW()-1)/" & Zei + 1 & "+ 1))"
Zei = Zei \* N + N
.Range(.Cells(1, N + 1), .Cells(Zei - 1, N + 1)).Formula = Formel
End With
End Sub
Gruß
Reinhard
Hallo Reinhard.
Vielen Dank für die Mühe.
Das mit dem Makro funktioniert prinzipiell. Ein kleiner Fehler muss aber noch drin sein. Wenn ich es ausführe wird am Ende meiner Tabelle ein Spalte angefügt in der alle vorherigen Spalten untereinander zusammengeführt sind (wie gewünscht). Jedoch steht statt des Wertes jetzt #NAME? in den einzelnen Zellen. Die Formel innerhalb jeder Zelle sieht dann wie folgt aus:
=WENN(REST(ZEILE();8)=0;"";INDEX(A:¸;REST(ZEILE()-1;8)+1;(ZEILE()-1)/8+ 1))
Irgendwie wird die range der Matrix nicht korrekt erkannt. Wenn ich statt „¸“ (Bei"INDEX(A:¸;…) dann die „richtige“ Spalte (also z.B. A:E;…) zuordne wird alles richtig eingetragen.
Ansonsten ist das genau das, was ich haben wollte. Perfekt.
Danke
Manfred
=WENN(REST(ZEILE();8)=0;"";INDEX(A:¸;REST(ZEILE()-1;8)+1;(ZEILE
()-1)/8+ 1))
Irgendwie wird die range der Matrix nicht korrekt erkannt.
Wenn ich statt „¸“ (Bei"INDEX(A:¸;…) dann die „richtige“
Spalte (also z.B. A:E;…) zuordne wird alles richtig
eingetragen.
Hallo Manfred,
ich kann nicht nachvollziehen warum da ein Punkt ( Zeichen(46) )
erscheint. Im Code steht an der Stelle des Punktes:
INDEX(A:" & Chr(64 + N)
Das entspricht im Deutschen:
INDEX(A:" & Zeichen(64 + N)
N müßte dann minus 18 sein damit aus 64 + N 46 wird.
In dieser Mappe ist der gezeigte Code und da funktioniert es:
http://www.uploadagent.de/show-190608-1361259234.html
Gruß
Reinhard
Hallo Reinhard.
Hmm, komisch. Ich habe mal meine Werte in deine Mappe kopiert, das Makro ausgeführt und es tritt dann da auch auf. Dann habe ich mal je 10 Zufallszahlen in Spalten A:EE eingefügt und das Problem tritt ebenfalls auf. Ich vermute dass die Spaltenanzahl zu groß ist.(?)
Ich habe sehr viele Spalten (~100) und es sind nicht immer gleich viel Zeilen pro Spalte. Kann es vielleicht daran liegen?
Die angehängte Spalte enthält folgenden Code
=WENN(REST(ZEILE();11)=0;"";INDEX(A :º; REST(ZEILE()-1;11)+1;(ZEILE()-1)/11+ 1))
Nebenbei: Das „Zeichen“ ist nicht immer das gleiche, sondern hängt von der Spaltenanzahl ab, z.B. manchmal auch
=WENN(REST(ZEILE();11)=0;"";INDEX(A :Ç; REST(ZEILE()-1;11)+1;(ZEILE()-1)/11+ 1))
Bei einer gewissen Anzahl an Spalten tritt ein Fehler auf (z.B. A:BT => Laufzeitfehler 1004 und beim Debuggen wird mir im Code die Zeile
„.Range(.Cells(1, N + 1), .Cells(Zei - 1, N + 1)).Formula = Formel“ angezeigt.
Die Anzahl der Zeilen pro Spalte wird automatisch richtig erkannt. Dass die einzelnen Spalten unterschiedliche Anzahl an Zeilen haben ist demnach kein Problem.
Macht aber nichs. Es klappt ja prinzipiell. Ich führe das Makro aus (Spalte mit „#NAME?“ wird angehängt mit Formel von oben), füge dann die richtige range ein und mache Autoausfüllen und dann passt das. Ist ja kein Aufwand. Das spart mir trotzdem schon ne Menge Arbeit und Zeit.
Falls Dir was einfällt kannst Du mir ja nochmal schreiben. Ansonsten Vielen Dank und beste Grüße
Manfred
Ich vermute dass die
Spaltenanzahl zu groß ist.(?)
Hallo Manfred,
ja, daran liegt das, ist im nachfolgenden Code beseitigt.
Der Code prüft nur die Zeilenanzahl in der „längsten“ Spalte.
Unterschiedliche Zeilenanzahl in anderen Spalten wird nicht beachtet.
Soll das noch mit rein? Kann möglicherweise schwierig werden.
Einfacher ist es dann die Formeln ganz wegzulassen.
Gruß
Reinhard
Sub Liste()
Dim N As Integer, Zei As Long, Formel As String, Spa As Long
With Worksheets("Tabelle1")
N = .Cells(1, Columns.Count).End(xlToLeft).Column
For Spa = 1 To N
Zei = Application.Max(Zei, .Cells(Rows.Count, Spa).End(xlUp).Row)
Next Spa
Formel = "=IF(MOD(ROW()," & Zei + 1 & ")=0,"""",INDEX(A:" & \_
Split(.Cells(1, N).Address, "$")(1) & ",MOD(ROW()-1," & \_
Zei + 1 & ")+1,(ROW()-1)/" & Zei + 1 & "+ 1))"
Zei = Zei \* N + N
.Range(.Cells(1, N + 1), .Cells(Zei - 1, N + 1)).Formula = Formel
End With
End Sub
Hallo Reinhard.
Vielen Dank. Ist Ok so. Damit kann ich prima arbeiten.
Beste Grüße
Manfred