Match or sort dates to match other dates

[BravoBravoAu]

Hi guys,

I have a worksheet with four columns (A = Calendar, B = Event Date, C = Alarm off, D = Alarm on) containing details of alarm off and alarm on times.

Column A contains every day from 1 May 2015 until 30 June 2017 - 793 rows including the header;
Column B contains selected days between 1 May 2016 and 30 June 2017 (when the alarm is turned off and then back on again, always the same day) - 302 rows including the header
Columns C and D have times based upon alarm off and alarm on and a matching entry to column B (that is whenever Column B has an event date, column C and D has a alarm off and alarm on time).

I want to somehow sort the worksheet so that the entries in Columns B, C & D align with Column A.

Below is a small sample which hopefully assists my explanation. I'd like to be able to sort the worksheet causing the entry in B3:D3 to actually be in B5:D5 (and B3:D4 be empty).
[TABLE="width: 420"]
<tbody>[TR]
[TD]

Calendar[/TD]
[TD]
Event date[/TD]
[TD]
Alarm off[/TD]
[TD]
Alarm on[/TD]
[/TR]
[TR]
[TD="align: right"]01-May-2015[/TD]
[TD="align: right"]01-May-2015[/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"]12:30[/TD]
[/TR]
[TR]
[TD="align: right"]02-May-2015[/TD]
[TD="align: right"]04-May-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]14:30[/TD]
[/TR]
[TR]
[TD="align: right"]03-May-2015[/TD]
[TD="align: right"]05-May-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]10:30[/TD]
[/TR]
[TR]
[TD="align: right"]04-May-2015[/TD]
[TD="align: right"]06-May-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]13:00[/TD]
[/TR]
[TR]
[TD="align: right"]05-May-2015[/TD]
[TD="align: right"]07-May-2015[/TD]
[TD="align: right"]08:00[/TD]
[TD="align: right"]11:00[/TD]
[/TR]
[TR]
[TD="align: right"]06-May-2015[/TD]
[TD="align: right"]08-May-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]10:00[/TD]
[/TR]
[TR]
[TD="align: right"]07-May-2015[/TD]
[TD="align: right"]12-May-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]10:00[/TD]
[/TR]
[TR]
[TD="align: right"]08-May-2015[/TD]
[TD="align: right"]23-May-2015[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]17:00[/TD]
[/TR]
[TR]
[TD="align: right"]09-May-2015[/TD]
[TD="align: right"]27-May-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]13:00[/TD]
[/TR]
[TR]
[TD="align: right"]10-May-2015[/TD]
[TD="align: right"]28-May-2015[/TD]
[TD="align: right"]07:30[/TD]
[TD="align: right"]10:30[/TD]
[/TR]
[TR]
[TD="align: right"]11-May-2015[/TD]
[TD="align: right"]29-May-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]10:00[/TD]
[/TR]
[TR]
[TD="align: right"]12-May-2015[/TD]
[TD="align: right"]01-June-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]14:00[/TD]
[/TR]
[TR]
[TD="align: right"]13-May-2015[/TD]
[TD="align: right"]02-June-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]10:00[/TD]
[/TR]
[TR]
[TD="align: right"]14-May-2015[/TD]
[TD="align: right"]03-June-2015[/TD]
[TD="align: right"]07:30[/TD]
[TD="align: right"]10:30[/TD]
[/TR]
[TR]
[TD="align: right"]15-May-2015[/TD]
[TD="align: right"]04-June-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]13:30[/TD]
[/TR]
[TR]
[TD="align: right"]16-May-2015[/TD]
[TD="align: right"]05-June-2015[/TD]
[TD="align: right"]08:30[/TD]
[TD="align: right"]11:30[/TD]
[/TR]
[TR]
[TD="align: right"]17-May-2015[/TD]
[TD="align: right"]09-June-2015[/TD]
[TD="align: right"]07:30[/TD]
[TD="align: right"]13:30[/TD]
[/TR]
[TR]
[TD="align: right"]18-May-2015[/TD]
[TD="align: right"]10-June-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]10:00[/TD]
[/TR]
[TR]
[TD="align: right"]19-May-2015[/TD]
[TD="align: right"]12-June-2015[/TD]
[TD="align: right"]07:30[/TD]
[TD="align: right"]10:30[/TD]
[/TR]
[TR]
[TD="align: right"]20-May-2015[/TD]
[TD="align: right"]15-June-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]13:30[/TD]
[/TR]
[TR]
[TD="align: right"]21-May-2015[/TD]
[TD="align: right"]17-June-2015[/TD]
[TD="align: right"]07:30[/TD]
[TD="align: right"]10:30[/TD]
[/TR]
[TR]
[TD="align: right"]22-May-2015[/TD]
[TD="align: right"]19-June-2015[/TD]
[TD="align: right"]08:00[/TD]
[TD="align: right"]11:00[/TD]
[/TR]
[TR]
[TD="align: right"]23-May-2015[/TD]
[TD="align: right"]22-June-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]10:00[/TD]
[/TR]
[TR]
[TD="align: right"]24-May-2015[/TD]
[TD="align: right"]30-June-2015[/TD]
[TD="align: right"]07:30[/TD]
[TD="align: right"]10:30[/TD]
[/TR]
[TR]
[TD="align: right"]25-May-2015[/TD]
[TD="align: right"]01-July-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]13:00[/TD]
[/TR]
[TR]
[TD="align: right"]26-May-2015[/TD]
[TD="align: right"]02-July-2015[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]19:30[/TD]
[/TR]
[TR]
[TD="align: right"]27-May-2015[/TD]
[TD="align: right"]03-July-2015[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]19:00[/TD]
[/TR]
[TR]
[TD="align: right"]28-May-2015[/TD]
[TD="align: right"]06-July-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]13:30[/TD]
[/TR]
[TR]
[TD="align: right"]29-May-2015[/TD]
[TD="align: right"]07-July-2015[/TD]
[TD="align: right"]07:30[/TD]
[TD="align: right"]10:30[/TD]
[/TR]
[TR]
[TD="align: right"]30-May-2015[/TD]
[TD="align: right"]13-July-2015[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]10:00[/TD]
[/TR]
[TR]
[TD="align: right"]31-May-2015[/TD]
[TD="align: right"]14-July-2015[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]22:30[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

Thanks in advance, BBau

 

[Peter_SSs]

Would this suffice?
- I have inserted new columns to move the original columns B:D to F:H
- Formula in B2 is copied down (You would have to adjust the column F range to suit you data rows)
- Formula in C2 is copied across and down (after adjusting ranges again). You may also need to copy the formatting from columns G:H to columns C:D after the formulas have been copied in.

Book1
	A	B	C	D	E	F	G	H
1	Calendar					Event date	Alarm off	Alarm on
2	1-May-15	1-May-15	9:00	12:30		1-May-15	9:00	12:30
3	2-May-15					4-May-15	7:00	14:30
4	3-May-15					5-May-15	7:00	10:30
5	4-May-15	4-May-15	7:00	14:30		6-May-15	7:00	13:00
6	5-May-15	5-May-15	7:00	10:30		7-May-15	8:00	11:00
7	6-May-15	6-May-15	7:00	13:00		8-May-15	7:00	10:00
8	7-May-15	7-May-15	8:00	11:00		12-May-15	7:00	10:00
9	8-May-15	8-May-15	7:00	10:00		23-May-15	14:00	17:00
10	9-May-15					27-May-15	7:00	13:00
11	10-May-15					28-May-15	7:30	10:30
12	11-May-15					29-May-15	7:00	10:00
13	12-May-15	12-May-15	7:00	10:00		1-Jun-15	7:00	14:00
14	13-May-15					2-Jun-15	7:00	10:00
15	14-May-15					3-Jun-15	7:30	10:30
16	15-May-15					4-Jun-15	7:00	13:30
17	16-May-15					5-Jun-15	8:30	11:30
18	17-May-15					9-Jun-15	7:30	13:30
19	18-May-15					10-Jun-15	7:00	10:00
20	19-May-15					12-Jun-15	7:30	10:30
21	20-May-15					15-Jun-15	7:00	13:30
22	21-May-15					17-Jun-15	7:30	10:30
23	22-May-15					19-Jun-15	8:00	11:00
24	23-May-15	23-May-15	14:00	17:00		22-Jun-15	7:00	10:00
25	24-May-15					30-Jun-15	7:30	10:30
26	25-May-15					1-Jul-15	7:00	13:00
27	26-May-15					2-Jul-15	16:00	19:30
28	27-May-15	27-May-15	7:00	13:00		3-Jul-15	12:00	19:00
29	28-May-15	28-May-15	7:30	10:30		6-Jul-15	7:00	13:30
30	29-May-15	29-May-15	7:00	10:00		7-Jul-15	7:30	10:30
31	30-May-15					13-Jul-15	7:00	10:00
32	31-May-15					14-Jul-15	16:00	22:30
Match Dates

Cell Formulas
Range	Formula
B2	=IF(ISNUMBER(MATCH(A2,F$2:F$32,0)),A2,"")
C2	=IF(B2="","",INDEX(G$2:G$32,MATCH($B2,$F$2:$F$32,0)))

 

[BravoBravoAu]

Thank you Peter - that is an excellent solution and very simply explained!!

 

[Peter_SSs]

Thank you Peter - that is an excellent solution and very simply explained!!

You are very welcome.