VBA to copy row of data based on criteria

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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try this :-
Results sheet(2)
Code:
[COLOR="Navy"]Sub[/COLOR] MG24Mar00
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dt1         [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Dt2         [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] k
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] oHds
oHds = Range("A1").Resize(, 11).Value
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Dt1 = CDbl(DateValue(Dn.Offset(, 2)) + Dn.Offset(, 3) / 24)
Dt2 = CDbl(DateValue(Dn.Offset(, 9)) + Dn.Offset(, 10) / 24)
[COLOR="Navy"]If[/COLOR] Dt2 > Dt1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Array(Dn, Dt2 - Dt1)
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.Value)
           [COLOR="Navy"]If[/COLOR] Q(1) < (Dt2 - Dt1) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Q(0) = Dn
                Q(1) = Dt2 - Dt1
            [COLOR="Navy"]End[/COLOR] If
        .Item(Dn.Value) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
c = 1
 Sheets("Sheet2").Range("A1").Resize(, 11) = oHds
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    [COLOR="Navy"]For[/COLOR] Ac = 0 To 10
    [COLOR="Navy"]If[/COLOR] Ac = 3 Or Ac = 5 Or Ac = 9 [COLOR="Navy"]Then[/COLOR]
    Sheets("sheet2").Range("A" & c).Offset(, Ac) = Format(.Item(k)(0).Offset(, Ac).Value, "dd/mm/yyyy")
    [COLOR="Navy"]Else[/COLOR]
    Sheets("sheet2").Range("A" & c).Offset(, Ac) = .Item(k)(0).Offset(, Ac).Value
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] With
MsgBox "Run"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Mick,

It almost does exactly what I was after, however the code is not returning the first row that contains the first date time that is passed the SLA due date.

Ill have a play and see if I can modify it slightly.

Cheers

John
 
Upvote 0
This is the return values on sheet (2) althouigh You need to change the 3 to a 2 as below :-
Rich (BB code):
If Ac = 2 Or Ac = 5 Or Ac = 9 Then

[TABLE="width: 637"]
<TBODY>[TR]
[TD="class: xl66, width: 86, bgcolor: #ccffff"]incident_no
[/TD]
[TD="class: xl66, width: 58, bgcolor: #ccffff"]priority
[/TD]
[TD="class: xl66, width: 86, bgcolor: #ccffff"]sla_due_date
[/TD]
[TD="class: xl66, width: 86, bgcolor: #ccffff"]sla_due_time
[/TD]
[TD="class: xl66, width: 54, bgcolor: #ccffff"]status
[/TD]
[TD="class: xl66, width: 86, bgcolor: #ccffff"]date_last_updated
[/TD]
[TD="class: xl67, width: 86, bgcolor: #ccffff"]time_last_updated
[/TD]
[TD="class: xl66, width: 86, bgcolor: #ccffff"]old_status
[/TD]
[TD="class: xl66, width: 53, bgcolor: #ccffff"]new_status
[/TD]
[TD="class: xl66, width: 86, bgcolor: #ccffff"]date_updated
[/TD]
[TD="class: xl67, width: 86, bgcolor: #ccffff"]time_updated
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]196664
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]30/01/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17:57:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]Closed
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]02/01/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]09:34:40
[/TD]
[TD="class: xl63, bgcolor: transparent"]Escalated to 3rd Party
[/TD]
[TD="class: xl63, bgcolor: transparent"]Closed
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]02/01/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]09:34:42
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]194531
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]30/01/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15:43:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]Closed
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]02/01/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10:45:03
[/TD]
[TD="class: xl63, bgcolor: transparent"]Escalated to 3rd Party
[/TD]
[TD="class: xl63, bgcolor: transparent"]Closed
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]02/01/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10:25:41
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]191877
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl65, bgcolor: transparent"]16/01/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11:24:00
[/TD]
[TD="class: xl63, bgcolor: transparent"]Closed
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]02/01/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10:55:05
[/TD]
[TD="class: xl63, bgcolor: transparent"]Passed Back
[/TD]
[TD="class: xl63, bgcolor: transparent"]Closed
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]02/01/2013
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10:55:11
[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
I changed it but still cant get the right row. It should draw out this row for 194531

[TABLE="width: 1277"]
<tbody>[TR]
[TD="class: xl65, width: 57, align: right"]194531[/TD]
[TD="class: xl65, width: 28, align: right"]4[/TD]
[TD="class: xl66, width: 90, align: right"]30/01/2013[/TD]
[TD="class: xl67, width: 92, align: right"]15:43:00[/TD]
[TD="class: xl65, width: 49"]Closed[/TD]
[TD="class: xl66, width: 125, align: right"]01/02/2013[/TD]
[TD="class: xl67, width: 126, align: right"]10:45:03[/TD]
[TD="class: xl65, width: 220"]Open[/TD]
[TD="class: xl65, width: 220"]Escalated to 3rd Party[/TD]
[TD="class: xl66, width: 135, align: right"]30/01/2013[/TD]
[TD="class: xl67, width: 135, align: right"]16:24:21

[/TD]
[/TR]
</tbody>[/TABLE]
This is the first Row where time and date updated first exceed SLA due date.

Otherwise the code works perfectly :)
 
Upvote 0
I think the last, two rows where incorrect !!.
Change the line (in red) around as shown below you'll see what I mean if you compare the two lines.

Rich (BB code):
Q = .Item(Dn.Value)
          If Dt2 - Dt1 < Q(1) Then
                Set Q(0) = Dn
 
Upvote 0
Genius works perfectly :)

Thanks for all your help.

Any suggestions on best ways for me to learn vba?

Many Thanks

John
 
Upvote 0
Thanks for the Feed back:- Like most things , get a couple of books and lots of practice !!!
Regrds Mick
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

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