Microsoft Excel

Herbers Excel/VBA-Archiv

Datumsintervall zerlegen in Zeile je Tag

Betrifft: Datumsintervall zerlegen in Zeile je Tag von: Lutz
Geschrieben am: 22.11.2012 11:12:21

Hallo Excel Profis,

ich habe ein Problem bei dem mein Wissen mal wieder nicht ausreicht:

Ich habe eine Liste in der in Spalte A, B und C ein Name etc. stehen, in Spalte D ein Datum von und in Spalte E ein Datum bis.

Jetzt brauche ich aber in meiner Auswertung je Tag eine Zeile - das müßte man per Makro zerlegen.

Vorher
Müller, Hans, normal, - von 01.01.2012 bis 03.01.2012
Müller, Hans, normal, - von 04.01.2012 bis 04.01.2012

Nachher sollte es so aussehen:

Müller, Hans, normal, - von 01.01.2012 bis 01.01.2012
Müller, Hans, normal, - von 02.01.2012 bis 02.01.2012
Müller, Hans, normal, - von 03.01.2012 bis 03.01.2012
Müller, Hans, normal, - von 04.01.2012 bis 04.01.2012

Kann man das mit einem Makro umsetzen? Das die Spalte bis auch angepaßt wird wäre schön ist aber nicht zwingend notwendig.

Vielen Dank schon einmal und viele Grüße Lutz

  

Betrifft: AW: Datumsintervall zerlegen in Zeile je Tag von: Klaus M.vdT.
Geschrieben am: 22.11.2012 12:31:38

Hi,

deine Eingaben stehen genau wie beschrieben in Tabelle1, A,B,C,D,E
als Ausgabe gibt es eine leere Tabelle2 mit den gleichen Überschriften A,B,C,D,E
Der erste Eintrag ist in Zeile 2 (darüber sind überschriften).
Die Spalte in der Ausgabe habe ich nicht angepasst.

Option Explicit

Sub VieleZeilen()
Dim wksInput As Worksheet
Dim wksOutput As Worksheet
Dim iCol As Integer
Dim lRow As Long

'bei dir heissen die Tabellen bestimmt nicht "1" und "2" .. hier ändern!
Set wksInput = Sheets("Tabelle1")
Set wksOutput = Sheets("Tabelle2")

'die erste SPALTE der Ausgangstabelle ... ich nehme an A (also 1, B wäre 2 usw..) falls nicht,  _
hier ändern!
'Ebenso die erste Zeile (ich nehme an 2, da Überschriften).
'ACHTUNG! Ich gehe davon aus, Spalte und Zeile der Ergebnistabelle sind die gleichen!!
iCol = 1        'erste Spalte
lRow = 2        'erste Zeile

Dim lLastRow As Long
Dim lLastRow2 As Long
Dim iAnz As Integer
Dim rCop As Range

lLastRow2 = wksOutput.Cells(Rows.Count, iCol).End(xlUp).Row + 1

With wksInput
    lLastRow = .Cells(Rows.Count, iCol).End(xlUp).Row
    For Each rCop In .Range(Cells(lRow, iCol), Cells(lLastRow, iCol))
        iAnz = rCop.Offset(0, 4) - rCop.Offset(0, 3) + 1   'BIS_Datum minus VON_Datum plus 1  _
gleich Anzahl Tage
        rCop.EntireRow.Copy
        wksOutput.Cells(lLastRow2, iCol).Resize(iAnz, 1).PasteSpecial
        lLastRow2 = lLastRow2 + iAnz
    Next rCop
End With

Application.CutCopyMode = xlCopy

End Sub
Grüße,
Klaus M.vdT.


  

Betrifft: AW: Datumsintervall zerlegen in Zeile je Tag von: Lutz
Geschrieben am: 22.11.2012 13:57:18

Hallo Klaus,

vielen Dank - klappt super.

Ich habe nur ein Problem: er ändert das Datum auch nicht in der Spalte Begin (Spalte D), d.h. ich habe dann da x den 01.01.2012 stehen und als nächstes kommt dann der 04.01.2012.

Bekommt man das noch hin?


Viele Grüße und vielen Dank Lutz


  

Betrifft: AW: Datumsintervall zerlegen in Zeile je Tag von: Klaus M.vdT.
Geschrieben am: 22.11.2012 14:13:07

Teste mal:

Option Explicit

Sub VieleZeilen()
Dim wksInput As Worksheet
Dim wksOutput As Worksheet
Dim iCol As Integer
Dim lRow As Long

'bei dir heissen die Tabellen bestimmt nicht "1" und "2" .. hier ändern!
Set wksInput = Sheets("Tabelle1")
Set wksOutput = Sheets("Tabelle2")

'die erste SPALTE der Ausgangstabelle ... ich nehme an A (also 1, B wäre 2 usw..) falls nicht,  _
_
hier ändern!
'Ebenso die erste Zeile (ich nehme an 2, da Überschriften).
'ACHTUNG! Ich gehe davon aus, Spalte und Zeile der Ergebnistabelle sind die gleichen!!
iCol = 1        'erste Spalte
lRow = 2        'erste Zeile

Dim lLastRow As Long
Dim lLastRow2 As Long
Dim iAnz As Integer
Dim rCop As Range

lLastRow2 = wksOutput.Cells(Rows.Count, iCol).End(xlUp).Row + 1

With wksOutput

    lLastRow = wksInput.Cells(Rows.Count, iCol).End(xlUp).Row
    For Each rCop In wksInput.Range(wksInput.Cells(lRow, iCol), wksInput.Cells(lLastRow, iCol))
        iAnz = rCop.Offset(0, 4) - rCop.Offset(0, 3) + 1   'BIS_Datum minus VON_Datum plus 1 _
