Extract to 2nd sheet only flights valid during a 7 day period?

[ctfloydscry]

Excel novice(at best)

Timetable data.csv file

Example/excerpt:

Wish to extract to 2nd sheet only flights valid for 7 day period beginning Jan/14 ending Jan/20 from following....

Jan/1-Jan/13,ABR,5:00AM,MSP,6:20AM,123456,7365,CRJ
Jan/16-Feb/28,ABR,5:00AM,MSP,6:20AM,123456,7365,CRJ
Jan/7-Jan/14,ABR,6:50AM,MSP,8:12AM,7,7365,CRJ
Jan/15-Jan/21,ABR,6:50AM,MSP,8:12AM,17,7365,CRJ
Jan/28-Feb/28,ABR,6:50AM,MSP,8:12AM,7,7365,CRJ
Jan/1-Jan/2,ABR,1:08PM,MSP,2:30PM,12,7363,CRJ
Jan/3-Feb/28,ABR,1:09PM,MSP,2:30PM,1234567,7363,CRJ
Feb/2-Feb/28,ACC,10:00AM,JFK,4:44PM,156,0220,76W
Jan/1-Jan/30,ACC,10:00AM,JFK,4:50PM,1256,0220,76W

Output to second sheet...

Jan/16-Feb/28,ABR,5:00AM,MSP,6:20AM,123456,7365,CRJ
Jan/7-Jan/14,ABR,6:50AM,MSP,8:12AM,7,7365,CRJ
Jan/15-Jan/21,ABR,6:50AM,MSP,8:12AM,17,7365,CRJ
Jan/3-Feb/28,ABR,1:09PM,MSP,2:30PM,1234567,7363,CRJ
Jan/1-Jan/30,ACC,10:00AM,JFK,4:50PM,1256,0220,76W

Possible?

Thank you...

 

[admiral100]

Hi,

To make it easier I added 2 helper columns :

C2 =DATE(2018,MONTH(LEFT(A2,FIND("-",A2)-1)),DAY(MID(A2,FIND("/",A2)+1,(FIND("-",A2)-1)-(FIND("/",A2)))))
D2 =DATE(2018,MONTH(MID(A2,FIND("-",A2)+1,(FIND(",",A2)-1)-(FIND("-",A2)))),DAY(MID(MID(A2,FIND("-",A2)+1,(FIND(",",A2)-1)-(FIND("-",A2))),5,2)))

G2 & H2 your date criteria

Ctrl+Shift+Enter NOT just Enter
A17 =IFERROR(INDEX($A$2:$A$10,SMALL(IF($H$2>=$C$2:$C$10,IF($G$2<=$D$2:$D$10,ROW($A$1:$A$9))),ROW(A1))),"")


[TABLE="width: 964"]
<colgroup><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Flights[/TD]
[TD][/TD]
[TD]Extract Start[/TD]
[TD]Extract End[/TD]
[TD][/TD]
[TD]Criteria[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jan/1-Jan/13,ABR,5:00AM,MSP,6:20AM,123456,7365,CRJ[/TD]
[TD][/TD]
[TD]01/01/18[/TD]
[TD]13/01/18[/TD]
[TD][/TD]
[TD][/TD]
[TD]14/01/18[/TD]
[TD]20/01/18[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jan/16-Feb/28,ABR,5:00AM,MSP,6:20AM,123456,7365,CRJ[/TD]
[TD][/TD]
[TD]16/01/18[/TD]
[TD]28/02/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jan/7-Jan/14,ABR,6:50AM,MSP,8:12AM,7,7365,CRJ[/TD]
[TD][/TD]
[TD]07/01/18[/TD]
[TD]14/01/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jan/15-Jan/21,ABR,6:50AM,MSP,8:12AM,17,7365,CRJ[/TD]
[TD][/TD]
[TD]15/01/18[/TD]
[TD]21/01/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jan/28-Feb/28,ABR,6:50AM,MSP,8:12AM,7,7365,CRJ[/TD]
[TD][/TD]
[TD]28/01/18[/TD]
[TD]28/02/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jan/1-Jan/2,ABR,1:08PM,MSP,2:30PM,12,7363,CRJ[/TD]
[TD][/TD]
[TD]01/01/18[/TD]
[TD]02/01/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Jan/3-Feb/28,ABR,1:09PM,MSP,2:30PM,1234567,7363,CRJ[/TD]
[TD][/TD]
[TD]03/01/18[/TD]
[TD]28/02/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Feb/2-Feb/28,ACC,10:00AM,JFK,4:44PM,156,0220,76W[/TD]
[TD][/TD]
[TD]02/02/18[/TD]
[TD]28/02/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Jan/1-Jan/30,ACC,10:00AM,JFK,4:50PM,1256,0220,76W[/TD]
[TD][/TD]
[TD]01/01/18[/TD]
[TD]30/01/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Jan/16-Feb/28,ABR,5:00AM,MSP,6:20AM,123456,7365,CRJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Jan/7-Jan/14,ABR,6:50AM,MSP,8:12AM,7,7365,CRJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Jan/15-Jan/21,ABR,6:50AM,MSP,8:12AM,17,7365,CRJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Jan/3-Feb/28,ABR,1:09PM,MSP,2:30PM,1234567,7363,CRJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Jan/1-Jan/30,ACC,10:00AM,JFK,4:50PM,1256,0220,76W[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ctfloydscry]

Thank you for the reply.

The only version of Excel I have is from Office 2000 on an older laptop, one that I haven't used in quite some time. Even then I only used Excel for just very basic editing on above type data, and as such, I'm not too familiar on the use of formulas and other functions, so you'll have to pardon my ignorance here, but, (a) will these work with Excel 2000? If so, is there an idiots guide as to how these are applied? I've been watching tutorials and reading other information for beginners...but at my age there's definitely a bit of a learning curve. Thanks again.