Waagerechte Zelleninhalt Senkrecht ausgeben mit Bezug

[koreandi]

Hallo Liebes Forum.
Ich habe mich hier angemeldet, da ich ein auf Tipps und Tricks aus dem Forum hoffe.
Bin schon länger und öfter hier als suchender unterwegs, aber nun brauche ich Hilfe.
Ich stehe vor einem Excel-Problem, zu dem ich eine Lösung benötige.

Ich habe eine Datenbank mit ca. 80.000 Zeilen.
Jede Zeile kann bis zu 20 Spalten mit Informationen haben.
Nun sollte ich die Spalteninhalte untereinander anordnen und dabei den Bezug zu einer Zelle bestehen lassen.

Ich probier das mal zu erklären:

Ausgangszustand:
A1  |  B1  |  C1  |  D1  |  E1  |  F1  |  ...
1    |  12  |  23  |  24   |  55  |  99  |  ...
2    |   77 |  99  | ...
...

Sollzustand:
A1  |  B1  |  ...
1    |  12
1    |   23
1    |   24
1    |   55
1    |   99
2    |   77
2    |   99
...

Ich denke ohne VBA / Makro kommt man da nicht weit. Und da kenne ich mich leider nicht wirklich aus.
Es kann sein, dass die Felder ab D3 leer sind. Wenn leere Felder nicht übertragen werden, wäre das super. Ansonsten mache ich das nachträglich.
Kann hier jemand einen Ratschlag geben?
Ich weiß, dass Excel nur 1 mio und ein paar zerquetschte Zeilen verarbeiten kann..
Ich müsste in dem Fall das Ganze ein wenig Stückeln.

Vielen Dank bereits an alle, die mir hier helfen.

Gruß
Andi

[ruppy]

Versuche mal die Funktion MTRANS().

Wenn du neuestes Excel hast, reicht sie wie sie daherkommt.

Ansonsten als Matrix-Formel eingeben (statt mit "Enter" mit "Strg+Shift+Enter" bestätigen).

Einfach den Bereich den du transponieren willst (das ist das was du mit dem Thread-Namen beschreibst) in der Formel verwenden (ist nur dieses eine Argument).

Wenn es mit den Zeilen eng wird, kannst du das auch in Power Query machen.

BG ruppy

[MisterBurns]

Erläutere doch bitte mal, wieso du das überhaupt machen willst. Denn du hast ja bestehend eine Tabelle, die entsprechend den Regeln der Datenverarbeitung aufgebaut ist. Nun willst du sie aber "kaputtmachen". Wieso?
Außerdem bedenke, dass du ja nicht immer 20 Zeilen pro Datensatz bekommen wirst. Ergo ist es auch nicht mehr eindeutig, was die Werte aussagen. Meines Erachtens müsste nicht nur die Datensatznummer, sondern auch die Spaltenüberschrift mit übernommen werden.

[marose67]

Hallo ruppy,

MTrans hilft da wohl nicht. Die Daten sollen untreinander stehen ... ansonsten hätte ich schon Transformieren vorgeschlagen.

Gruß
Marcus

[ruppy]

Hi marose67,

ok jetzt sehe ich es auch und besonders verstehe ich jetzt auch MisterBurns.
Finde auch das ist sinnfrei.

Habe es nur überflogen und dachte ok da muss transponieren gesucht sein.

Sorry für meinen Schnellschuss.

BG ruppy

[atilla]

Hallo,

unten zwei Prozeduren, einmal mit Prüfung auf Leerzellen und einmal ohne.

Teste mal. Du kannst für die Variable lngLetzte einen festen Zeilenwert eintragen und mal schauen ob es das macht, was Du möchtest.
Schau Dir die Kommentare im Code an und passe an den entsprechenden Stellen die Tabellennamen an.

Ich habe mit 5000 Zeilen und 20 Spalten getestet und es war recht flott.
Vielleicht schaut ja snb noch vorbei und stellt etwas schnelleres  ein.

Code:

Sub Zeile_tranponieren() 'ohne Prüfung auf leer
   Dim i As Long, j As Long, k As Long
   Dim lngLetzte As Long, lngSpalten As Long, lngGesamt As Long
   Dim arr, outArr
  
   lngSpalten = 20   'eingelen werden 20 Spalten
   With Worksheets("Tabelle1")   'Datentabelle aus der eingelesen wird
      lngLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
      arr = .Range(.Cells(1, 1), .Cells(lngLetzte, lngSpalten))
      lngGesamt = lngLetzte * lngSpalten
   End With
  
   With Worksheets("Tabelle4")   'Ausgabe Tabelle
      .Cells.Clear      'alle Zellen werden geleert
      outArr = .Range(.Cells(1, 1), .Cells(lngGesamt, 2)) 'angenommener Schreibbereich leer einlesen
   End With
  
   k = 1
   For i = 1 To lngLetzte
      For j = 1 To lngSpalten
         outArr(k, 1) = i
         outArr(k, 2) = arr(i, j)
         k = k + 1
      Next j
   Next i
  
   'Ausgabe
   With Worksheets("Tabelle4")
       .Range(.Cells(1, 1), .Cells(lngGesamt, 2)) = outArr
   End With
