CODEWITHSUNDEEP
excelexcel-formulavba
jordanhill123
jordanhill123

Reputation: 4182

Retrieve Cell Value in Excel with largest date between duplicates

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:

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.

Upvotes: 2

Views: 1058

Answers (1)

NickSlash
NickSlash

Reputation: 5077

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

Upvotes: 2

Related Questions