Match or sort dates to match other dates

BravoBravoAu

Board Regular
Joined
Nov 8, 2011
Messages
64
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
ABCDEFGH
1CalendarEvent dateAlarm offAlarm on
21-May-151-May-159:0012:301-May-159:0012:30
32-May-154-May-157:0014:30
43-May-155-May-157:0010:30
54-May-154-May-157:0014:306-May-157:0013:00
65-May-155-May-157:0010:307-May-158:0011:00
76-May-156-May-157:0013:008-May-157:0010:00
87-May-157-May-158:0011:0012-May-157:0010:00
98-May-158-May-157:0010:0023-May-1514:0017:00
109-May-1527-May-157:0013:00
1110-May-1528-May-157:3010:30
1211-May-1529-May-157:0010:00
1312-May-1512-May-157:0010:001-Jun-157:0014:00
1413-May-152-Jun-157:0010:00
1514-May-153-Jun-157:3010:30
1615-May-154-Jun-157:0013:30
1716-May-155-Jun-158:3011:30
1817-May-159-Jun-157:3013:30
1918-May-1510-Jun-157:0010:00
2019-May-1512-Jun-157:3010:30
2120-May-1515-Jun-157:0013:30
2221-May-1517-Jun-157:3010:30
2322-May-1519-Jun-158:0011:00
2423-May-1523-May-1514:0017:0022-Jun-157:0010:00
2524-May-1530-Jun-157:3010:30
2625-May-151-Jul-157:0013:00
2726-May-152-Jul-1516:0019:30
2827-May-1527-May-157:0013:003-Jul-1512:0019:00
2928-May-1528-May-157:3010:306-Jul-157:0013:30
3029-May-1529-May-157:0010:007-Jul-157:3010:30
3130-May-1513-Jul-157:0010:00
3231-May-1514-Jul-1516:0022:30
Match Dates
Cell Formulas
RangeFormula
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)))
 
Upvote 0
Thank you Peter - that is an excellent solution and very simply explained!!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top