Solved How to find and copy data based on several trigger dates

December 14, 2018 at 01:58:25
Specs: Windows 10
Hi

Not sure if this is all too complex or I'm asking too much. I'm just not sure where to start so some pointers would be greatly appreciated.

I need to find rows where a "Date Asigned" is within a certain amount of time of one of 3 case age categories: 31-90 days, 91-180 days or 181 days plus - all from whatever the current date is. So if the Date Assigned were 19/11/2018 and today's date is 14/12/2018 then the case is 25 days old.

Bearing this in mind, I then need to find the relevant rows and copy certain cells from each row based on one of the following criteria:

Criteria 1) If the case age (based on today's date minus Date Assigned) is within 7 days of falling into the 31-90 day bracket
Criteria 2) If the case age is within 14 days of falling into the 91-180 day bracket
Criteria 3) If the case age is within 28 days of falling within the 180 days plus bracket

For each row that is found fitting one of the criteria above, I'd need to copy the cells from columns A, D and E on Sheet1 (see example sheet below) and paste them into columns A, B and C on Sheet2. For each row pasted, I'd then also want to insert some category text into column D on Sheet2 to show which bracket each case is falling into (see second example layout below).

Example source data from Sheet1:

      A         B          C           D                E
    "Ref"  "Submitter"   "Team"   "Assigned To"   "Date Assigned"
1   1858      John       North        Jo            19/11/2018 
2   1347      Sue        South      Laura           05/07/2018
3    985      Tom        East         Jo            11/02/2017


Example destination results to be pasted into Sheet2:

    A         B               C                 D
  "Ref"  "Assigned To"  "Date Assigned"  "Moving to Bracket"
1  1858       Jo          19/11/2018         31 to 90
2  1347     Laura         05/07/2018         180 plus

So in this example the relevant data for case ref 1858 is copied because the case age (25 days from 19/11/2018 to 14/12/2018) falls within 7 days of moving into the 31-90 bracket; and for ref 1347 because its case age (162 days from 05/07/2018 to 14/12/2018) is within 28 days of falling within the 180 day plus bracket.

So in my simple mind, I guess I need a macro that will find the right rows (according to the three criteria above) using a formula to calculate the case ages in Sheet1, then copy the relevant data from each found row to Sheet2?

Any ideas?

message edited by ScottV


See More: How to find and copy data based on several trigger dates

Report •

✔ Best Answer
December 31, 2018 at 07:07:21
Try this instruction instead:

If Sheets(1).Cells(myDates, "F") = "Work in progress" Then

When I added that instruction, I did not specifically reference Sheet1. As written, the code is going to reference whatever sheet is active at the time that instruction is executed. My guess from afar is that you did not have the focus on Sheet1 when you ran the code, therefore Cells(myDates, "F") = "Work in progress" was indeed False.

Let me know if that works for you.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
December 15, 2018 at 17:14:26
It seems doable, but not tonight. I'll give it a shot either tomorrow or Monday.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
December 17, 2018 at 13:19:37
I came up with some code that seems to work with your example data. Before I post it, I have 2 quick questions:

1 - For Ref numbers that are already aged > 180 days, e.g. Ref 985, I assume that they should be ignored since they are not "moving" into that group, i.e. they are already there. Is that correct?

2 - The code can either append data to what is already in Sheet 2 each time it is run or it can clear Sheet 2 and create a then-current list. Which do you prefer?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#3
December 17, 2018 at 15:15:38
Thanks DerbyDad03.

Question 1: Correct - I don’t need to know about anything that’s already moved into the 181+ bracket.

Question 2: Clear and create then-current would be preferable

I’m on holiday now and won’t have a chance to test out anything you post until 27/12/2018 so no rush. Thanks as usual for your great help.

Happy Christmas!


Report •

Related Solutions

#4
December 17, 2018 at 16:33:04
If I don't finish it tonight, I won't finish it at all. ;-)

The code assumes that you already have the headers in Sheet 2, Row 1. When it clears the sheet, it will leave the header row.

You'll need to test this carefully and make sure that I am not off by a day in the various brackets. You know better than I do where the border of each bracket is. Minor adjustments will fix any issues.

Sub DateBrackets()

'Clear Sheet 2, Leave Header Row
  Sheets(2).Rows(2 & ":" & Rows.Count).ClearContents

'Determine Last Row with data on Sheet 1
  lastSrcRw = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row

'Loop through Column E, calculate Case Age
    For myDates = 2 To lastSrcRw
      caseAge = Date - Sheets(1).Cells(myDates, "E")
   
'Set New Bracket based on Case Age
      If caseAge < 181 Then
        If 180 - caseAge < 28 Then
           new_Bracket = "180 Plus"
        ElseIf 91 - caseAge < 14 Then
           new_Bracket = "91 to 180"
        ElseIf 30 - caseAge < 7 Then
           new_Bracket = "31 to 90"
        End If
      End If

 'If new Bracket required, copy data to Sheet 2
      If new_Bracket <> "" Then
        With Sheets(2)
          nxtDstRw = .Cells(Rows.Count, "A").End(xlUp).Row + 1
         .Cells(nxtDstRw, "A") = Sheets(1).Cells(myDates, "A")
         .Cells(nxtDstRw, "B") = Sheets(1).Cells(myDates, "D")
         .Cells(nxtDstRw, "C") = Sheets(1).Cells(myDates, "E")
         .Cells(nxtDstRw, "D") = new_Bracket
        End With
           
