Office: Daten ordnen

Hallo, möchte gerne die "Textzellen" aus Spalte A:

SpalteA
TextA1
1 Leerzeile
TextB1
13 Leerzeilen
TextA2
1 Leerzeile
TextB2
13 Leerzeilen
...
TextAn
1 Leerzeile
TextBn

so in zwei Spalten gruppiert ordnen:
SpalteA|SpalteB
TextA1|TextB1
TextA2|TextB2
...
TextAn|TextBn

Gibt es dafür eine Formel? Danke!
VG, Mat

 

Hallo,

wenn die Texte wirklich abwechselnd kommen, dann wäre das eine Möglichkeit:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	TextA1		TextA1	TextB1
2			TextA2	TextB2
3
4	TextB1
5
6
7
8	TextA2
9
10	TextB2

Zelle	Formel
C1	{=INDEX($A:$A;KKLEINSTE(WENN($A$1:$A$1000<>"";ZEILE($A$1:$A$1000));(ZEILE(A1)-1)*2+1))}
D1	{=INDEX($A:$A;KKLEINSTE(WENN($A$1:$A$1000<>"";ZEILE($A$1:$A$1000));ZEILE(B1)*2))}
C2	{=INDEX($A:$A;KKLEINSTE(WENN($A$1:$A$1000<>"";ZEILE($A$1:$A$1000));(ZEILE(A2)-1)*2+1))}
D2	{=INDEX($A:$A;KKLEINSTE(WENN($A$1:$A$1000<>"";ZEILE($A$1:$A$1000));ZEILE(B2)*2))}

<table><tr><td>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span>Strg+Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table>[/parsehtml]<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]

Das Ganze ohne Matrixformel:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	TextA1		TextA1	TextB1
2			TextA2	TextB2
3			TextA3	TextB3
4	TextB1
5
6
7
8	TextA2
9
10	TextB2
11
12
13
14
15
16	TextA3
17
18
19
20
21
22
23	TextB3

Zelle	Formel
C1	=INDEX(A:A;VERGLEICH("*";A:A;0))
D1	=INDEX(A:A;VERGLEICH("*";INDEX(A:A;VERGLEICH(C1;A:A;0)+1):$A$65535;0)+VERGLEICH(C1;A:A;0))
C2	=INDEX(A:A;VERGLEICH("*";INDEX(A:A;VERGLEICH(D1;A:A;0)+1):$A$65535;0)+VERGLEICH(D1;A:A;0))
D2	=INDEX(A:A;VERGLEICH("*";INDEX(A:A;VERGLEICH(C2;A:A;0)+1):$A$65535;0)+VERGLEICH(C2;A:A;0))
C3	=INDEX(A:A;VERGLEICH("*";INDEX(A:A;VERGLEICH(D2;A:A;0)+1):$A$65535;0)+VERGLEICH(D2;A:A;0))
D3	=INDEX(A:A;VERGLEICH("*";INDEX(A:A;VERGLEICH(C3;A:A;0)+1):$A$65535;0)+VERGLEICH(C3;A:A;0))

<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]


Gruß

Edgar

 

Hallo Mat,

wenn die Zeilenabstände wirklich immer genau so sind wie Du geschrieben hast, würde auch diese Makrolösung gehen:

Code:

Option Explicit

Dim loCounter As Long
Dim loLetzte As Long


Public Sub ZweiSpaltenText()
    
    With ThisWorkbook.Worksheets("Tabelle1")
        
        ' letzte belegte Zelle in Spalte A
        loLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
        
        For loCounter = 1 To loLetzte
            .Cells(loCounter + 2, 1).Copy .Cells(loCounter, 2)
            .Range(.Rows(loCounter + 1), Rows(loCounter + 15)).Delete shift:=xlUp
            loLetzte = loLetzte - 15
        Next loCounter
        
    End With
    
End Sub

in der Beispieldatei wirst Du im Modul1 zwei Subs finden.
Mit dem ersten habe ich Deinen Text nachgebaut, das zweite verteilt Ihn dann auf die Spalten A und B.


Ich hoffe geholfen zu haben.

 

Hallo,

wenn das wirklich System hat, dann genügen zwei kleine Formeln:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	TextA1		TextA1	TextB1
2			TextA2	TextB2
3	TextB1		TextA3	TextB3
4
5
6
7
8
9
10
11
12
13
14
15
16
17	TextA2
18
19	TextB2
20
21
22
23
24
25
26
27
28
29
30
31
32
33	TextA3
34
35	TextB3

Zelle	Formel
C1	=INDEX($A:$A;(ZEILE($A1)-1)*16+1)
D1	=INDEX($A:$A;(ZEILE($A1)-1)*16+3)

<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]


@achim,

Dein Code in allen Ehren, aber man sollte in einer laufende Schleife den Schleifenzähler nicht manipulieren:

Code:

loLetzte = loLetzte - 15 

Hier noch ein Code, hinter die Tabelle:

Code:

Option Explicit