End Sub


Sub Zeile_tranponieren2() 'Mit Prüfung auf leer
   Dim i As Long, j As Long, k As Long
   Dim lngLetzte As Long, lngSpalten As Long, lngGesamt As Long
   Dim arr, outArr
   lngSpalten = 20   'eingelen werden 20 Spalten
   With Worksheets("Tabelle1")   'Datentabelle aus der eingelesen wird
      lngLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
      arr = .Range(.Cells(1, 1), .Cells(lngLetzte, lngSpalten))
      lngGesamt = lngLetzte * lngSpalten
   End With
  
   With Worksheets("Tabelle4")   'Ausgabe Tabelle
      .Cells.Clear      'alle Zellen werden geleert
      outArr = .Range(.Cells(1, 1), .Cells(lngGesamt, 2)) 'angenommener Schreibbereich leer einlesen
   End With
  
   k = 1
   For i = 1 To lngLetzte
      For j = 1 To lngSpalten
         If arr(i, j) <> "" Then
            outArr(k, 1) = i
            outArr(k, 2) = arr(i, j)
            k = k + 1
         End If
      Next j
   Next i
  
   'Ausgabe
   With Worksheets("Tabelle4")
       .Range(.Cells(1, 1), .Cells(lngGesamt, 2)) = outArr
   End With
End Sub

[RPP63]

Moin!
Wenn ich das richtig sehe, handelt es sich um ein simples Entpivotieren einer Kreuztabelle.
Das ist mit PowerQuery mit wenigen Mausklicks möglich.

Ausgangssituation:

	A	B	C	D	E
1	Gruppe	Wert1	Wert2	Wert3	Wert4
2	1	7	9
3	2	4	5	2
4	3	5

Ergibt:

	A	B	C
1	Gruppe	Attribut	Wert
2	1	Wert1	7
3	1	Wert2	9
4	2	Wert1	4
5	2	Wert2	5
6	2	Wert3	2
7	3	Wert1	5

Spalte B kann jetzt ausgeblendet werden (oder bereits im PQ-Editor gelöscht werden)
Ich würde sie aber beibehalten, hilft sie doch bei weiteren Auswertungen.

Gruß Ralf

---

Erläutere doch bitte mal, wieso du das überhaupt machen willst. Denn du hast ja bestehend eine Tabelle, die entsprechend den Regeln der Datenverarbeitung aufgebaut ist. Nun willst du sie aber "kaputtmachen". Wieso?

Moin Berni!
Die vorliegende Kreuztabelle ist ja bereits ein "Endprodukt", welches weitere Auswertungen erschwert bzw. sogar unmöglich macht.
Die entpivotierte Fassung erlaubt hingegen weiterführende Auswertungen, ist also imo ausgesprochen sinnvoll.

Gruß Ralf

[LCohen]

G1: =INDEX(A:E;ZEILE(A5)/5;REST(ZEILE(A5);5)+1) runterziehen, plattmachen, A:F löschen (bei fix 5 Spalten)
____________________________________________
Bei bis zu 20 Spalten:

V1: =INDEX(A:T;ZEILE(A20)/20;REST(ZEILE(A20);20)+1) 
W1: =ISTLEER(V1)
X1: =KÜRZEN(ZEILE(A20)/20)

alle 3 runterziehen, plattmachen, A:U löschen. Nun nach Spalte B (ehem. W) sortieren.

Hier hat Mr. Burns recht, RPP63. Daher die Spalte X, die die Zeilennummer rettet.

[MisterBurns]

@Ralf: Deswegen hatte ich nach dem Hintergrund gefragt :)
Die von mir angemerkte dritte Spalte ist aber essentiell, sonst sind die Werte vollkommen aussagelos.

[koreandi]

Hallo zusammen.

Vielen Dank für eure Vorschläge und Ideen.
Heute Abend werde ich meinen Rechner neu aufsetzen und das alles testen.

Die "Datenbank" soll später als CSV für eine Webpage mit Produktsuche funktionieren.
Ich habe die Daten bereits in dem Ursprungsformat auf 90.000 Zeilen vorliegen.

Aussage von einem Webpage programmier ist, dass ein Suchen nach dem neuen Muster schneller und effizienter sein.
Da ich mich mit dem Programmieren, Datenbanken und so nicht suuuuper auskenne, wollte ich einfach mal die Datenbank in dem neuen Format bereitstellen.

Ich melde mich am Wochenende zurück und gebe feedback.

Dank nochmal an alle.