'Clear New Bracket flag
      new_Bracket = ""
      End If
    
    Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
December 27, 2018 at 07:03:22
Hi DerbyDad03

I hope you had a great Christmas.

The code works great, except for one thing. I was getting a lot more results than I expected. Stupidly, I forgot that there is a "Status" column, let's say column F in Sheet1. The only rows that your code should operate on are those where the case status = "Work in progress". All other values in the "Status" column should be ignored.

Would you add something to each of the If statements in the New Bracket section of your code to look at the status in column F on sheet 1 looking for the "Work in progress" status?

Thanks


Report •

#6
December 29, 2018 at 13:05:10
I see that you have marked the thread solved even though I had not yet addressed your last requirement. I'm not sure if you still the Status check added, but here it is anyway.

re: "Would you add something to each of the If statements in the New Bracket section of your code to look at the status in column F on sheet 1 looking for the "Work in progress" status?"

The Status check does not need to be done in the New Bracket section. That would mean doing all the other work first and then checking Column F. That is a waste of resources. The more efficient manner is to check the Status column first. If "Work in progress" is not present, there is no need to do any more work on that row.

Try this code:

Sub DateBrackets_V2()

'Clear Sheet 2, Leave Header Row
  Sheets(2).Rows(2 & ":" & Rows.Count).ClearContents

'Determine Last Row with data on Sheet 1
  lastSrcRw = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row

'Loop through Column F, Status must be "Work in progress"
    For myDates = 2 To lastSrcRw
     If Cells(myDates, "F") = "Work in progress" Then
      
'Calculate Case Age from Column E,
      caseAge = Date - Sheets(1).Cells(myDates, "E")
   
'Set New Bracket based on Case Age
      If caseAge < 181 Then
        If 180 - caseAge < 28 Then
           new_Bracket = "180 Plus"
        ElseIf 91 - caseAge < 14 Then
           new_Bracket = "91 to 180"
        ElseIf 30 - caseAge < 7 Then
           new_Bracket = "31 to 90"
        End If
      End If

 'If new Bracket required, copy data to Sheet 2
      If new_Bracket <> "" Then
        With Sheets(2)
          nxtDstRw = .Cells(Rows.Count, "A").End(xlUp).Row + 1
         .Cells(nxtDstRw, "A") = Sheets(1).Cells(myDates, "A")
         .Cells(nxtDstRw, "B") = Sheets(1).Cells(myDates, "D")
         .Cells(nxtDstRw, "C") = Sheets(1).Cells(myDates, "E")
         .Cells(nxtDstRw, "D") = new_Bracket
        End With
           
'Clear New Bracket flag
      new_Bracket = ""
      End If
      
     End If
     
    Next
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#7
December 31, 2018 at 05:44:22
I marked it solved because I got a prompt email asking me to. Also because you solved my initial ask. I'll remove it as solved for now until this gets worked out.

Thanks for the additional code. It's a bit of a strange one because generally it's not working. To start debugging I set up a simple data set on Sheet1 like this:

      A         B           C           D               E                F
1   "Ref"  "Submitter"   "Team"   "Assigned To"   "Date Assigned"     "Status"
2     1       John        North        Jo            15/07/2018    Work in progress
3     2       Dave        West         Jo            03/10/2018    Complete   
4     3       Sue         East         Laura         26/12/2018    Work in progress
5     4       John        North        Jo            01/01/2017    Work in progress
6     5       Dave        West         Jo            02/01/2017    Work in progress
7     6       Sue         East         Laura         03/01/2017    Work in progress
8     7       John        North        Jo            04/01/2017    Work in progress
9     8       Dave        West         Jo            05/01/2017    Complete
10    9       Sue         East         Laura         02/12/2018    Work in progress

By my reckoning, only the data from rows 2 and 10 should be copied because in the remaining rows, row 3 has the wrong status and all the others are too old (given today's date of 31/12/2018),

I tried single stepping to see what the issue is, but the code doesn't do anything beyond clearing Sheet2. It did work once, but I don't know why because I didn't change anything - either in the code or in the data.

I added a watch to the expression

Cells(myDates, "F") = "Work in progress"
And as I step through, the Watch shows the expression evaluating as False every time - hence why nothing is copying.

I've made sure there's no rogue spaces in the statuses etc but can't get it to work. I don't know if I'm missing something simple and straightforward?


Report •

#8
December 31, 2018 at 07:07:21
✔ Best Answer
Try this instruction instead:

If Sheets(1).Cells(myDates, "F") = "Work in progress" Then

When I added that instruction, I did not specifically reference Sheet1. As written, the code is going to reference whatever sheet is active at the time that instruction is executed. My guess from afar is that you did not have the focus on Sheet1 when you ran the code, therefore Cells(myDates, "F") = "Work in progress" was indeed False.

Let me know if that works for you.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#9
January 3, 2019 at 01:21:19
That did it. Thank you.

In my actual spreadsheet I needed the data to paste into the AB column. It took me a while to figure it out, until I realised I could change your original line

nxtDstRw = .Cells(Rows.Count, "A").End(xlUp).Row + 1

to

nxtDstRw = .Cells(Rows.Count, 28).End(xlUp).Row + 1

The sheet the data searches through has about 1,500 rows and so the code is a little slow, even with screen updating off, so if you know any cunning ways to speed it up then great, but it's not drastic so not the end of the world.

I will have another question about error handling, but I'll post that in a separate thread.

Thank you as always for your generous help.


Report •

Ask Question