在Excel中检索单元格值，其中重复项之间的日期最大

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

我在Excel 2010的Excel工作表中有以下列：

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:

我需要一个公式，我可以填写第4列，将执行以下操作：

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
在所有重复项的“结束时间”中，使用“最终时间”中的最大列“批处理ID”中的单元格值填充第4列

Expected output of 4th column:

第4栏的预期产量：

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.

我尝试过使用INDEX，MATCH和IF语句的组合，还没有让它工作。

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

我也愿意使用VBA /宏解决方案。

1 个解决方案

#1

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!

我快速尝试用Formulas来做，但我认为你可能需要使用Array公式，它们对我没有意义！

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.

以下一个应该更快一点，因为它只迭代数据集一次两次。而不是Rows * Rows迭代（我认为）第一次。

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

#1