JSR1306
New Member
- Joined
- Sep 15, 2012
- Messages
- 45
Hello,
Hopefully one of you VBA whizzes will be able to solve my problem
I have a table of data (See below) that I need to extract data from based on the unique ID number and the time that the specific record breached the SLA due date.
For example: record ID 196664 was due by 30/01/13 at 17:57:00 - The first row that goes past this date and time is row 6 with a date and time of 01/02/13 at 09:34:42. This is then the row I want to copy and paste into a new sheet.
All breaching date times after the first one are not required, I only want to return the first one that breaches SLA Due date.
[TABLE="width: 1312"]
<tbody>[TR]
[TD]incident_no
[/TD]
[TD]priority
[/TD]
[TD]sla_due_date
[/TD]
[TD]sla_due_time
[/TD]
[TD]status
[/TD]
[TD]date_last_updated
[/TD]
[TD]time_last_updated
[/TD]
[TD]old_status
[/TD]
[TD]new_status
[/TD]
[TD]date_updated
[/TD]
[TD]time_updated
[/TD]
[/TR]
[TR]
[TD]196664
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]17:57:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:40
[/TD]
[TD]Await Cust 1
[/TD]
[TD]Await Cust 2
[/TD]
[TD]27/01/2013
[/TD]
[TD]11:50:44
[/TD]
[/TR]
[TR]
[TD]196664
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]17:57:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:40
[/TD]
[TD]Await Cust 1
[/TD]
[TD]Await Cust 2
[/TD]
[TD]28/01/2013
[/TD]
[TD]17:55:31
[/TD]
[/TR]
[TR]
[TD]196664
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]17:57:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:40
[/TD]
[TD]Await Cust 2
[/TD]
[TD]Resolved
[/TD]
[TD]29/01/2013
[/TD]
[TD]13:52:17
[/TD]
[/TR]
[TR]
[TD]196664
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]17:57:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:40
[/TD]
[TD]Resolved
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]29/01/2013
[/TD]
[TD]13:57:07
[/TD]
[/TR]
[TR]
[TD]196664
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]17:57:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:40
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:42
[/TD]
[/TR]
[TR]
[TD]196664
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]17:57:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:40
[/TD]
[TD]Await Cust 2
[/TD]
[TD]Await Cust 1
[/TD]
[TD]28/01/2013
[/TD]
[TD]09:20:29
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Open
[/TD]
[TD]Await Cust 1
[/TD]
[TD]21/01/2013
[/TD]
[TD]14:00:05
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:25:41
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Open
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]31/01/2013
[/TD]
[TD]11:42:50
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Await Cust 2
[/TD]
[TD]Escalated to 2nd Line
[/TD]
[TD]29/01/2013
[/TD]
[TD]16:11:30
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]Open
[/TD]
[TD]31/01/2013
[/TD]
[TD]09:55:53
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Await Cust 1
[/TD]
[TD]Await Cust 2
[/TD]
[TD]23/01/2013
[/TD]
[TD]17:57:18
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Escalated to 2nd Line
[/TD]
[TD]Open
[/TD]
[TD]30/01/2013
[/TD]
[TD]14:27:12
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Open
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]31/01/2013
[/TD]
[TD]08:26:22
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Open
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]30/01/2013
[/TD]
[TD]16:24:21
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]Open
[/TD]
[TD]30/01/2013
[/TD]
[TD]16:50:42
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Escalated to 3rd Line
[/TD]
[TD]Passed Back
[/TD]
[TD]25/01/2013
[/TD]
[TD]13:43:33
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Await Cust 1
[/TD]
[TD]Await Cust 2
[/TD]
[TD]14/01/2013
[/TD]
[TD]10:40:00
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Passed Back
[/TD]
[TD]Escalated to 3rd Line
[/TD]
[TD]25/01/2013
[/TD]
[TD]11:15:01
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Await Cust 2
[/TD]
[TD]Escalated to 2nd Line
[/TD]
[TD]15/01/2013
[/TD]
[TD]11:40:54
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Passed Back
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:11
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Open
[/TD]
[TD]Await Cust 1
[/TD]
[TD]12/01/2013
[/TD]
[TD]10:17:54
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Escalated to 2nd Line
[/TD]
[TD]Escalated to 3rd Line
[/TD]
[TD]16/01/2013
[/TD]
[TD]10:46:22
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Escalated to 3rd Line
[/TD]
[TD]Passed Back
[/TD]
[TD]19/01/2013
[/TD]
[TD]09:10:03
[/TD]
[/TR]
</tbody>[/TABLE]
I appreciate any help.
Many Thanks
John
Hopefully one of you VBA whizzes will be able to solve my problem
I have a table of data (See below) that I need to extract data from based on the unique ID number and the time that the specific record breached the SLA due date.
For example: record ID 196664 was due by 30/01/13 at 17:57:00 - The first row that goes past this date and time is row 6 with a date and time of 01/02/13 at 09:34:42. This is then the row I want to copy and paste into a new sheet.
All breaching date times after the first one are not required, I only want to return the first one that breaches SLA Due date.
[TABLE="width: 1312"]
<tbody>[TR]
[TD]incident_no
[/TD]
[TD]priority
[/TD]
[TD]sla_due_date
[/TD]
[TD]sla_due_time
[/TD]
[TD]status
[/TD]
[TD]date_last_updated
[/TD]
[TD]time_last_updated
[/TD]
[TD]old_status
[/TD]
[TD]new_status
[/TD]
[TD]date_updated
[/TD]
[TD]time_updated
[/TD]
[/TR]
[TR]
[TD]196664
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]17:57:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:40
[/TD]
[TD]Await Cust 1
[/TD]
[TD]Await Cust 2
[/TD]
[TD]27/01/2013
[/TD]
[TD]11:50:44
[/TD]
[/TR]
[TR]
[TD]196664
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]17:57:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:40
[/TD]
[TD]Await Cust 1
[/TD]
[TD]Await Cust 2
[/TD]
[TD]28/01/2013
[/TD]
[TD]17:55:31
[/TD]
[/TR]
[TR]
[TD]196664
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]17:57:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:40
[/TD]
[TD]Await Cust 2
[/TD]
[TD]Resolved
[/TD]
[TD]29/01/2013
[/TD]
[TD]13:52:17
[/TD]
[/TR]
[TR]
[TD]196664
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]17:57:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:40
[/TD]
[TD]Resolved
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]29/01/2013
[/TD]
[TD]13:57:07
[/TD]
[/TR]
[TR]
[TD]196664
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]17:57:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:40
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:42
[/TD]
[/TR]
[TR]
[TD]196664
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]17:57:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]09:34:40
[/TD]
[TD]Await Cust 2
[/TD]
[TD]Await Cust 1
[/TD]
[TD]28/01/2013
[/TD]
[TD]09:20:29
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Open
[/TD]
[TD]Await Cust 1
[/TD]
[TD]21/01/2013
[/TD]
[TD]14:00:05
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:25:41
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Open
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]31/01/2013
[/TD]
[TD]11:42:50
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Await Cust 2
[/TD]
[TD]Escalated to 2nd Line
[/TD]
[TD]29/01/2013
[/TD]
[TD]16:11:30
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]Open
[/TD]
[TD]31/01/2013
[/TD]
[TD]09:55:53
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Await Cust 1
[/TD]
[TD]Await Cust 2
[/TD]
[TD]23/01/2013
[/TD]
[TD]17:57:18
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Escalated to 2nd Line
[/TD]
[TD]Open
[/TD]
[TD]30/01/2013
[/TD]
[TD]14:27:12
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Open
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]31/01/2013
[/TD]
[TD]08:26:22
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Open
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]30/01/2013
[/TD]
[TD]16:24:21
[/TD]
[/TR]
[TR]
[TD]194531
[/TD]
[TD]4
[/TD]
[TD]30/01/2013
[/TD]
[TD]15:43:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:45:03
[/TD]
[TD]Escalated to 3rd Party
[/TD]
[TD]Open
[/TD]
[TD]30/01/2013
[/TD]
[TD]16:50:42
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Escalated to 3rd Line
[/TD]
[TD]Passed Back
[/TD]
[TD]25/01/2013
[/TD]
[TD]13:43:33
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Await Cust 1
[/TD]
[TD]Await Cust 2
[/TD]
[TD]14/01/2013
[/TD]
[TD]10:40:00
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Passed Back
[/TD]
[TD]Escalated to 3rd Line
[/TD]
[TD]25/01/2013
[/TD]
[TD]11:15:01
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Await Cust 2
[/TD]
[TD]Escalated to 2nd Line
[/TD]
[TD]15/01/2013
[/TD]
[TD]11:40:54
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Passed Back
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:11
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Open
[/TD]
[TD]Await Cust 1
[/TD]
[TD]12/01/2013
[/TD]
[TD]10:17:54
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Escalated to 2nd Line
[/TD]
[TD]Escalated to 3rd Line
[/TD]
[TD]16/01/2013
[/TD]
[TD]10:46:22
[/TD]
[/TR]
[TR]
[TD]191877
[/TD]
[TD]4
[/TD]
[TD]16/01/2013
[/TD]
[TD]11:24:00
[/TD]
[TD]Closed
[/TD]
[TD]01/02/2013
[/TD]
[TD]10:55:05
[/TD]
[TD]Escalated to 3rd Line
[/TD]
[TD]Passed Back
[/TD]
[TD]19/01/2013
[/TD]
[TD]09:10:03
[/TD]
[/TR]
</tbody>[/TABLE]
I appreciate any help.
Many Thanks
John