Thread: Pivot Table / Chart

Hi,I hope there is a relatively easy solution to an issue I've tried to resolve and am completely stumped!!! The information I'm working relates to airline arrivals and departures.I get a simple spreadsheet on a monthly basis (I've attached an abbreviated copy) that tells me:Flight 123 will arrive at xx:xx time and depart at yy:yy time on dd/mm/yyFlight 234 will arrive at xx:xx time and depart at yy:yy time on dd/mm/yyetc.The problem I'm having is the format of the arrival and departure information: it currently is listed in the columns as "Effect" for the first date it will occur that month, and "Discon" for the last date it will occur that month. (ie: Flight 123 will start arriving on 4/02/11 and do so daily until 4/17/11.What I'm trying to do is manipulate the spreadsheet so it will display the info for the inclusive dates in between....ie: Flt 123 also comes in on 4/03/11, 4/04/11, etc. If it could do that, I could then do a simple pivot table based on the dates and arrival/departure times. (the second page of my attached sheet is the pivot table I've got so far).My goal with the pivot table is to reflect simply each day of the upcoming month, and to reflect the total flights arriving between 0700-0759hrs that day, between 0800-0859hrs that day, etc. Is this something someone could review and give me some guidance? I'm not very proficient at Excel yet, but I'm learning! Thank You,Dave(Also... if what I've described is possible, there will be another issue to deal with: the Column "Frequency" reports whether these flights are "daily", or limited to certain days of the week. (For instance "x7" means it does not operate on Sundays, "135" means it operates only Mon, Wed, and Fri. "Daily" of course means 7-days a week.)
The attached is an abbreviated version of the spreadsheet....

Try this:

Option Explicit

Sub x()
    Dim rInp        As Range
    Dim rOut        As Range
    Dim iRow        As Long
    Dim nRow        As Long

    Application.ScreenUpdating = False
    Sheet2.Columns.Delete

    With Sheet1
        Set rInp = .Range("A2", .Cells(.Rows.Count, "J").End(xlUp))
    End With

    With rInp
        .Rows(0).Copy Sheet2.Range("A1")
        
        ' Copy flights
        For iRow = 1 To .Rows.Count
            nRow = .Cells(iRow, "C").Value - .Cells(iRow, "B").Value + 1
            Set rOut = Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(nRow)
            .Rows(iRow).Copy rOut
            rOut.Range("B1").AutoFill rOut.Columns("B"), xlFillDays
        Next iRow
    End With

    With Sheet2
        .Range("B1:C1").Value = Array("Date", "Weekday")
        Set rOut = .Range("A2", .Cells(.Rows.Count, "J").End(xlUp))
        rOut.Columns("C").FormulaR1C1 = "=weekday(rc[-1], 2)"
        rOut.Columns("C").NumberFormat = "0"
    End With
    
    ' Delete rows for flights not operating on a given day
    Dim sFrq        As String
    Dim iPos        As Long

    For iRow = rOut.Rows.Count To 1 Step -1
        With rOut.Rows(iRow).EntireRow
            sFrq = Trim(UCase(.Range("D1").Text))
            iPos = InStr(sFrq, .Range("C1").Text)
            
            If iPos Then
                ' "X67" means except Sat & Sun
                If sFrq Like "X*" Then .Delete
            Else
                ' "135" means Mon/Wed/Fri
                If IsNumeric(sFrq) Then .Delete
            End If
        End With
    Next iRow
    
    Application.Goto rOut
    rOut.EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:

Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button