Last time in L for each in A

[Luke777]

Hi all,

Column A contains a bunch of IDs, L contains various times - I would like a lovely bit of code that chucks out a unique list of those ID's with the last associated time on another sheet).

Unfortunately I had a bit of code that would do this for me, but I've lost it.

Here is my example data

1219											24/12/2021 17:00
1219											24/12/2021 17:15
1219											24/12/2021 19:30
1219											24/12/2021 20:00
1219											24/12/2021 22:00
1219											24/12/2021 22:15
1219											25/12/2021 00:30
932											24/12/2021 07:45
932											24/12/2021 08:15
932											24/12/2021 08:45
932											24/12/2021 09:45
932											24/12/2021 10:00
932											24/12/2021 13:30
932											24/12/2021 14:00
932											24/12/2021 15:00
932											24/12/2021 15:15
932											24/12/2021 17:30
916											24/12/2021 13:00
916											24/12/2021 14:30
916											24/12/2021 14:45
916											24/12/2021 16:45
916											24/12/2021 17:15
916											24/12/2021 19:15
916											24/12/2021 19:45
916											24/12/2021 22:00
916											24/12/2021 22:15
916											25/12/2021 00:30
542											24/12/2021 08:00
542											24/12/2021 09:00
542											24/12/2021 09:15
542											24/12/2021 11:15
542											24/12/2021 11:30
542											24/12/2021 12:39
542											24/12/2021 13:09
542											24/12/2021 14:15
542											24/12/2021 14:30
542											24/12/2021 15:30
542											24/12/2021 15:45
542											24/12/2021 16:45

The results on sheet2 should look something like the following

1219										25/12/2021 00:30
932										24/12/2021 17:30
916										25/12/2021 00:30
542										24/12/2021 16:45

with the times now in K instead of L

Thanks everyone

 

[Peter_SSs]

Does it need to be code?
It can be done with two single-cell formulas.

Luke777.xlsm
	A										K
1	1219										25/12/2021 00:30
2	932										24/12/2021 17:30
3	916										25/12/2021 00:30
4	542										24/12/2021 16:45
5
Sheet2

Cell Formulas
Range	Formula
A1:A4	A1	=UNIQUE(Sheet1!A1:A39)
K1:K4	K1	=MAXIFS(Sheet1!L1:L39,Sheet1!A1:A39,A1#)
Dynamic array formulas.

If you are not sure of the exact range in Sheet1 then change the Sheet2 A1 formula to something like this

=UNIQUE(FILTER(Sheet1!A1:A1000,Sheet1!A1:A1000<>""))

 

[Luke777]

Does it need to be code?
It can be done with two single-cell formulas.

Luke777.xlsm
	A										K
1	1219										25/12/2021 00:30
2	932										24/12/2021 17:30
3	916										25/12/2021 00:30
4	542										24/12/2021 16:45
5
Sheet2

Cell Formulas
Range	Formula
A1:A4	A1	=UNIQUE(Sheet1!A1:A39)
K1:K4	K1	=MAXIFS(Sheet1!L1:L39,Sheet1!A1:A39,A1#)
Dynamic array formulas.

If you are not sure of the exact range in Sheet1 then change the Sheet2A1 formula to something like this

=UNIQUE(FILTER(Sheet1!A1:A1000,Sheet1!A1:A1000<>""))

Thanks!

I keep forgetting formulas like this exist.. seems like a much simpler solution than any sort of loop I had in mind. I'll give it a go and let you know how I get on If you don't hear anything else it worked perfectly and I'll mark as answer

 

[Peter_SSs]

 

[mohadin]

Hi
If code then try

Sub test()
    lr = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    a = Sheets("sheet1").Cells(1, 1).Resize(Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row, 12)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If Not .Exists(a(i, 1)) Then
                .Add a(i, 1), a(i, 12)
            Else
                .Item(a(i, 1)) = a(i, 12)
            End If
        Next
        Sheets("sheet2").Cells(1, 1).Resize(.Count) = Application.Transpose(.Keys)
        Sheets("sheet2").Cells(1, 11).Resize(.Count) = Application.Transpose(.Items)
    End With
End Sub