VBA: Create unique list based on latest entry in list

[welchs101]

I have a sheet which has 3 cols of data. Lotid, Yield(Yld), Test_date.

For any given "date" I only have a single lotid. HOwever, i want to generate a list of
lot-ids and their yield with the latest test date.

See enclosed file......i try to show an example.

I am trying to do this in VBA........

[Miraun]

Given the conditions you provided, I was able to create it without VBA...

Used two array formulae,
{=SUM(IF(F3=$A$3:$A$15,IF(H3=$C$3:$C$15,$B$3:$B$15,0),0))} for the Yld,
{=MAX(IF($A$3:$A$15=F3,$C$3:$C$15,0))} for the Test_Date...

I assume the actual data is much larger than this, you can always do an advanced filter:
Alt, D, F, A
Then unique records only, copy to another location: F3
Then copy the formulae down..

Was there any particular reason you were looking for the output to be created in VBA?

[tigeravatar]

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

[welchs101]

thanks, both instances (in vba and out) are helpful. thanks.

I was wanting to do this in vba as i am reading the file in via vba and i wanted to keep all the "work" in vba but its nice to know i can do this outside vba as well. So thanks.

fyi: when i click on the get data button the macro has some issues running but if i go into the code and run it from there it works..........as i was hoping it would so thanks!!!!!!!!!!

[welchs101]

Every time i someone posts their soln i learn ALOT! Not only do i learn how to do what i was asking about but i learn different ways to do things. So thanks.

[welchs101]

As i am going through the code i am realizing that it works great. However, for my situation it may not. When i created the test file for the "excel forum" only put 3 of the cols of data. In fact there are about 30 cols of data in addition to the 3 i showed. For the code to work i would need to create an array for each col.

What i was wondering is instead of using the arrays as they are in the code if some how we could just delete the row which has the lesser date. Does this make sense? Any ideas?

Note: I really like the code. Took me a while to go through it. I have some other questions abou the previously posted code which i will start another thread for.....really cool!