Public Sub ZweiSpaltenText()
    Dim loCounter As Long
    Dim loLetzte As Long
    Dim loA As Long
    loA = 1
    Application.ScreenUpdating = False
       
        ' letzte belegte Zelle in Spalte A
        loLetzte = IIf(IsEmpty(Cells(Rows.Count, 1)), Cells(Rows.Count, 1).End(xlUp).Row, Rows.Count)
       
        For loCounter = 1 To loLetzte Step 16
            Cells(loA, 1) = Cells(loCounter, 1)
            Cells(loA, 2) = Cells(loCounter + 2, 1)
            loA = loA + 1
        Next
       Range("A" & loA & ":A" & loLetzte).Clear
   Application.ScreenUpdating = True
End Sub

Gruß

Edgar

 

Hallo Edgar,

"Step" habe ich schon mal benutzt, aber inzwischen wieder vergessen.
Guter Tipp. Danke dafür.


Warum sollte man den Schleifenzähler in einer laufenden Schleife nicht manipulieren?
Habe ich schon öfter so gemacht und noch nie Probleme gehabt.

 

Hallo,

jeder Programmierer haut Dir den Code um die Ohren, weil die Manipulation des Schleifenzählers ganz böse in die Hose gehn kann, wenn Du z.B. durch die Subtraktion niedriger kommst als der aktuelle Wert, hier, wenn loletzte niedriger wird als loCounter. Dann läuft sich ggf. Deine Schleife tot. Bei do Loop ist das sehr gefährlich. Das kannst Du ja mal selbst ausprobieren.

Gruß

Edgar

 

Hallo Edgar,

danke für die kleine Lehrstunde.

Da hast Du natürlich völlig recht.
Am besten verzichten.
Oder, wenn das nicht geht (oder man nicht auf eine Alternative kommt) zumindest immer ein Fangnetz einbauen.
Z.Bsp. eine Sicherheitsabfrage à la:

Code:

If (loLetzte - 15) <= loCounter then Exit For

Auch wenn jetzt vermutlich immernoch so einige die Augen verdrehen werden...
;O)

 

Hallo,

wenn man das richtig strukturiert, sollte es immer ohne gehen! hier in diesem Fall ist es gar nicht notwendig, Zeilen zu löcchen, es reicht doch die Inhalte zu löschen, so wie ich es gemacht habe.

Gruß

Edgar

 

Hallo Kallewirsch und fette Elfe, danke hilft mir weiter, aber...

1.) Was macht die Formel:
=INDEX($A:$A;(ZEILE($A1)-1)*16+1) ?

2.) Wie sage ich, die erste Textzeile für C1 beginnt erst bei A30 und die für D1 ab A32?

3.) Ich habe ein noch größeres Problem, nämlich das auch mal unwichtiger Text den Zeilenrhytmus durcheinander bringt:
SpalteA
TextA1
1 Leerzeile
TextB1
13 Leerzeilen
TextA2
unwichtiger Text
1 Leerzeile
TextB2
13 Leerzeilen
TextA3
1 Leerzeile
TextB3
...
TextAn
1 Leerzeile
TextBn

Das kann man wahrscheinlich nur durch vorheriges manuelles Rauslöschen dieser Zeile und nicht durch eine Formel lösen, oder?
:roll:

Danke für alles.

 

Hallo,

mit der Formelauswertung kann man sehen, was eine Formel macht!

Code:

=INDEX($A:$A;(ZEILE($A1)-1)*16+30)
=INDEX($A:$A;(ZEILE($A1)-1)*16+32)

es werden nur die Zeilen 30 u. 32 sowie alle Zellen im Abstand 16 dazu übertragen, dazwischen stehender Text wird ignoriert.


Gruß

Edgar

 

Danke Edgar, gibt es für mein 3. Problem eine Lösung? Vielleicht kann man das irgendwie über eine WENN-Funktion abfangen? Wäre toll wenn das geht.

VG, Mat

 

Hallo,

funktioniert, wenn nur nach TextA in der nächsten Zeile ein Eintrag und keine doppelten Texte in A vorkommen!!!!:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C
1		Text A1	TextB1
2		Text A2	TextB2
3		Text A3	TextB3
4		Text A4	TextB4
5		Text A5	TextB5
6		Text A6	TextB6
30	Text A1
31
32	TextB1
46	Text A2
47	Mist
48
49	TextB2
63	Text A3
64
65	TextB3
79	Text A4
80	Mist
81
82	TextB4
96	Text A5
97	Mist
98
99	TextB5
113	Text A6
114
115	TextB6
129	Text A7
130
131	TextB7
145	Text A8
146
147	TextB8

Zelle	Formel
B1	=INDEX($A:$A;(ZEILE($A1)-1)*16+30)
C1	=INDEX($A:$A;VERGLEICH(B1;A:A;0)+2+(INDEX($A:$A;VERGLEICH(B1;A:A;0)+1)<>""))
B2	=INDEX($A:$A;(ZEILE($A2)-1)*16+30+ANZAHL2($A$1:INDEX($A:$A;(ZEILE($A1))*16+29))-ANZAHL2($B$1:C1))
C2	=INDEX($A:$A;VERGLEICH(B2;A:A;0)+2+(INDEX($A:$A;VERGLEICH(B2;A:A;0)+1)<>""))

Gruß

Edgar