gleich Anzahl Tage
        rCop.EntireRow.Copy
            
            'Eintrag N-Mal einfügen
            .Cells(lLastRow2, iCol).Resize(iAnz, 1).PasteSpecial
            
            'Datum "von" steht genau drei Spalten weiter rechts als die erste Spalte, ändern ab  _
dem zweiten Datum
            .Cells(lLastRow2 + 1, iCol + 3).Resize(iAnz - 1, 1).FormulaR1C1 = "=R[-1]C+1"
            
        lLastRow2 = lLastRow2 + iAnz
    Next rCop
            
    'optional (auskommentieren, wenns nicht passt):
    'BIS Spalte entspricht VON Spalte. Mach ich nicht einzeln pro Datum, sondern einmal für  _
alle Datümer.
    .Range(.Cells(2, iCol + 3), .Cells(lLastRow2, iCol + 3)).Copy .Range(.Cells(2, iCol + 4), . _
Cells(lLastRow2, iCol + 4))

End With

Application.CutCopyMode = xlCopy

End Sub
Achtung! sieht auf den ersten Blick so aus, als hätt ich nur diese Zeile eingefügt:
.Range(.Cells(2, iCol + 3), .Cells(lLastRow2, iCol + 3)).Copy .Range(.Cells(2, iCol + 4), .Cells(lLastRow2, iCol + 4))
Ausserdem habe ich aber einen ganzen WITH / END WITH Rahmen umbenannt und verschoben, daher bitte den ganzen Code kopieren und nicht nur die Zeile updaten

Grüße,
Klaus M.vdT.


  

Betrifft: AW: Datumsintervall zerlegen in Zeile je Tag von: Lutz
Geschrieben am: 22.11.2012 14:21:40

Hallo Klaus,

klappt perfekt - wirklich genial.

Tausend Dank und noch einen schönen Tag für Dich

Liebe Grüße Lutz


  

Betrifft: Danke für die Rückmeldung! owT von: Klaus M.vdT.
Geschrieben am: 22.11.2012 14:28:10

.


  

Betrifft: AW: Datumsintervall zerlegen in Zeile je Tag von: Christian
Geschrieben am: 22.11.2012 13:10:46

Hallo Lutz,

Vorschlag mit Formeln und Hilfsspalten:

 ABCDEFGHIJKLM
1NameVornameSchichtvonbis   NameVornameSchichtvonbis
2MüllerHansnormal01.01.201203.01.2012311MüllerHansnormal01.01.201201.01.2012
3MüllerHansnormal04.01.201204.01.2012421MüllerHansnormal02.01.201202.01.2012
4       1MüllerHansnormal03.01.201203.01.2012
5       2MüllerHansnormal04.01.201204.01.2012
6             
7             
8             
9             
10             
11             
12             
13             

Formeln der Tabelle
ZelleFormel
F2=WENN(A2="";"";SUMME(F1:F1)+E2-D2+1)
G2=WENN(A2="";"";ZEILE(A1))
H2=WENN(F2="";"";G2)
I2=WENN($H2="";"";INDEX(A:A;VERGLEICH($H2;$G:$G;0)))
J2=WENN($H2="";"";INDEX(B:B;VERGLEICH($H2;$G:$G;0)))
K2=WENN($H2="";"";INDEX(C:C;VERGLEICH($H2;$G:$G;0)))
L2=WENN($H2="";"";WENN(ZÄHLENWENN($H$2:H2;H2)=1;INDEX(D:D;VERGLEICH(H2;G:G;0)); INDEX(D:D;VERGLEICH(H2;G:G;0))-1+ZÄHLENWENN($H$2:H2;H2)))
M2=WENN($H2="";"";L2)
F3=WENN(A3="";"";SUMME(F2:F2)+E3-D3+1)
G3=WENN(A3="";"";ZEILE(A2))
H3=WENN(ZEILE(A2)>MAX(F:F); "";WENN(ZEILE(A2)>INDEX(F:F;VERGLEICH(H2;G:G;0)); INDEX(G:G;VERGLEICH(H2;G:G;0)+1); H2))
I3=WENN($H3="";"";INDEX(A:A;VERGLEICH($H3;$G:$G;0)))
J3=WENN($H3="";"";INDEX(B:B;VERGLEICH($H3;$G:$G;0)))
K3=WENN($H3="";"";INDEX(C:C;VERGLEICH($H3;$G:$G;0)))
L3=WENN($H3="";"";WENN(ZÄHLENWENN($H$2:H3;H3)=1;INDEX(D:D;VERGLEICH(H3;G:G;0)); INDEX(D:D;VERGLEICH(H3;G:G;0))-1+ZÄHLENWENN($H$2:H3;H3)))
M3=WENN($H3="";"";L3)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

MfG Christian


  

Betrifft: AW: Datumsintervall zerlegen in Zeile je Tag von: Lutz
Geschrieben am: 22.11.2012 14:13:58

Hallo Christian,

ich bin mehr als beeindruckt, ich hätte nicht gedacht das man das über eine Formel auch lösen kann.


Respekt, klappt echt super.


Viele Grüße und vielen lieben Dank Lutz


  

Betrifft: warum offen? von: Klaus M.vdT.
Geschrieben am: 22.11.2012 14:16:35

Hallo Lutz,

ich find Christians Lösung auch super, aber warum lässt du den Beitrag dann offen?

Grüße,
Klaus M.vdT.


  

Betrifft: AW: warum offen? von: Lutz
Geschrieben am: 22.11.2012 14:23:26

Hallo Klaus/Christian - sorry ich dachte vorher die Formllösung ginge bei Datumslücken nicht aber sie geht auch da.

Hatte nur den Hacken nicht rausgenommen - Entschuldigung und noch mal vielen Dank an Euch beide

Viele Grüße Lutz