find last date and time from multiple

[wilbot]

Good day world

I have a workbook with 2 sheets
sheet 1(Input Data) column A has order number (over 5 000), column B has dates and column C has times
the date and time indicate when was worked on the order number

so the same order number may be in column A more than once depending how many times was work on the order number
I need a code that will look in Input Data sheet when was the last date and time when there was worked on each order number and paste it in sheet 2(Upload Data)

 

[DanteAmor]

Hi @wilbot, welcome to the board!

The above can be obtained with a pivot table:

______________________________________
Or with the following macro:

Sub Find_last_Date()
  Dim a(), sh As Worksheet, i As Long, dic As Object, c As Variant, j As Long
  Set sh = Sheets("Input data")
  a = sh.Range("A2:C" & sh.Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a), 1 To 2)
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a)
    If dic.exists(a(i, 1)) Then
      c = Val(Split(dic(a(i, 1)), "|")(0)) + Val(Split(dic(a(i, 1)), "|")(1))
      If CDbl(a(i, 2)) + a(i, 3) > c Then
        dic(a(i, 1)) = CDbl(a(i, 2)) & "|" & a(i, 3)
      End If
    Else
      dic(a(i, 1)) = CDbl(a(i, 2)) & "|" & a(i, 3)
    End If
  Next
  j = 2
  For Each ky In dic.keys
    Sheets("Sheet2").Range("A" & j).Value = ky
    Sheets("Sheet2").Range("B" & j).Value = Split(dic(ky), "|")(0) + 0
    Sheets("Sheet2").Range("C" & j).Value = Split(dic(ky), "|")(1)
    j = j + 1
  Next
End Sub

Result on Sheet2:

Book1
	A	B	C
1	Order	Date	Time
2	A2	02/12/2019	09:48:00 a.m.
3	A3	02/12/2019	09:50:00 a.m.
4	A4	03/12/2019	09:52:00 a.m.
5	A5	04/12/2019	09:54:00 a.m.
6	A6	05/12/2019	09:56:00 a.m.
7	A7	06/12/2019	09:58:00 a.m.
8	A8	07/12/2019	10:00:00 a.m.
9	A9	08/12/2019	10:02:00 a.m.
Sheet2

 

[Peter_SSs]

Welcome to the MrExcel board!

Another macro to consider (not sure how you wanted the date/time presented). Same layout on Input Data as Dante

Sub LastWorked()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
 
  a = Sheets("Input Data").Range("A2", Sheets("Input Data").Range("C" & Rows.Count).End(xlUp)).Value
  Set d = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a)
    If d(a(i, 1)) < a(i, 2) + a(i, 3) Then d(a(i, 1)) = a(i, 2) + a(i, 3)
  Next i
  Sheets("Upload Data").Range("A1:B1").Value = Array("Order", "Last Worked")
  Sheets("Upload Data").Range("A2:b2").Resize(d.Count) = Application.Transpose(Array(d.Keys, d.Items))
End Sub

Results (Date & Time could be in different columns if you want):

Book1
	A	B
1	Order	Last Worked
2	A2	12/01/2019 9:16:00 AM
3	A3	12/02/2019 9:18:00 AM
4	A4	12/03/2019 9:20:00 AM
5	A5	12/04/2019 9:22:00 AM
6	A6	12/05/2019 9:08:00 AM
7	A7	12/06/2019 9:10:00 AM
8	A8	12/07/2019 9:12:00 AM
9	A9	12/08/2019 9:14:00 AM
Upload Data

so the same order number may be in column A more than once

In case more work is required on this ..
1. Are the order numbers grouped together in column A or could be apart from each other like in Dante's screen shot?
2. Are the Dates /Times in columns B & C always ascending or always descending?

 

[wilbot]

good day Dante and Peter
thank you for your reply's
the attached image is what my data looks like , I have filtered on order 8 and 100 and marked in yellow the ones I actually would like to see in my sheet 2
as you can see all is very random

I have tried Dante's code but then get an error on line " If CDbl(a(i, 2)) + a(i, 3) > c Then "
and error on Peter's code on line " If d(a(i, 1)) < a(i, 2) + a(i, 3) Then"

I am very new to this vba coding
any help is very much appreciated

 

[Peter_SSs]

error on Peter's code on line " If d(a(i, 1)) < a(i, 2) + a(i, 3) Then

Whenever you get an error, it helps if you state exactly what the error message is. When you get the error with my code, click Debug and hover over one of the 'i' variables in that line. What value is shown in the resulting pop-up?

Also hover over the 'a' in a(i,2) and over the 'a' in a(i,3) and see if anything looks unusual with the pop-up date or time.

 

[wilbot]

hi Peter
the error I get is
Run-time error'13':
Type mismatch

screen shots when I hover over d and a

 

[DanteAmor]

I have tried Dante's code but then get an error on line " If CDbl(a(i, 2)) + a(i, 3) > c Then "

It seems that your time is a text, for that, try this:

Sub Find_last_Date()
  Dim a(), sh As Worksheet, i As Long, dic As Object, c As Variant, j As Long, t As Variant
  Set sh = Sheets("Input data")
  a = sh.Range("A2:C" & sh.Range("A" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a), 1 To 2)
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a)
    If dic.exists(a(i, 1)) Then
      c = Val(Split(dic(a(i, 1)), "|")(0)) + Val(Split(dic(a(i, 1)), "|")(1))
      If Not IsNumeric(a(i, 3)) Then t = TimeValue(a(i, 3)) Else t = a(i, 3)
      If CDbl(a(i, 2)) + t > c Then
        dic(a(i, 1)) = CDbl(a(i, 2)) & "|" & a(i, 3)
      End If
    Else
      dic(a(i, 1)) = CDbl(a(i, 2)) & "|" & a(i, 3)
    End If
  Next
  j = 2
  For Each ky In dic.keys
    Sheets("Sheet2").Range("A" & j).Value = ky
    Sheets("Sheet2").Range("B" & j).Value = Split(dic(ky), "|")(0) + 0
    Sheets("Sheet2").Range("C" & j).Value = Split(dic(ky), "|")(1)
    j = j + 1
  Next
End Sub

 

[Peter_SSs]

screen shots when I hover over d and a

What I wanted to know was what the hover values were for 'i' not d and ..

the 'a' in a(i,2) and over the 'a' in a(i,3)

.. not the a in a(i,1) which is what you have shown.

BTW, I think my code should work whether the dates and times are numerical or text.

 

[wilbot]

Thanks a million Dante
works like a charm

 

[DanteAmor]

Thanks a million Dante
works like a charm

I'm glad to help you . Thanks for the feedback ?