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).

Calendar	Event date	Alarm off	Alarm on
01-May-2015	01-May-2015	09:00	12:30
02-May-2015	04-May-2015	07:00	14:30
03-May-2015	05-May-2015	07:00	10:30
04-May-2015	06-May-2015	07:00	13:00
05-May-2015	07-May-2015	08:00	11:00
06-May-2015	08-May-2015	07:00	10:00
07-May-2015	12-May-2015	07:00	10:00
08-May-2015	23-May-2015	14:00	17:00
09-May-2015	27-May-2015	07:00	13:00
10-May-2015	28-May-2015	07:30	10:30
11-May-2015	29-May-2015	07:00	10:00
12-May-2015	01-June-2015	07:00	14:00
13-May-2015	02-June-2015	07:00	10:00
14-May-2015	03-June-2015	07:30	10:30
15-May-2015	04-June-2015	07:00	13:30
16-May-2015	05-June-2015	08:30	11:30
17-May-2015	09-June-2015	07:30	13:30
18-May-2015	10-June-2015	07:00	10:00
19-May-2015	12-June-2015	07:30	10:30
20-May-2015	15-June-2015	07:00	13:30
21-May-2015	17-June-2015	07:30	10:30
22-May-2015	19-June-2015	08:00	11:00
23-May-2015	22-June-2015	07:00	10:00
24-May-2015	30-June-2015	07:30	10:30
25-May-2015	01-July-2015	07:00	13:00
26-May-2015	02-July-2015	16:00	19:30
27-May-2015	03-July-2015	12:00	19:00
28-May-2015	06-July-2015	07:00	13:30
29-May-2015	07-July-2015	07:30	10:30
30-May-2015	13-July-2015	07:00	10:00
31-May-2015	14-July-2015	16:00	22:30

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

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.