Herbers Excel/VBA-Archiv

Spalte in Zeile umwandeln mit referenz

• Spalte in Zeile umwandeln mit referenz von Tobi vom 10.10.2020 15:25:22
• AW: Spalte in Zeile umwandeln mit referenz - von Hajo_Zi am 10.10.2020 15:29:28
• AW: verscheidene Lösungsvarianten möglich ... - von neopa C am 10.10.2020 15:39:44
• AW: Spalte in Zeile umwandeln mit referenz - von Frank Svoboda am 10.10.2020 16:20:43
  • AW: Spalte in Zeile umwandeln mit referenz - von Tobi am 10.10.2020 17:07:33
    • AW: Spalte in Zeile umwandeln mit referenz - von Frank Svoboda am 10.10.2020 18:53:41

Betrifft: Spalte in Zeile umwandeln mit referenz von: Tobi
Geschrieben am: 10.10.2020 15:25:22

Hallo zusammen,

Ich habe eine Exceltabelle welche die Auftragsnummer (wiederholt sich) in Spalte A und in Spalte B die bestellten Artikel enthält.

Ich suche nach einer Möglichkeit die Daten so anzuordnen das in Spalte A die Auftragsnummer steht und die Artikelnummer in den spalten rechts davon, so das jeder Auftrag nur noch eine Zeile hat

Ich habe eine Beispiel Datei mit 2 Blättern einmal den ist Zustand und einmal wie die Ausgabe aussehen soll.

https://www.herber.de/bbs/user/140781.xlsx

Transponieren funktioniert ja nicht das dann keine Zuordnung der Artikel mehr zum Auftrag vorhanden ist.

  

Betrifft: AW: Spalte in Zeile umwandeln mit referenz
von: Hajo_Zi
Geschrieben am: 10.10.2020 15:29:28

https://www.herber.de/excelformeln/pages/SVERWEIS_bei_mehrfach_vorkommendem_Suchkriterium.html

  

Betrifft: AW: verscheidene Lösungsvarianten möglich ...
von: neopa C
Geschrieben am: 10.10.2020 15:39:44

Hallo Tobi,

... außer VBA gibt es die Möglichkeit ohne Formeln mit der Funktion Daten abrufen und transformieren) und auch reine Formellösung. Hier gibt es wiederum verschieden Varianten teils in auch in Abhängigkeit von Deiner wirklich eingesetzten Excelversion´. In der angegebenen XL-Version bevorzuge ich folgende:

In A2:
=WENNFEHLER(INDEX(Tabelle1[Auftrag];AGGREGAT(15;6;(ZEILE(Tabelle1[Auftrag])-ZEILE(Tabelle1[#Kopfzeilen]))/(ZÄHLENWENN(A$1:A1;Tabelle1[Auftrag])=0);1));"")

und weit genug nach unten ziehend kopieren.
dann in B2:
=WENNFEHLER(INDEX(Tabelle1;AGGREGAT(15;6;(ZEILE(Tabelle1)-ZEILE(Tabelle1[#Kopfzeilen]))/(INDEX(Tabelle1;;1)=$A2);SPALTE(A1));2);"")

und weit genug nach unten und rechts ziehend kopieren.

Gruß Werner
.. , - ...

  

Betrifft: AW: Spalte in Zeile umwandeln mit referenz
von: Frank Svoboda
Geschrieben am: 10.10.2020 16:20:43

Hallo Tobi,

das folgende VBA kann das Umsortieren erledigen
 

Sub Umsortieren()
    Dim region As Range, zeile As Long, zeile1 As Long, zeileN As Long
    Dim auftrag As String, vorherauftrag As String, artikel As Long
    ' Arbeitsbereich bestimmen
    Set region = [A2].CurrentRegion
    zeile1 = region.Row
    zeileN = zeile1 + region.Rows.Count - 1
    ' Zu Beginn gibts keinen Vorher-Autrag
    vorherauftrag = ""
    ' Kopfzeile nicht beachten
    zeile = zeile1 + 1
    ' Schleife über alle Zeilen des Bereichs
    Do While zeile <= zeileN
        ' Daten holen
        auftrag = Cells(zeile, 1).Value
        artikel = Cells(zeile, 2).Value
        ' Wenn es der gleiche Auftrag wie vorher ist:
        If auftrag = vorherauftrag Then
            ' Letzte freie Zelle in der Zeile vorher ermitteln und Artikel dort speichern
            Cells(zeile - 1, 1).End(xlToRight).Offset(0, 1).Value = artikel
            ' Aktuelle Zeile löschen
            Rows(zeile).EntireRow.Delete
            ' Bereich um 1 verkleinern, weil Zeile gelöscht
            zeileN = zeileN - 1
        ' Sonst:
        Else
            ' Nächste Zeile
            zeile = zeile + 1
            ' Neuer Vorher-Auftrag
            vorherauftrag = auftrag
        End If
    Loop
End Sub

Die abwechselnden Hintergrundfarben berücksichtigt es allerding noch nicht.

Viele Grüße
Frank

  

Betrifft: AW: Spalte in Zeile umwandeln mit referenz
von: Tobi
Geschrieben am: 10.10.2020 17:07:33

Ihr seit genial. Vielen Dank!
Mit der Formel hat es nicht ganz geklappt. Excel hat sich aufgehängt. (die Originaldatei hatte 144k Zeilen.)

Mit dem VBA Code von Frank hat es super geklappt.

  

Betrifft: AW: Spalte in Zeile umwandeln mit referenz
von: Frank Svoboda
Geschrieben am: 10.10.2020 18:53:41

Prima Tobi,
mein Skript hat allerdings noch einen Haken: Wenn in einer Zeile gar kein Artikel angegeben ist, ordnet es falsch ein. Kann man aber durch einfügen eines if...then ändern. Wo man das einfügt, hängt davon ab, was in diesen Fällen passieren soll.
Viele Grüße
Frank