Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1824to1828
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Jede x-te Spalte in eine Spalte (Block)

Jede x-te Spalte in eine Spalte (Block)
23.04.2021 22:59:43
Chris
Hallo Zusammen,
ich habe eine recht große Exceldatei und würde gerne jede Ute Spalte mit der gleichen Bezeichnung in je einer Spalte haben wollen. Gibt es Dafür einen Automatismus?Die Spalten sind immer gleich aufgebaut, allerdings bis zu Spalte CX und ein paar mehr Monate.
Hier seht ihr wie die Daten aktuell aussehen und wie ich sie gerne hätte.
Schonmal vielen Dank vorab.
Ausgangsformat:
A B C D E F G H I J K L
1 Datum Personalnummer Name Gehalt Personalnummer Name Gehalt Personalnummer Name Gehalt
2 01.01.20 1234567 Max Mustermann 4.000,00 € 1234568 Erika Musterfrau 4.200,00 € 1234569 Jon Doe 3.700,00 €
3 01.02.20 1234567 Max Mustermann 4.000,00 € 1234568 Erika Musterfrau 4.200,00 € 1234569 Jon Doe 3.700,00 €
4 01.03.20 1234567 Max Mustermann 4.000,00 € 1234568 Erika Musterfrau 4.300,00 € 1234569 Jon Doe 3.700,00 €
5 01.04.20 1234567 Max Mustermann 4.100,00 € 1234568 Erika Musterfrau 4.300,00 € 1234569 Jon Doe 3.700,00 €
6 01.05.20 1234567 Max Mustermann 4.100,00 € 1234568 Erika Musterfrau 4.300,00 € 1234569 Jon Doe 3.700,00 €
7 01.06.20 1234567 Max Mustermann 4.100,00 € 1234568 Erika Musterfrau 4.100,00 € 1234569 Jon Doe 3.800,00 €
Zielformat:
A B C D
1 Datum Personalnummer Name Gehalt
2 01.01.20 1234567 Max Mustermann 4.000,00 €
3 01.02.20 1234567 Max Mustermann 4.000,00 €
4 01.03.20 1234567 Max Mustermann 4.000,00 €
5 01.04.20 1234567 Max Mustermann 4.100,00 €
6 01.05.20 1234567 Max Mustermann 4.100,00 €
7 01.06.20 1234567 Max Mustermann 4.100,00 €
8 01.01.20 1234568 Erika Musterfrau 4.200,00 €
9 01.02.20 1234568 Erika Musterfrau 4.200,00 €
10 01.03.20 1234568 Erika Musterfrau 4.300,00 €
11 01.04.20 1234568 Erika Musterfrau 4.300,00 €
12 01.05.20 1234568 Erika Musterfrau 4.300,00 €
13 01.06.20 1234568 Erika Musterfrau 4.100,00 €
14 01.01.20 1234569 Jon Doe 3.700,00 €
15 01.02.20 1234569 Jon Doe 3.700,00 €
16 01.03.20 1234569 Jon Doe 3.700,00 €
17 01.04.20 1234569 Jon Doe 3.700,00 €
18 01.05.20 1234569 Jon Doe 3.700,00 €
19 01.06.20 1234569 Jon Doe 3.800,00 €
LG
Chris

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Jede x-te Spalte in eine Spalte (Block)
23.04.2021 23:55:08
Yal
Hallo Chris,
1 Spalte Datum und 3 Spalten Information, die sich wiederholen.
Folgende Code sollte passend sein. Es erzeugt eine separaten Blatt, daher ist das Risiko gering.
Name der Quelltabelle muss angepasst werden.

Sub depiv()
Dim wZ As Worksheet 'Ziel
Dim wQ As Worksheet 'Quelle
Dim r, c 'Row, Column
Dim Z As Range
Set wQ = ThisWorkbook.Worksheets("Tabelle1")
Set wZ = ThisWorkbook.Worksheets.Add
wZ.Range("A1:D1") = wQ.Range("A1:D1").Value 'Überschrift
For r = 2 To wQ.Range("A99999").End(xlUp).Row
For c = 2 To wQ.Range("XFD2").End(xlToLeft).Column Step 3
Set Z = wZ.Range("A99999").End(xlUp).Offset(1, 0) 'neue Zeile
Z = wQ.Cells(r, 1).Value 'Datum
Z.Offset(0, 1).Resize(1, 3) = wQ.Cells(r, c).Resize(1, 3).Value 'Daten
Next c
Next r
End Sub
Viel Erfolg damit
VG
Yal
Anzeige
AW: Jede x-te Spalte in eine Spalte (Block)
24.04.2021 00:15:21
Chris
Hi Yal,
perfekt, funktioniert einwandfrei und ist genau das was ich mir vorgestellt habe. Vielen lieben Dank!
VG
Chris
AW: Jede x-te Spalte in eine Spalte (Block)
26.04.2021 15:06:16
Chris
Ich würde gerne zusätzlich noch die Hintergrundfarbe der Zellen mit übertragen.
Wenn ich das einbaue werden diese jedoch schwarz angezeigt (über "Interior.Color")
Gibt es eine Möglichkeit ".Value" und ".Interior.Color" miteinander zu verbinden?
AW: Jede x-te Spalte in eine Spalte (Block)
26.04.2021 15:41:31
Yal
Hallo Chris,
dann geht es nicht mehr, die 3 Zellen "en block" zu übertragen. Da muss eine kleine For-Scheife rein:

