Advanced sporting & filtering function

[cristiander]

Hello,

I'm working on a table that centralizes the due dates for various components and I've ran into a problem I don't know how to solve.
The way that it works is that I have a column with the element name and a column with it's due date, but a lot of elements appear multiple times, with different due dates. I need to somehow sort out for each element and filter out all but the most recent due date.

So say I have Comp1 with 12/21/2017 and another Comp1 with 12/11/2017 in the table. I need a function that can filter out the Comp1 with 12/21/2017.
All of the filtered data needs to be in a new column so that it can be used for a Vlookup function.

Is there anything I could use for something like this?

 

[FormR]

Hi, have you considered using a pivot table; you would drag the "element" to the "rows area" and drag the "due date" to the "values area" and change the "Value field setting" to "Max"

 

[cristiander]

Hi, have you considered using a pivot table; you would drag the "element" to the "rows area" and drag the "due date" to the "values area" and change the "Value field setting" to "Max"

That might work.
I might give it a shot.

But I actually managed to make a function that works now

Function custom_sort(unit As String, all_data As Range) As String
    'troubleshooting
    custom_sort = ""
    On Error GoTo ErrorHandle:
    If unit = "" Then GoTo ErrorHandle:
    
    'defining variables
    Dim output As Date, wk As Integer, WK2 As String, new_date As String
    output = 999999
    Dim cell_data As Range

    'checking for smallest date
    For Each cell_data In all_data

            If Mid(cell_data.Value, 7, 3) = unit Then
            
                new_date = Val(Mid(cell_data.Value, 11, Len(cell_data.Value) - 10))
            
                If output > new_date Then
                    output = new_date
                End If
            End If
        
    Next cell_data

    custom_sort = output
    
ErrorHandle:
 End Function