VBA: Create unique list based on latest entry in list

welchs101,

Here's a macro solution. I have attached a modified version of your sample file so you can see how it works.

Sub GetDataMacro_for_welchs101()
    
    Const LotIDCol As String = "A"      'The column containing LotIDs in the source data
    Const LotYieldCol As String = "B"   'The column containing Yield numbers in the source data
    Const LotDateCol As String = "C"    'The column containing Dates in the source data
    Const StartRow As String = "3"      'The row that the source data starts on (excluding headers)
    
    Const DestCol As String = "F"       'The column that the extracted LotID's will be sent to
    Const DestStartRow As String = "3"  'The starting row for the extracted data
    
    Dim rngLotIDs As Range: Set rngLotIDs = Range(LotIDCol & StartRow, Cells(Rows.Count, LotIDCol).End(xlUp))
    Dim rngDest As Range:   Set rngDest = Range(DestCol & DestStartRow)
    
    Dim LotIDCell As Range
    Dim LotIDFound As Boolean
    Dim arrMax As Long, arrIndex As Long
    Dim LotID() As String, LotYield() As Double, LotDate() As Date
    
    Application.ScreenUpdating = False
    
    If rngDest.Value <> vbNullString Then
        Range(rngDest, Cells(Rows.Count, rngDest.Offset(0, 2).Column).End(xlUp)).ClearContents
    End If
    
    For Each LotIDCell In rngLotIDs
        LotIDFound = False
        For arrIndex = 1 To arrMax
            If LotID(arrIndex) = LotIDCell.Value Then
                If LotDate(arrIndex) < Range(LotDateCol & LotIDCell.Row).Value Then
                    LotDate(arrIndex) = Range(LotDateCol & LotIDCell.Row).Value
                    LotYield(arrIndex) = Range(LotYieldCol & LotIDCell.Row).Value
                End If
                LotIDFound = True
                Exit For
            End If
        Next
        If LotIDFound = False Then
            arrMax = arrMax + 1
            ReDim Preserve LotID(1 To arrMax)
            ReDim Preserve LotYield(1 To arrMax)
            ReDim Preserve LotDate(1 To arrMax)
            LotID(arrMax) = Range(LotIDCol & LotIDCell.Row).Value
            LotYield(arrMax) = Range(LotYieldCol & LotIDCell.Row).Value
            LotDate(arrMax) = Range(LotDateCol & LotIDCell.Row).Value
        End If
    Next LotIDCell
    
    rngDest.Offset(0, 0).Resize(arrMax, 1).Value = WorksheetFunction.Transpose(LotID)
    rngDest.Offset(0, 1).Resize(arrMax, 1).Value = WorksheetFunction.Transpose(LotYield)
    rngDest.Offset(0, 2).Resize(arrMax, 1).Value = WorksheetFunction.Transpose(LotDate)
    
    Application.ScreenUpdating = True
    
End Sub

Hope this helps,
~tigeravatar