Sub depiv()
Dim wZ As Worksheet 'Ziel
Dim wQ As Worksheet 'Quelle
Dim r, c, i 'Row, Column, Index
Dim Z As Range
Set wQ = ThisWorkbook.Worksheets("Tabelle1")
Set wZ = ThisWorkbook.Worksheets.Add
wZ.Range("A1:D1") = wQ.Range("A1:D1").Value 'Überschrift
For r = 2 To wQ.Range("A99999").End(xlUp).Row
For c = 2 To wQ.Range("XFD2").End(xlToLeft).Column Step 3
Set Z = wZ.Range("A99999").End(xlUp).Offset(1, 0) 'neue Zeile
Z = wQ.Cells(r, 1).Value 'Datum
For i = 1 To 3
Z.Offset(0, i) = wQ.Cells(r, c).Offset(0, i).Value 'Daten
Z.Offset(0, i).Interior.ColorIndex = wQ.Cells(r, c).Offset(0, i).Interior.ColorIndex 'Farbe
Next i
Next c
Next r
End Sub
VG
Yal
Anzeige
AW: Jede x-te Spalte in eine Spalte (Block)
26.04.2021 16:19:28
Chris
Hi Yal,
Danke, so funktioniert es. Ein kleiner Fehler hat sich allerdings eingeschlichen. Der Output hat die Spalte B vergessen zu kopieren. Habe es der Vollständigkeit halber hier nochmal ergänzt:
(Allerdings scheint Excel mit der Formel von Daniel besser zu Recht zu kommen, da die Farben hier an sich RGB-seitig nicht 1:1 übernommen werden.)
  • 
    Sub depiv()
    Dim wZ As Worksheet 'Ziel
    Dim wQ As Worksheet 'Quelle
    Dim r, c, i 'Row, Column, Index
    Dim Z As Range
    Set wQ = ThisWorkbook.Worksheets("Tabelle1")
    Set wZ = ThisWorkbook.Worksheets.Add
    wZ.Range("A1:D1") = wQ.Range("A1:D1").Value 'Überschrift
    For r = 2 To wQ.Range("A99999").End(xlUp).Row
    For c = 2 To wQ.Range("XFD2").End(xlToLeft).Column Step 3
    Set Z = wZ.Range("A99999").End(xlUp).Offset(1, 0) 'neue Zeile
    Z = wQ.Cells(r, 1).Value 'Datum
    For i = 1 To 3
    Z.Offset(0, i) = wQ.Cells(r, c).Offset(0, i-1).Value 'Daten
    Z.Offset(0, i).Interior.ColorIndex = wQ.Cells(r, c).Offset(0, i-1).Interior.ColorIndex 'Farbe
    Next i
    Next c
    Next r
    End Sub
    

  • VG
    Chris
    Anzeige
    AW: Jede x-te Spalte in eine Spalte (Block)
    26.04.2021 16:29:49
    Daniel
    HI
    die Farben sollten besser passen, wenn du .Color statt .ColorIndex verwendest
    der ColorIndex ist ein überbleibsel aus Excel-2003-Tagen, als Excel maximal 56 verschiedene Farben gleichzeitig in einem Tabellenblatt angezeigen konnte.
    Hierzu wurden 56 verschiedene RGB-Werte in einer Tabelle hinterlegt, aus diesen konnte man dann per ColorIndex eine Farbe auswählen.
    Das .Color (der RGB-Wert) war damals nur "ReadOnly".
    Mittlerweile kann man auch den Color-Wert direkt angeben, da Excel seit 2007 die RGB-Farben ohne Einschränkung darstellen kann.
    Der ColorIndex wird nur noch aus kompatibilitätsgründen weitergeführt.
    Fragst du den ColorIndex einer Zelle ab, deren Farbe nicht in der Tabelle hinterlegt ist, so wird der Farbähnlichste ColorIndex verwendet.
    Gruß Daniel
    Anzeige
    AW: Jede x-te Spalte in eine Spalte (Block)
    26.04.2021 15:49:25
    Daniel
    Hi
    spricht was dagegen, die Zellen komplett zu kopieren?
    dann sollten Farben mit übernommen werden.
    
    Sub test()
    Dim shQuelle As Worksheet
    Dim shZiel As Worksheet
    Dim rngDatum As Range
    Dim rngWerte As Range
    Set shQuelle = ActiveSheet
    Set shZiel = Sheets.Add(after:=shQuelle)
    With shQuelle.Cells(1, 1).CurrentRegion
    With .Offset(1, 0).Resize(.Rows.Count - 1)
    Set rngDatum = .Columns(1)
    Set rngWerte = .Offset(0, 1).Resize(, 3)
    Do Until WorksheetFunction.CountA(rngWerte) = 0
    With shZiel.Cells(Rows.Count, 1).End(xlUp)
    rngDatum.Copy .Offset(1, 0)
    rngWerte.Copy .Offset(1, 1)
    End With
    Set rngWerte = rngWerte.Offset(0, 3)
    Loop
    End With
    End With
    End Sub
    
    auch hier erfolgt die Umwandlung in einem neuen Blatt, so dass die Ausgangswerte erhalten bleiben.
    Gruß Daniel
    Anzeige
    AW: Jede x-te Spalte in eine Spalte (Block)
    26.04.2021 16:23:19
    Chris
    Hallo Daniel,
    Dankeschön, das funktioniert auch sehr gut. Beim Verglich der beiden Formeln scheint es, dass Excel die Farbei bei deiner Formel "schöner" übernimmt.
    PS: Die Überschrift hattest du vergessen, aber das kann ich auch händisch nachziehen.
    VG
    Chris
    AW: Jede x-te Spalte in eine Spalte (Block)
    26.04.2021 16:32:24
    Daniel
    Hi
    ja, Überschriften hab ich nicht in den Code geschrieben, das kannst du ja selber.
    nur ein Hinweis zur Kommunikation: "Formeln" ist das was in Zellen steht und mit einem "=" beginnt.
    das hier ist "Code", oder "ein VBA-Makro", oder eine "Prozedur"
    Gruß Daniel

    300 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige