Spilt a date range into one row per day

liorchn · Jun 1, 2014

Hi there,

Can you please help me (I guess with a macro code) how to split a date range into one day per row.

Ive got an excel sheet with 30,000 rows which contains an absence leaves of the company employees (emp number, emp name, absence code, start date, end date).

I want that every row contain only one day and not a date range (I know that this will lead to the addition of many roes but i dont have a choise)

Thank you very much

Peter_SSs · Jun 1, 2014

apo said:
I did a quick test with 5000 rows and it did it in what 'seemed' like under a second.

Wow, I wasn't expecting that and I don't understand how our results could be so different.

I ran your code on 1000 rows of data (the same 4 as in our screen shots repeated down to row 1001) and your code took 26 seconds for me.

apo · Jun 1, 2014

That is strange..

I just tried it by dragging down those 4 rows to row 30 000.

It resulted in data down to row 157492 and took about 8 seconds..

I did try your code on the same amount of data and it took far less time though.. (2 or 3 seconds tops)..

alzon21 · Jul 31, 2015

Hi Peter_SSs
this VBA is awesome,
I am making an flight schedule work sheet. and needs a VBA code like this. unlike on this worksheet, flight schedule worksheet should be generate only day with the said days of operation and not daily.

Example:

[TABLE="width: 703"]
<colgroup><col><col><col span="2"><col><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]Carrier IATA code [/TD]
[TD]Flight number [/TD]
[TD]Effective From DDMMMYY[/TD]
[TD]Effective To DDMMMYY[/TD]
[TD]Days of Operation
numeric values (MTWTFSS)
(1234567) [/TD]
[TD]Departure
Airport[/TD]
[TD]Dep Time
(LT)[/TD]
[TD]Arrival
Airport[/TD]
[TD]Arr Time
(LT)[/TD]
[TD]Seat[/TD]
[TD]Acft code (IATA)[/TD]
[TD]Service Type[/TD]
[/TR]
[TR]
[TD]5J[/TD]
[TD]334[/TD]
[TD]01Oct15[/TD]
[TD]22Oct15[/TD]
[TD]46[/TD]
[TD]MNL[/TD]
[TD]2015[/TD]
[TD]KLO[/TD]
[TD]2130[/TD]
[TD]180[/TD]
[TD]320[/TD]
[TD]J[/TD]
[/TR]
</tbody>[/TABLE]

Generate:
[TABLE="width: 670"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Carrier IATA code [/TD]
[TD]Flight number [/TD]
[TD]Date[/TD]
[TD]Days of Operation
numeric values (MTWTFSS)
(1234567) [/TD]
[TD]Departure
Airport[/TD]
[TD]Dep Time
(LT)[/TD]
[TD]Arrival
Airport[/TD]
[TD]Arr Time
(LT)[/TD]
[TD]Seat[/TD]
[TD]Acft code (IATA)[/TD]
[TD]Service Type[/TD]
[/TR]
[TR]
[TD]5J[/TD]
[TD]334[/TD]
[TD]01Oct15[/TD]
[TD]4[/TD]
[TD]MNL[/TD]
[TD]2015[/TD]
[TD]KLO[/TD]
[TD]2130[/TD]
[TD]180[/TD]
[TD]320[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]5J[/TD]
[TD]334[/TD]
[TD]05Oct15[/TD]
[TD]1[/TD]
[TD]MNL[/TD]
[TD]2015[/TD]
[TD]KLO[/TD]
[TD]2130[/TD]
[TD]180[/TD]
[TD]320[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]5J[/TD]
[TD]334[/TD]
[TD]08Oct15[/TD]
[TD]4[/TD]
[TD]MNL[/TD]
[TD]2015[/TD]
[TD]KLO[/TD]
[TD]2130[/TD]
[TD]180[/TD]
[TD]320[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]5J[/TD]
[TD]334[/TD]
[TD]12Oct15[/TD]
[TD]1[/TD]
[TD]MNL[/TD]
[TD]2015[/TD]
[TD]KLO[/TD]
[TD]2130[/TD]
[TD]180[/TD]
[TD]320[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]5J[/TD]
[TD]334[/TD]
[TD]15Oct15[/TD]
[TD]4[/TD]
[TD]MNL[/TD]
[TD]2015[/TD]
[TD]KLO[/TD]
[TD]2130[/TD]
[TD]180[/TD]
[TD]320[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]5J[/TD]
[TD]334[/TD]
[TD]19Oct15[/TD]
[TD]1[/TD]
[TD]MNL[/TD]
[TD]2015[/TD]
[TD]KLO[/TD]
[TD]2130[/TD]
[TD]180[/TD]
[TD]320[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]5J[/TD]
[TD]334[/TD]
[TD]22Oct15[/TD]
[TD]4[/TD]
[TD]MNL[/TD]
[TD]2015[/TD]
[TD]KLO[/TD]
[TD]2130[/TD]
[TD]180[/TD]
[TD]320[/TD]
[TD]J[/TD]
[/TR]
</tbody>[/TABLE]

thanks peter hopping for positive response

alzon

Peter_SSs · Aug 8, 2015

alzon21 said:
Hi Peter_SSs
this VBA is awesome,
I am making an flight schedule work sheet. and needs a VBA code like this. unlike on this worksheet, flight schedule worksheet should be generate only day with the said days of operation and not daily.

Welcome to the MrExcel board!

My internet has been down most of the time recently so I'm late replying. If you are still needing this, I have some code to try below. However, it doesn't produce the results you gave for your sample data as I couldn't understand them.
Your 'Example' table shows days of operation as 4 and 6 (as I understand it) but your 'Generate' table shows flights on days 4 and 1.

Anyway, see if this is some help. Starting with the data in rows 1:3, the code produces the results shown in rows 6 onward. There may possibly be a bit more tweaking required relating to formatting or placement of the results.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

1

Carrier IATA code

Flight number

Effective From DDMMMYY

Effective To DDMMMYY

Days of Operation numeric values (MTWTFSS) (1234567)

Departure

Dep Time

Arrival

Arr Time

Seat

Acft code (IATA)

Service Type

2

5J

334

1-Oct-15

22-Oct-15

46

MNL

2015

KLO

2130

180

320

J

3

2XZ

207

1-Oct-15

27-Oct-15

135

MNL

1025

SYD

2015

230

388

K

4

5

6

Carrier IATA code

Flight number

Date

Days of Operation numeric values (MTWTFSS) (1234567)

Departure

Dep Time

Arrival

Arr Time

Seat

Acft code (IATA)

Service Type

7

5J

334

1-Oct-15

4

MNL

2015

KLO

2130

180

320

J

8

5J

334

3-Oct-15

6

MNL

2015

KLO

2130

180

320

J

9

5J

334

8-Oct-15

4

MNL

2015

KLO

2130

180

320

J

10

5J

334

10-Oct-15

6

MNL

2015

KLO

2130

180

320

J

11

5J

334

15-Oct-15

4

MNL

2015

KLO

2130

180

320

J

12

5J

334

17-Oct-15

6

MNL

2015

KLO

2130

180

320

J

13

5J

334

22-Oct-15

4

MNL

2015

KLO

2130

180

320

J

14

2XZ

207

2-Oct-15

5

MNL

1025

SYD

2015

230

388

K

15

2XZ

207

5-Oct-15

1

MNL

1025

SYD

2015

230

388

K

16

2XZ

207

7-Oct-15

3

MNL

1025

SYD

2015

230

388

K

17

2XZ

207

9-Oct-15

5

MNL

1025

SYD

2015

230

388

K

18

2XZ

207

12-Oct-15

1

MNL

1025

SYD

2015

230

388

K

19

2XZ

207

14-Oct-15

3

MNL

1025

SYD

2015

230

388

K

20

2XZ

207

16-Oct-15

5

MNL

1025

SYD

2015

230

388

K

21

2XZ

207

19-Oct-15

1

MNL

1025

SYD

2015

230

388

K

22

2XZ

207

21-Oct-15

3

MNL

1025

SYD

2015

230

388

K

23

2XZ

207

23-Oct-15

5

MNL

1025

SYD

2015

230

388

K

24

2XZ

207

26-Oct-15

1

MNL

1025

SYD

2015

230

388

K

Sheet1

Rich (BB code):

Sub One_Day_Per_Row_v2()
  Dim a, b
  Dim rws As Long, nr As Long, i As Long, j As Long, k As Long, r As Long
  Dim FlightDays As String
  Dim TopLeftData As Range, TopLeftResults As Range
  
  Set TopLeftData = Range("A1")
  Set TopLeftResults = Cells(Rows.Count, TopLeftData.Column).End(xlUp).Offset(3)
  
  a = TopLeftData.CurrentRegion.Value
  rws = UBound(a, 1)
  For r = 2 To rws
    a(r, 4) = a(r, 4) - a(r, 3) + 1
    k = k + (Int(a(r, 4) + 6) / 7) * Len(a(r, 5))
  Next r

  ReDim b(1 To k, 1 To 11)
  For r = 2 To rws
    FlightDays = a(r, 5)
    For i = 0 To a(r, 4) - 1
      If InStr(1, FlightDays, Weekday(a(r, 3) + i, vbMonday)) Then
        nr = nr + 1
        b(nr, 1) = a(r, 1)
        b(nr, 2) = a(r, 2)
        b(nr, 3) = a(r, 3) + i
        b(nr, 4) = Weekday(b(nr, 3), vbMonday)
        For j = 5 To 11
          b(nr, j) = a(r, j + 1)
        Next j
      End If
    Next i
  Next r
  
  With TopLeftResults
    .Resize(, 2).Value = TopLeftData.Resize(, 2).Value
    .Offset(, 2).Value = "Date"
    .Offset(, 3).Resize(, 8).Value = TopLeftData.Offset(, 4).Resize(, 8).Value
    With .Offset(1).Resize(nr, 11)
      .Columns(3).NumberFormat = "d-mmm-yy"
      .Value = b
    End With
  End With
End Sub

alzon21 · Aug 8, 2015

first of all thank you for the response peter!

WoW! your are so amazing peter!

my fault on the example , regarding the 4 , 1 . :D

you know what I've studied your code for 1 week and I come up with this code, it will load a minute or two:

Sub One_Day_Per_Row()
Dim a, b
Dim rws As Long, SR As Long, i As Long, j As Long, k As Long, r As Long

a = Range("a2", Range("a" & Rows.Count).End(xlUp)).Resize(, 14).Value
rws = UBound(a, 1)
For r = 1 To rws
a(r, 14) = a(r, 13) - a(r, 12) + 1
k = k + a(r, 14)
Next r
If k < Rows.Count Then
ReDim b(1 To k, 1 To 12)
SR = 1
For r = 1 To rws
For i = 0 To a(r, 14) - 1
For j = 1 To 11
b(SR + i, j) = a(r, j)
Next j
b(SR + i, 12) = a(r, 12) + i
Next i
SR = SR + a(r, 14)
Next r
Sheet2.Range("a2").Resize(k, 12).Value = b
Else
MsgBox "Too many rows"
End If

Dim LR As Long

LR = Sheet2.Range("L" & Rows.Count).End(xlUp).Row
Sheet2.Range("M2").Formula = "=WEEKDAY(L2,2)"
Sheet2.Range("M2").AutoFill Destination:=Sheet2.Range("M2:M" & LR)

Sheet2.Range("N2").Formula = "=IFERROR(FIND(M2,K2),0)"
Sheet2.Range("N2").AutoFill Destination:=Sheet2.Range("N2:N" & LR)

last = Sheet2.Cells(Rows.Count, "N").End(xlUp).Row
For d = last To 1 Step -1
If (Sheet2.Cells(d, "N").Value) = "0" Then

Sheet2.Cells(d, "A").EntireRow.Delete
End If
Next d

End Sub

-----------------

I have notice with your code, when I click twice the button asssigned for this sub it will generate twice, unlike your first VBA even if I click the button many times it just generate once.

peter, is it possible that the generated data goes to sheet 2, and there is popup msgbox if the generated data has a duplicate.

sheet2 will be the data, will append the generated data every time click the cmdButton sub .

thank you so much peter.

alzon21 · Aug 10, 2015

Hi peter!

Good Day!

do you have idea on how to reverse your formula? from the one day per to range per date of operation.

[TABLE="width: 768"]
<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]DATA:[/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[TD="class: xl64, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]Carrier IATA code[/TD]
[TD="class: xl63, width: 64"]Flight number[/TD]
[TD="class: xl63, width: 64"]Date[/TD]
[TD="class: xl63, width: 64"]Days of Operation numeric values (MTWTFSS) (1234567)[/TD]
[TD="class: xl63, width: 64"]Departure[/TD]
[TD="class: xl63, width: 64"]Dep Time[/TD]
[TD="class: xl63, width: 64"]Arrival[/TD]
[TD="class: xl63, width: 64"]Arr Time[/TD]
[TD="class: xl63, width: 64"]Seat[/TD]
[TD="class: xl63, width: 64"]Acft code (IATA)[/TD]
[TD="class: xl63, width: 64"]Service Type[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]5J[/TD]
[TD="class: xl63, width: 64"]334[/TD]
[TD="class: xl65, width: 64"]1-Oct-15[/TD]
[TD="class: xl63, width: 64"]4[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]KLO[/TD]
[TD="class: xl63, width: 64"]2130[/TD]
[TD="class: xl63, width: 64"]180[/TD]
[TD="class: xl63, width: 64"]320[/TD]
[TD="class: xl63, width: 64"]J[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]5J[/TD]
[TD="class: xl63, width: 64"]334[/TD]
[TD="class: xl65, width: 64"]3-Oct-15[/TD]
[TD="class: xl63, width: 64"]6[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]KLO[/TD]
[TD="class: xl63, width: 64"]2130[/TD]
[TD="class: xl63, width: 64"]180[/TD]
[TD="class: xl63, width: 64"]320[/TD]
[TD="class: xl63, width: 64"]J[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]5J[/TD]
[TD="class: xl63, width: 64"]334[/TD]
[TD="class: xl65, width: 64"]8-Oct-15[/TD]
[TD="class: xl63, width: 64"]4[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]KLO[/TD]
[TD="class: xl63, width: 64"]2130[/TD]
[TD="class: xl63, width: 64"]180[/TD]
[TD="class: xl63, width: 64"]320[/TD]
[TD="class: xl63, width: 64"]J[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]5J[/TD]
[TD="class: xl63, width: 64"]334[/TD]
[TD="class: xl65, width: 64"]10-Oct-15[/TD]
[TD="class: xl63, width: 64"]6[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]KLO[/TD]
[TD="class: xl63, width: 64"]2130[/TD]
[TD="class: xl63, width: 64"]180[/TD]
[TD="class: xl63, width: 64"]320[/TD]
[TD="class: xl63, width: 64"]J[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]5J[/TD]
[TD="class: xl63, width: 64"]334[/TD]
[TD="class: xl65, width: 64"]17-Oct-15[/TD]
[TD="class: xl63, width: 64"]6[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]KLO[/TD]
[TD="class: xl63, width: 64"]2130[/TD]
[TD="class: xl63, width: 64"]180[/TD]
[TD="class: xl63, width: 64"]320[/TD]
[TD="class: xl63, width: 64"]J[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]5J[/TD]
[TD="class: xl63, width: 64"]334[/TD]
[TD="class: xl65, width: 64"]22-Oct-15[/TD]
[TD="class: xl63, width: 64"]4[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]KLO[/TD]
[TD="class: xl63, width: 64"]2130[/TD]
[TD="class: xl63, width: 64"]180[/TD]
[TD="class: xl63, width: 64"]320[/TD]
[TD="class: xl63, width: 64"]J[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]2XZ[/TD]
[TD="class: xl63, width: 64"]207[/TD]
[TD="class: xl65, width: 64"]2-Oct-15[/TD]
[TD="class: xl63, width: 64"]5[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]1025[/TD]
[TD="class: xl63, width: 64"]SYD[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]230[/TD]
[TD="class: xl63, width: 64"]388[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]2XZ[/TD]
[TD="class: xl63, width: 64"]207[/TD]
[TD="class: xl65, width: 64"]5-Oct-15[/TD]
[TD="class: xl63, width: 64"]1[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]1025[/TD]
[TD="class: xl63, width: 64"]SYD[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]230[/TD]
[TD="class: xl63, width: 64"]388[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]2XZ[/TD]
[TD="class: xl63, width: 64"]207[/TD]
[TD="class: xl65, width: 64"]7-Oct-15[/TD]
[TD="class: xl63, width: 64"]3[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]1025[/TD]
[TD="class: xl63, width: 64"]SYD[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]230[/TD]
[TD="class: xl63, width: 64"]388[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]2XZ[/TD]
[TD="class: xl63, width: 64"]207[/TD]
[TD="class: xl65, width: 64"]9-Oct-15[/TD]
[TD="class: xl63, width: 64"]5[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]1025[/TD]
[TD="class: xl63, width: 64"]SYD[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]230[/TD]
[TD="class: xl63, width: 64"]388[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]2XZ[/TD]
[TD="class: xl63, width: 64"]207[/TD]
[TD="class: xl65, width: 64"]12-Oct-15[/TD]
[TD="class: xl63, width: 64"]1[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]1025[/TD]
[TD="class: xl63, width: 64"]SYD[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]230[/TD]
[TD="class: xl63, width: 64"]388[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]2XZ[/TD]
[TD="class: xl63, width: 64"]207[/TD]
[TD="class: xl65, width: 64"]14-Oct-15[/TD]
[TD="class: xl63, width: 64"]3[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]1025[/TD]
[TD="class: xl63, width: 64"]SYD[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]230[/TD]
[TD="class: xl63, width: 64"]388[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]2XZ[/TD]
[TD="class: xl63, width: 64"]207[/TD]
[TD="class: xl65, width: 64"]16-Oct-15[/TD]
[TD="class: xl63, width: 64"]5[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]1025[/TD]
[TD="class: xl63, width: 64"]SYD[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]230[/TD]
[TD="class: xl63, width: 64"]388[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]2XZ[/TD]
[TD="class: xl63, width: 64"]207[/TD]
[TD="class: xl65, width: 64"]19-Oct-15[/TD]
[TD="class: xl63, width: 64"]1[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]1025[/TD]
[TD="class: xl63, width: 64"]SYD[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]230[/TD]
[TD="class: xl63, width: 64"]388[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]2XZ[/TD]
[TD="class: xl63, width: 64"]207[/TD]
[TD="class: xl65, width: 64"]23-Oct-15[/TD]
[TD="class: xl63, width: 64"]5[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]1025[/TD]
[TD="class: xl63, width: 64"]SYD[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]230[/TD]
[TD="class: xl63, width: 64"]388[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]2XZ[/TD]
[TD="class: xl63, width: 64"]207[/TD]
[TD="class: xl65, width: 64"]26-Oct-15[/TD]
[TD="class: xl63, width: 64"]1[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]1025[/TD]
[TD="class: xl63, width: 64"]SYD[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]230[/TD]
[TD="class: xl63, width: 64"]388[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl64"]RESULT[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]Carrier IATA code[/TD]
[TD="class: xl63, width: 64"]Flight number[/TD]
[TD="class: xl63, width: 64"]Effective From DDMMMYY[/TD]
[TD="class: xl63, width: 64"]Effective To DDMMMYY[/TD]
[TD="class: xl63, width: 64"]Days of Operation numeric values (MTWTFSS) (1234567)[/TD]
[TD="class: xl63, width: 64"]Departure[/TD]
[TD="class: xl63, width: 64"]Dep Time[/TD]
[TD="class: xl63, width: 64"]Arrival[/TD]
[TD="class: xl63, width: 64"]Arr Time[/TD]
[TD="class: xl63, width: 64"]Seat[/TD]
[TD="class: xl63, width: 64"]Acft code (IATA)[/TD]
[TD="class: xl63, width: 64"]Service Type[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]5J[/TD]
[TD="class: xl63, width: 64"]334[/TD]
[TD="class: xl65, width: 64"]1-Oct-15[/TD]
[TD="class: xl65, width: 64"]10-Oct-15[/TD]
[TD="class: xl63, width: 64"]46[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]KLO[/TD]
[TD="class: xl63, width: 64"]2130[/TD]
[TD="class: xl63, width: 64"]180[/TD]
[TD="class: xl63, width: 64"]320[/TD]
[TD="class: xl63, width: 64"]J[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]5J[/TD]
[TD="class: xl63, width: 64"]334[/TD]
[TD="class: xl65, width: 64"]17-Oct-15[/TD]
[TD="class: xl65, width: 64"]22-Oct-15[/TD]
[TD="class: xl63, width: 64"]46[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]KLO[/TD]
[TD="class: xl63, width: 64"]2130[/TD]
[TD="class: xl63, width: 64"]180[/TD]
[TD="class: xl63, width: 64"]320[/TD]
[TD="class: xl63, width: 64"]J[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]2XZ[/TD]
[TD="class: xl63, width: 64"]207[/TD]
[TD="class: xl65, width: 64"]1-Oct-15[/TD]
[TD="class: xl65, width: 64"]14-Oct-15[/TD]
[TD="class: xl63, width: 64"]135[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]1025[/TD]
[TD="class: xl63, width: 64"]SYD[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]230[/TD]
[TD="class: xl63, width: 64"]388[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]2XZ[/TD]
[TD="class: xl63, width: 64"]207[/TD]
[TD="class: xl65, width: 64"]16-Oct-15[/TD]
[TD="class: xl65, width: 64"]26-Oct-15[/TD]
[TD="class: xl63, width: 64"]15[/TD]
[TD="class: xl63, width: 64"]MNL[/TD]
[TD="class: xl63, width: 64"]1025[/TD]
[TD="class: xl63, width: 64"]SYD[/TD]
[TD="class: xl63, width: 64"]2015[/TD]
[TD="class: xl63, width: 64"]230[/TD]
[TD="class: xl63, width: 64"]388[/TD]
[TD="class: xl63, width: 64"]K[/TD]
[/TR]
</tbody>[/TABLE]

thanks alzon

alzon21 · Aug 10, 2015

Peter_SSs said:
Welcome to the MrExcel board!

My internet has been down most of the time recently so I'm late replying. If you are still needing this, I have some code to try below. However, it doesn't produce the results you gave for your sample data as I couldn't understand them.
Your 'Example' table shows days of operation as 4 and 6 (as I understand it) but your 'Generate' table shows flights on days 4 and 1.

Anyway, see if this is some help. Starting with the data in rows 1:3, the code produces the results shown in rows 6 onward. There may possibly be a bit more tweaking required relating to formatting or placement of the results.

Sheet1

A B C D E F G H I J K L

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:63px;"><col style="width:77px;"><col style="width:123px;"><col style="width:161px;"><col style="width:173px;"><col style="width:75px;"><col style="width:56px;"><col style="width:57px;"><col style="width:68px;"><col style="width:46px;"><col style="width:73px;"><col style="width:61px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Carrier IATA code[/TD]
[TD="align: center"]Flight number[/TD]
[TD="align: center"]Effective From DDMMMYY[/TD]
[TD="align: center"]Effective To DDMMMYY[/TD]
[TD="align: center"]Days of Operation numeric values (MTWTFSS) (1234567)[/TD]
[TD="align: center"]Departure[/TD]
[TD="align: center"]Dep Time[/TD]
[TD="align: center"]Arrival[/TD]
[TD="align: center"]Arr Time[/TD]
[TD="align: center"]Seat[/TD]
[TD="align: center"]Acft code (IATA)[/TD]
[TD="align: center"]Service Type[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]5J[/TD]
[TD="align: center"]334[/TD]
[TD="align: center"]1-Oct-15[/TD]
[TD="align: center"]22-Oct-15[/TD]
[TD="align: center"]46[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]KLO[/TD]
[TD="align: center"]2130[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"]320[/TD]
[TD="align: center"]J[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]1-Oct-15[/TD]
[TD="align: center"]27-Oct-15[/TD]
[TD="align: center"]135[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]Carrier IATA code[/TD]
[TD="align: center"]Flight number[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Days of Operation numeric values (MTWTFSS) (1234567)[/TD]
[TD="align: center"]Departure[/TD]
[TD="align: center"]Dep Time[/TD]
[TD="align: center"]Arrival[/TD]
[TD="align: center"]Arr Time[/TD]
[TD="align: center"]Seat[/TD]
[TD="align: center"]Acft code (IATA)[/TD]
[TD="align: center"]Service Type[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]5J[/TD]
[TD="align: center"]334[/TD]
[TD="align: center"]1-Oct-15[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]KLO[/TD]
[TD="align: center"]2130[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"]320[/TD]
[TD="align: center"]J[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]5J[/TD]
[TD="align: center"]334[/TD]
[TD="align: center"]3-Oct-15[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]KLO[/TD]
[TD="align: center"]2130[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"]320[/TD]
[TD="align: center"]J[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]5J[/TD]
[TD="align: center"]334[/TD]
[TD="align: center"]8-Oct-15[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]KLO[/TD]
[TD="align: center"]2130[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"]320[/TD]
[TD="align: center"]J[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]5J[/TD]
[TD="align: center"]334[/TD]
[TD="align: center"]10-Oct-15[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]KLO[/TD]
[TD="align: center"]2130[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"]320[/TD]
[TD="align: center"]J[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]5J[/TD]
[TD="align: center"]334[/TD]
[TD="align: center"]15-Oct-15[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]KLO[/TD]
[TD="align: center"]2130[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"]320[/TD]
[TD="align: center"]J[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]5J[/TD]
[TD="align: center"]334[/TD]
[TD="align: center"]17-Oct-15[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]KLO[/TD]
[TD="align: center"]2130[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"]320[/TD]
[TD="align: center"]J[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]5J[/TD]
[TD="align: center"]334[/TD]
[TD="align: center"]22-Oct-15[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]KLO[/TD]
[TD="align: center"]2130[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"]320[/TD]
[TD="align: center"]J[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]2-Oct-15[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]5-Oct-15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]7-Oct-15[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]9-Oct-15[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]12-Oct-15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]14-Oct-15[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]16-Oct-15[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]19-Oct-15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]21-Oct-15[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]23-Oct-15[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]26-Oct-15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Rich (BB code):

Sub One_Day_Per_Row_v2() Dim a, b Dim rws As Long, nr As Long, i As Long, j As Long, k As Long, r As Long Dim FlightDays As String Dim TopLeftData As Range, TopLeftResults As Range Set TopLeftData = Range("A1") Set TopLeftResults = Cells(Rows.Count, TopLeftData.Column).End(xlUp).Offset(3) a = TopLeftData.CurrentRegion.Value rws = UBound(a, 1) For r = 2 To rws a(r, 4) = a(r, 4) - a(r, 3) + 1 k = k + (Int(a(r, 4) + 6) / 7) * Len(a(r, 5)) Next r ReDim b(1 To k, 1 To 11) For r = 2 To rws FlightDays = a(r, 5) For i = 0 To a(r, 4) - 1 If InStr(1, FlightDays, Weekday(a(r, 3) + i, vbMonday)) Then nr = nr + 1 b(nr, 1) = a(r, 1) b(nr, 2) = a(r, 2) b(nr, 3) = a(r, 3) + i b(nr, 4) = Weekday(b(nr, 3), vbMonday) For j = 5 To 11 b(nr, j) = a(r, j + 1) Next j End If Next i Next r With TopLeftResults .Resize(, 2).Value = TopLeftData.Resize(, 2).Value .Offset(, 2).Value = "Date" .Offset(, 3).Resize(, 8).Value = TopLeftData.Offset(, 4).Resize(, 8).Value With .Offset(1).Resize(nr, 11) .Columns(3).NumberFormat = "d-mmm-yy" .Value = b End With End With End Sub

Hi peter!

Good Day!

do you have idea on how to reverse your formula? from the one day per to range per date of operation.

[TABLE="class: cms_table, width: 768"]
<tbody>[TR]
[TD="class: cms_table_xl64, width: 64"]DATA:[/TD]
[TD="class: cms_table_xl64, width: 64"][/TD]
[TD="class: cms_table_xl64, width: 64"][/TD]
[TD="class: cms_table_xl64, width: 64"][/TD]
[TD="class: cms_table_xl64, width: 64"][/TD]
[TD="class: cms_table_xl64, width: 64"][/TD]
[TD="class: cms_table_xl64, width: 64"][/TD]
[TD="class: cms_table_xl64, width: 64"][/TD]
[TD="class: cms_table_xl64, width: 64"][/TD]
[TD="class: cms_table_xl64, width: 64"][/TD]
[TD="class: cms_table_xl64, width: 64"][/TD]
[TD="class: cms_table_xl64, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]Carrier IATA code[/TD]
[TD="class: cms_table_xl63, width: 64"]Flight number[/TD]
[TD="class: cms_table_xl63, width: 64"]Date[/TD]
[TD="class: cms_table_xl63, width: 64"]Days of Operation numeric values (MTWTFSS) (1234567)[/TD]
[TD="class: cms_table_xl63, width: 64"]Departure[/TD]
[TD="class: cms_table_xl63, width: 64"]Dep Time[/TD]
[TD="class: cms_table_xl63, width: 64"]Arrival[/TD]
[TD="class: cms_table_xl63, width: 64"]Arr Time[/TD]
[TD="class: cms_table_xl63, width: 64"]Seat[/TD]
[TD="class: cms_table_xl63, width: 64"]Acft code (IATA)[/TD]
[TD="class: cms_table_xl63, width: 64"]Service Type[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]5J[/TD]
[TD="class: cms_table_xl63, width: 64"]334[/TD]
[TD="class: cms_table_xl65, width: 64"]1-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]4[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]KLO[/TD]
[TD="class: cms_table_xl63, width: 64"]2130[/TD]
[TD="class: cms_table_xl63, width: 64"]180[/TD]
[TD="class: cms_table_xl63, width: 64"]320[/TD]
[TD="class: cms_table_xl63, width: 64"]J[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]5J[/TD]
[TD="class: cms_table_xl63, width: 64"]334[/TD]
[TD="class: cms_table_xl65, width: 64"]3-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]6[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]KLO[/TD]
[TD="class: cms_table_xl63, width: 64"]2130[/TD]
[TD="class: cms_table_xl63, width: 64"]180[/TD]
[TD="class: cms_table_xl63, width: 64"]320[/TD]
[TD="class: cms_table_xl63, width: 64"]J[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]5J[/TD]
[TD="class: cms_table_xl63, width: 64"]334[/TD]
[TD="class: cms_table_xl65, width: 64"]8-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]4[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]KLO[/TD]
[TD="class: cms_table_xl63, width: 64"]2130[/TD]
[TD="class: cms_table_xl63, width: 64"]180[/TD]
[TD="class: cms_table_xl63, width: 64"]320[/TD]
[TD="class: cms_table_xl63, width: 64"]J[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]5J[/TD]
[TD="class: cms_table_xl63, width: 64"]334[/TD]
[TD="class: cms_table_xl65, width: 64"]10-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]6[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]KLO[/TD]
[TD="class: cms_table_xl63, width: 64"]2130[/TD]
[TD="class: cms_table_xl63, width: 64"]180[/TD]
[TD="class: cms_table_xl63, width: 64"]320[/TD]
[TD="class: cms_table_xl63, width: 64"]J[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]5J[/TD]
[TD="class: cms_table_xl63, width: 64"]334[/TD]
[TD="class: cms_table_xl65, width: 64"]17-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]6[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]KLO[/TD]
[TD="class: cms_table_xl63, width: 64"]2130[/TD]
[TD="class: cms_table_xl63, width: 64"]180[/TD]
[TD="class: cms_table_xl63, width: 64"]320[/TD]
[TD="class: cms_table_xl63, width: 64"]J[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]5J[/TD]
[TD="class: cms_table_xl63, width: 64"]334[/TD]
[TD="class: cms_table_xl65, width: 64"]22-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]4[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]KLO[/TD]
[TD="class: cms_table_xl63, width: 64"]2130[/TD]
[TD="class: cms_table_xl63, width: 64"]180[/TD]
[TD="class: cms_table_xl63, width: 64"]320[/TD]
[TD="class: cms_table_xl63, width: 64"]J[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]2XZ[/TD]
[TD="class: cms_table_xl63, width: 64"]207[/TD]
[TD="class: cms_table_xl65, width: 64"]2-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]5[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]1025[/TD]
[TD="class: cms_table_xl63, width: 64"]SYD[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]230[/TD]
[TD="class: cms_table_xl63, width: 64"]388[/TD]
[TD="class: cms_table_xl63, width: 64"]K[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]2XZ[/TD]
[TD="class: cms_table_xl63, width: 64"]207[/TD]
[TD="class: cms_table_xl65, width: 64"]5-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]1[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]1025[/TD]
[TD="class: cms_table_xl63, width: 64"]SYD[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]230[/TD]
[TD="class: cms_table_xl63, width: 64"]388[/TD]
[TD="class: cms_table_xl63, width: 64"]K[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]2XZ[/TD]
[TD="class: cms_table_xl63, width: 64"]207[/TD]
[TD="class: cms_table_xl65, width: 64"]7-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]3[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]1025[/TD]
[TD="class: cms_table_xl63, width: 64"]SYD[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]230[/TD]
[TD="class: cms_table_xl63, width: 64"]388[/TD]
[TD="class: cms_table_xl63, width: 64"]K[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]2XZ[/TD]
[TD="class: cms_table_xl63, width: 64"]207[/TD]
[TD="class: cms_table_xl65, width: 64"]9-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]5[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]1025[/TD]
[TD="class: cms_table_xl63, width: 64"]SYD[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]230[/TD]
[TD="class: cms_table_xl63, width: 64"]388[/TD]
[TD="class: cms_table_xl63, width: 64"]K[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]2XZ[/TD]
[TD="class: cms_table_xl63, width: 64"]207[/TD]
[TD="class: cms_table_xl65, width: 64"]12-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]1[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]1025[/TD]
[TD="class: cms_table_xl63, width: 64"]SYD[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]230[/TD]
[TD="class: cms_table_xl63, width: 64"]388[/TD]
[TD="class: cms_table_xl63, width: 64"]K[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]2XZ[/TD]
[TD="class: cms_table_xl63, width: 64"]207[/TD]
[TD="class: cms_table_xl65, width: 64"]14-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]3[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]1025[/TD]
[TD="class: cms_table_xl63, width: 64"]SYD[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]230[/TD]
[TD="class: cms_table_xl63, width: 64"]388[/TD]
[TD="class: cms_table_xl63, width: 64"]K[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]2XZ[/TD]
[TD="class: cms_table_xl63, width: 64"]207[/TD]
[TD="class: cms_table_xl65, width: 64"]16-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]5[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]1025[/TD]
[TD="class: cms_table_xl63, width: 64"]SYD[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]230[/TD]
[TD="class: cms_table_xl63, width: 64"]388[/TD]
[TD="class: cms_table_xl63, width: 64"]K[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]2XZ[/TD]
[TD="class: cms_table_xl63, width: 64"]207[/TD]
[TD="class: cms_table_xl65, width: 64"]19-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]1[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]1025[/TD]
[TD="class: cms_table_xl63, width: 64"]SYD[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]230[/TD]
[TD="class: cms_table_xl63, width: 64"]388[/TD]
[TD="class: cms_table_xl63, width: 64"]K[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]2XZ[/TD]
[TD="class: cms_table_xl63, width: 64"]207[/TD]
[TD="class: cms_table_xl65, width: 64"]23-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]5[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]1025[/TD]
[TD="class: cms_table_xl63, width: 64"]SYD[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]230[/TD]
[TD="class: cms_table_xl63, width: 64"]388[/TD]
[TD="class: cms_table_xl63, width: 64"]K[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]2XZ[/TD]
[TD="class: cms_table_xl63, width: 64"]207[/TD]
[TD="class: cms_table_xl65, width: 64"]26-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]1[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]1025[/TD]
[TD="class: cms_table_xl63, width: 64"]SYD[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]230[/TD]
[TD="class: cms_table_xl63, width: 64"]388[/TD]
[TD="class: cms_table_xl63, width: 64"]K[/TD]
[TD="class: cms_table_xl67"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64"]RESULT[/TD]
[TD="class: cms_table_xl64"][/TD]
[TD="class: cms_table_xl64"][/TD]
[TD="class: cms_table_xl64"][/TD]
[TD="class: cms_table_xl64"][/TD]
[TD="class: cms_table_xl64"][/TD]
[TD="class: cms_table_xl64"][/TD]
[TD="class: cms_table_xl64"][/TD]
[TD="class: cms_table_xl64"][/TD]
[TD="class: cms_table_xl64"][/TD]
[TD="class: cms_table_xl64"][/TD]
[TD="class: cms_table_xl64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]Carrier IATA code[/TD]
[TD="class: cms_table_xl63, width: 64"]Flight number[/TD]
[TD="class: cms_table_xl63, width: 64"]Effective From DDMMMYY[/TD]
[TD="class: cms_table_xl63, width: 64"]Effective To DDMMMYY[/TD]
[TD="class: cms_table_xl63, width: 64"]Days of Operation numeric values (MTWTFSS) (1234567)[/TD]
[TD="class: cms_table_xl63, width: 64"]Departure[/TD]
[TD="class: cms_table_xl63, width: 64"]Dep Time[/TD]
[TD="class: cms_table_xl63, width: 64"]Arrival[/TD]
[TD="class: cms_table_xl63, width: 64"]Arr Time[/TD]
[TD="class: cms_table_xl63, width: 64"]Seat[/TD]
[TD="class: cms_table_xl63, width: 64"]Acft code (IATA)[/TD]
[TD="class: cms_table_xl63, width: 64"]Service Type[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]5J[/TD]
[TD="class: cms_table_xl63, width: 64"]334[/TD]
[TD="class: cms_table_xl65, width: 64"]1-Oct-15[/TD]
[TD="class: cms_table_xl65, width: 64"]10-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]46[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]KLO[/TD]
[TD="class: cms_table_xl63, width: 64"]2130[/TD]
[TD="class: cms_table_xl63, width: 64"]180[/TD]
[TD="class: cms_table_xl63, width: 64"]320[/TD]
[TD="class: cms_table_xl63, width: 64"]J[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]5J[/TD]
[TD="class: cms_table_xl63, width: 64"]334[/TD]
[TD="class: cms_table_xl65, width: 64"]17-Oct-15[/TD]
[TD="class: cms_table_xl65, width: 64"]22-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]46[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]KLO[/TD]
[TD="class: cms_table_xl63, width: 64"]2130[/TD]
[TD="class: cms_table_xl63, width: 64"]180[/TD]
[TD="class: cms_table_xl63, width: 64"]320[/TD]
[TD="class: cms_table_xl63, width: 64"]J[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]2XZ[/TD]
[TD="class: cms_table_xl63, width: 64"]207[/TD]
[TD="class: cms_table_xl65, width: 64"]1-Oct-15[/TD]
[TD="class: cms_table_xl65, width: 64"]14-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]135[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]1025[/TD]
[TD="class: cms_table_xl63, width: 64"]SYD[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]230[/TD]
[TD="class: cms_table_xl63, width: 64"]388[/TD]
[TD="class: cms_table_xl63, width: 64"]K[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63, width: 64"]2XZ[/TD]
[TD="class: cms_table_xl63, width: 64"]207[/TD]
[TD="class: cms_table_xl65, width: 64"]16-Oct-15[/TD]
[TD="class: cms_table_xl65, width: 64"]26-Oct-15[/TD]
[TD="class: cms_table_xl63, width: 64"]15[/TD]
[TD="class: cms_table_xl63, width: 64"]MNL[/TD]
[TD="class: cms_table_xl63, width: 64"]1025[/TD]
[TD="class: cms_table_xl63, width: 64"]SYD[/TD]
[TD="class: cms_table_xl63, width: 64"]2015[/TD]
[TD="class: cms_table_xl63, width: 64"]230[/TD]
[TD="class: cms_table_xl63, width: 64"]388[/TD]
[TD="class: cms_table_xl63, width: 64"]K[/TD]
[/TR]
</tbody>[/TABLE]

thanks alzon

Peter_SSs · Aug 10, 2015

alzon21 said:
Hi peter!

Good Day!

do you have idea on how to reverse your formula? from the one day per to range per date of operation.

Without further guidance/information, I don't see how that could be done logically/consistently. For example, using your sample data for Flight number 207...

- Rows 23-24 below show your expected results.

- Rows 26-27 show a different set of results that seem to me to also fit the same original data.

Also, why does your first 207 result row use a start date of 1-Oct-15 when the first 207 flight is on 2-Oct-15?

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

1

DATA:

2

Carrier IATA code

Flight number

Date

Days of Operation numeric values (MTWTFSS) (1234567)

Departure

Dep Time

Arrival

Arr Time

Seat

Acft code (IATA)

Service Type

9

2XZ

207

2-Oct-15

5

MNL

1025

SYD

2015

230

388

K

10

2XZ

207

5-Oct-15

1

MNL

1025

SYD

2015

230

388

K

11

2XZ

207

7-Oct-15

3

MNL

1025

SYD

2015

230

388

K

12

2XZ

207

9-Oct-15

5

MNL

1025

SYD

2015

230

388

K

13

2XZ

207

12-Oct-15

1

MNL

1025

SYD

2015

230

388

K

14

2XZ

207

14-Oct-15

3

MNL

1025

SYD

2015

230

388

K

15

2XZ

207

16-Oct-15

5

MNL

1025

SYD

2015

230

388

K

16

2XZ

207

19-Oct-15

1

MNL

1025

SYD

2015

230

388

K

17

2XZ

207

23-Oct-15

5

MNL

1025

SYD

2015

230

388

K

18

2XZ

207

26-Oct-15

1

MNL

1025

SYD

2015

230

388

K

19

RESULT

20

Carrier IATA code

Flight number

Effective From DDMMMYY

Effective To DDMMMYY

Days of Operation numeric values (MTWTFSS) (1234567)

Departure

Dep Time

Arrival

Arr Time

Seat

Acft code (IATA)

Service Type

23

2XZ

207

1-Oct-15

14-Oct-15

135

MNL

1025

SYD

2015

230

388

K

24

2XZ

207

16-Oct-15

26-Oct-15

15

MNL

1025

SYD

2015

230

388

K

25

26

2XZ

207

2-Oct-15

19-Oct-15

135

MNL

1025

SYD

2015

230

388

K

27

2XZ

207

23-Oct-15

26-Oct-15

15

MNL

1025

SYD

2015

230

388

K

Sheet2

alzon21 · Aug 11, 2015

Peter_SSs said:
Without further guidance/information, I don't see how that could be done logically/consistently. For example, using your sample data for Flight number 207...

- Rows 23-24 below show your expected results.

- Rows 26-27 show a different set of results that seem to me to also fit the same original data.

Also, why does your first 207 result row use a start date of 1-Oct-15 when the first 207 flight is on 2-Oct-15?

Sheet2

* A B C D E F G H I J K L
* * * * * * * * * * *
*
*
*
*
*
*
*
*
*
*
*
* * * * * * * * * * *
* * * * * * * * * * * *

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:69px;"><col style="width:80px;"><col style="width:110px;"><col style="width:170px;"><col style="width:125px;"><col style="width:75px;"><col style="width:57px;"><col style="width:51px;"><col style="width:52px;"><col style="width:67px;"><col style="width:65px;"><col style="width:58px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]DATA:[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Carrier IATA code[/TD]
[TD="align: center"]Flight number[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Days of Operation numeric values (MTWTFSS) (1234567)[/TD]
[TD="align: center"]Departure[/TD]
[TD="align: center"]Dep Time[/TD]
[TD="align: center"]Arrival[/TD]
[TD="align: center"]Arr Time[/TD]
[TD="align: center"]Seat[/TD]
[TD="align: center"]Acft code (IATA)[/TD]
[TD="align: center"]Service Type[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]2-Oct-15[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]5-Oct-15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]7-Oct-15[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]9-Oct-15[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]12-Oct-15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]14-Oct-15[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]16-Oct-15[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]19-Oct-15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]23-Oct-15[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]26-Oct-15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: center"]RESULT[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: center"]Carrier IATA code[/TD]
[TD="align: center"]Flight number[/TD]
[TD="align: center"]Effective From DDMMMYY[/TD]
[TD="align: center"]Effective To DDMMMYY[/TD]
[TD="align: center"]Days of Operation numeric values (MTWTFSS) (1234567)[/TD]
[TD="align: center"]Departure[/TD]
[TD="align: center"]Dep Time[/TD]
[TD="align: center"]Arrival[/TD]
[TD="align: center"]Arr Time[/TD]
[TD="align: center"]Seat[/TD]
[TD="align: center"]Acft code (IATA)[/TD]
[TD="align: center"]Service Type[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]1-Oct-15[/TD]
[TD="align: center"]14-Oct-15[/TD]
[TD="align: center"]135[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]16-Oct-15[/TD]
[TD="align: center"]26-Oct-15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]2-Oct-15[/TD]
[TD="align: center"]19-Oct-15[/TD]
[TD="align: center"]135[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: center"]2XZ[/TD]
[TD="align: center"]207[/TD]
[TD="align: center"]23-Oct-15[/TD]
[TD="align: center"]26-Oct-15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]MNL[/TD]
[TD="align: center"]1025[/TD]
[TD="align: center"]SYD[/TD]
[TD="align: center"]2015[/TD]
[TD="align: center"]230[/TD]
[TD="align: center"]388[/TD]
[TD="align: center"]K[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi Petter,

Good Day!

my mistake, it should be 1-oct-15.

this is the whole concept of the project schedule database.

for example at the beginning of the year our airline mounted a daily flight:

[TABLE="width: 835"]
<tbody>[TR]
[TD="class: xl66, width: 64"]2XZ[/TD]
[TD="class: xl66, width: 64"]207[/TD]
[TD="class: xl67, width: 77"]1-Jan-15[/TD]
[TD="class: xl67, width: 77"]31-Jan-15[/TD]
[TD="class: xl66, width: 105"]1234567[/TD]
[TD="class: xl66, width: 64"]MNL[/TD]
[TD="class: xl66, width: 64"]1025[/TD]
[TD="class: xl66, width: 64"]SYD[/TD]
[TD="class: xl66, width: 64"]2015[/TD]
[TD="class: xl66, width: 64"]230[/TD]
[TD="class: xl66, width: 64"]388[/TD]
[TD="class: xl66, width: 64"]K[/TD]
[/TR]
</tbody>[/TABLE]

then the management desides to cancel the flights on jan 15 for some reason. then the schedule will be split in to seperate the jan 15 from the range, and cancel or delete it:

[TABLE="width: 835"]
<colgroup><col span="2"><col span="2"><col><col span="7"></colgroup><tbody>[TR]
[TD]2XZ[/TD]
[TD]207[/TD]
[TD]1-Jan-15[/TD]
[TD]14-Jan-15[/TD]
[TD]1234567[/TD]
[TD]MNL[/TD]
[TD]1025[/TD]
[TD]SYD[/TD]
[TD]2015[/TD]
[TD]230[/TD]
[TD]388[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]2XZ[/TD]
[TD]207[/TD]
[TD]16-Jan-15[/TD]
[TD]31-Jan-15[/TD]
[TD]1234567[/TD]
[TD]MNL[/TD]
[TD]1025[/TD]
[TD]SYD[/TD]
[TD]2015[/TD]
[TD]230[/TD]
[TD]388[/TD]
[TD]K[/TD]
[/TR]
</tbody>[/TABLE]

then after howmany days the management said to cancel jan 27 and 28, the schedule will be

[TABLE="width: 835"]
<colgroup><col span="2"><col span="2"><col><col span="7"></colgroup><tbody>[TR]
[TD]2XZ[/TD]
[TD]207[/TD]
[TD]1-Jan-15[/TD]
[TD]14-Jan-15[/TD]
[TD]1234567[/TD]
[TD]MNL[/TD]
[TD]1025[/TD]
[TD]SYD[/TD]
[TD]2015[/TD]
[TD]230[/TD]
[TD]388[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]2XZ[/TD]
[TD]207[/TD]
[TD]16-Jan-15[/TD]
[TD]26-Jan-15[/TD]
[TD]1234567[/TD]
[TD]MNL[/TD]
[TD]1025[/TD]
[TD]SYD[/TD]
[TD]2015[/TD]
[TD]230[/TD]
[TD]388[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]2XZ[/TD]
[TD]207[/TD]
[TD]29-Jan-15[/TD]
[TD]31-Jan-15[/TD]
[TD]456[/TD]
[TD]MNL[/TD]
[TD]1025[/TD]
[TD]SYD[/TD]
[TD]2015[/TD]
[TD]230[/TD]
[TD]388[/TD]
[TD]K[/TD]
[/TR]
</tbody>[/TABLE]

Connection · Aug 13, 2015

Hi Peter and Alzon

Thanks to you both for your code. I had a macro that was taking AGES to run and your versions are WAAAYYYY faster. My source data had a lot more rows and so I have added in some variables to help adapt the code for smaller/bigger data sets.

Here is my final output in case it assists others:

Code:

Sub One_Day_Per_Row_v2()  Dim a, b
  Dim rws As Long, nr As Long, i As Long, j As Long, k As Long, r As Long
  Dim FlightDays As String
  Dim TopLeftData As Range, TopLeftResults As Range
  Dim LastCol, StartDate_Col, EndDate_Col As Integer
  Dim bTitles As Boolean
  Set TopLeftData = Range("A1")
  Set TopLeftResults = Sheets("Results").Range("A1") 'Cells(Rows.Count, TopLeftData.Column).End(xlUp).Offset(3)
  
  'Enter the column Number that the start date and end date are in.
  StartDate_Col = 17
  EndDate_Col = 18
  
  a = TopLeftData.CurrentRegion.Value
  rws = UBound(a, 1)
  For r = 2 To rws
    a(r, EndDate_Col) = a(r, EndDate_Col) - a(r, StartDate_Col) + 1
    k = k + a(r, EndDate_Col)
  Next r
  LastCol = 11 'Total number of columns of result data
  
  ReDim b(1 To k + 1, 1 To LastCol) 'LastCol is the number of columns of data we need.
  For r = 2 To rws  'Total number of rows in the original data set
    For i = 0 To a(r, EndDate_Col) - 1 'Total number of days leave
        'The columns we need are:
            'Unique ID column 1
            'PersonID_EXT 2
            'First Name and Last Name combined 6 and 5
            'Cost Centre 12
            'Org Unit 13
            'Organisational Unit 14
            'Start Date 17
            'Days 19
            'Attendance Type 21
            'Line Manager Name 23
            'Line Manager BRID 24


        nr = nr + 1
        'This if statement creates the title row for the results
        If Not (bTitles) Then
            r = 1
            b(nr, 1) = a(r, 1) 'Key
            b(nr, 2) = a(r, 2) 'PersonID_EXT
            b(nr, 3) = "Name"
            b(nr, 4) = "Trimmed_Cost_Ctr"
            b(nr, 5) = a(r, 13) 'Org Unit
            b(nr, 6) = a(r, 14) 'Organisational Unit
            b(nr, 7) = "Date"
            b(nr, 8) = "Days"
            b(nr, 9) = a(r, 21) 'Leave Type
            b(nr, 10) = "Manager_Name"
            b(nr, 11) = "Manager_Number"
            r = 2
            i = i - 1
            bTitles = True
        Else
            b(nr, 1) = a(r, 1) 'Key
            b(nr, 2) = a(r, 2) 'PersonID_EXT
            b(nr, 3) = a(r, 6) & " " & a(r, 5) 'Initial and Surname
            b(nr, 4) = Val(Mid((("0." & a(r, 12)) * 1), 3, 20)) 'Trimmed cost centre (remove trailing zeros)
            b(nr, 5) = a(r, 13) 'Org Unit
            b(nr, 6) = a(r, 14) 'Organisational Unit
            b(nr, 7) = a(r, StartDate_Col) + i 'The date
            b(nr, 8) = a(r, 19) 'Days leave
            b(nr, 9) = a(r, 21) 'Leave Type
            b(nr, 10) = a(r, 23) 'Manager Name
            b(nr, 11) = a(r, 24) 'Manager Number
        End If
    Next i
  Next r
  
  With TopLeftResults
    With .Offset(0).Resize(nr, LastCol)
      .Columns(7).NumberFormat = "d-mmm-yy"
      .Value = b
      .Columns.AutoFit
    End With
  End With
End Sub

Spilt a date range into one row per day

liorchn

New Member

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

apo

Well-known Member

alzon21

New Member

Peter_SSs

MrExcel MVP, Moderator

alzon21

New Member

alzon21

New Member

alzon21

New Member

Peter_SSs

MrExcel MVP, Moderator

alzon21

New Member

Connection

New Member

Similar threads

Forum statistics

Share this page

Spilt a date range into one row per day

New Member

MrExcel MVP, Moderator

Excel Facts

Well-known Member

New Member

MrExcel MVP, Moderator

New Member

New Member

New Member

MrExcel MVP, Moderator

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock