Advanced sporting & filtering function

cristiander

New Member
Joined
Jul 4, 2017
Messages
7
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?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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"
 
Upvote 0
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
Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,213
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top