Retrieve Cell Value in Excel with largest date between duplicates

Question

I have the following columns in an excel sheet in Excel 2010:

Batch Number        BatchID            END TIME
120319-0001     120319-0001_TEST1   3/20/12 13:44
120319-0001     120319-0001_TEST2   3/20/12 10:05
120319-0002     120319-0002_TEST1   3/20/12 14:40
120319-0002     120319-0002_TEST2   3/20/12 12:46
120319-0003     120319-0003_TEST1   3/20/12 14:01
120319-0003     120319-0003_TEST2   3/20/12 12:11
120319-0004     120319-0004_TEST1   3/20/12 15:37
120319-0004     120319-0004_TEST2   3/20/12 11:59
120319-0005     120319-0005_TEST1   3/20/12 19:06
120319-0005     120319-0005_TEST2   3/20/12 11:47

I need a formula I can fill down in the 4th column that will do the following:

Select all duplicates in column "Batch Number"
Find Max in column "END TIME" for duplicates
Fill 4th column with the Cell Value in Column "BatchID" on Max in "END TIME" on all duplicates

Expected output of 4th column:

120319-0001_TEST1
120319-0001_TEST1
120319-0002_TEST1
120319-0002_TEST1
120319-0003_TEST1
120319-0003_TEST1
120319-0004_TEST1
120319-0004_TEST1
120319-0005_TEST1
120319-0005_TEST1

I've tried using a combination of INDEX, MATCH, and IF statements and haven't gotten it to work yet.

I would be willing to work with a VBA/macro solution too.

The output, i guess you took that from a larger dataset? If so could you remove the ones that are not in your example dataset? (might be wrong, after re-read) — NickSlash
@NickSlash Yes these are a pseudo modification from the original dataset which has > 50,000 rows and more columns — jordanhill123
is it practical to do a sort first to assist with a 1 time fx? Or does this need to be an ongoing live formula? — Kevin
@Kevin I do a sort on BatchID to bring all duplicates in "Batch Number" together for my attempts but a sort can be done on any of the columns before a fill down of the column. It does not need to be a live formula — jordanhill123

NickSlash NickSlash · Accepted Answer · 2013-03-21T22:23:22

I had a quick go trying to do it with Formulas, but I think you'd possibly need to use Array formulas and they make no sense to me!

The following macro should do it though. (could be an issue comparing the timestamps though)

Sub Main()
Dim Sheet As Worksheet
Dim Data As Range

Set Sheet = ThisWorkbook.Worksheets("Sheet1")
Set Data = Sheet.Range("A2:A" & Range("A" & Range("A" & Sheet.UsedRange.Rows.Count).Row)

Dim BatchNumber As Variant
Dim EndTime As Variant
Dim Result As Variant

BatchNumber = Data.Value2
BatchID = Data.Offset(ColumnOffset:=1).Value2
EndTime = Data.Offset(ColumnOffset:=2).Value2
Result = Data.Offset(ColumnOffset:=3).Value2

Dim Index As Integer
Dim Lookup As Integer
Dim Max As Integer

For Index = LBound(BatchNumber, 1) To UBound(BatchNumber, 1)
    Max = Index
    For Lookup = LBound(BatchNumber, 1) To UBound(BatchNumber, 1)
        If BatchNumber(Lookup, 1) = BatchNumber(Index, 1) Then
            If Not Lookup = Index Then
' NOTE: you might to do stuff to the date/time comparison below to get it to work correctly
                If EndTime(Lookup, 1) > EndTime(Index, 1) Then
                    Max = Lookup
                Else
                    Max = Index
                End If
            End If
        End If
    Next Lookup
    Result(Index, 1) = BatchID(Max, 1)
Next Index

Data.Offset(ColumnOffset:=3).Value2 = Result

End Sub

Update

The following one should be a bit quicker as it only iterates over the dataset ~~once~~ twice. Instead of Rows*Rows iterations (i think) for the first.

Sub Main2()
Dim Sheet As Worksheet
Dim Data As Range
Dim vData As Variant
Dim vResult As Variant
Set Sheet = ThisWorkbook.Worksheets("Sheet1")
Set Data = Sheet.Range("A2:A" & Range("A" & Sheet.UsedRange.Rows.Count).Row)

vData = Data.Resize(ColumnSize:=3).Value2
vResult = Data.Value2
Dim List As Object

Set List = CreateObject("Scripting.Dictionary")

Dim Index As Integer
Dim Key As String

For Index = LBound(vData, 1) To UBound(vData, 1)
    Key = vData(Index, 1)
    If List.exists(Key) Then
        If vData(Index, 3) > vData(List(Key)(1), 3) Then
            List(Key)(1) = Index
        End If
    Else
        List.Add vData(Index, 1), Array(Index, Index)
    End If
Next Index

For Index = LBound(vData, 1) To UBound(vData, 1)
    Key = vData(Index, 1)
    vResult(Index, 1) = vData(List(Key)(1), 2)
Next Index

Data.Offset(ColumnOffset:=3).Value2 = vResult

Set List = Nothing

End Sub

Retrieve Cell Value in Excel with largest date between duplicates

1 Answers