array dynamic vlookup

If your date in column A and the name is in column B, both starting at row 2 then you could put this formula in C2 and copy down the sheet.

=IF(ISNA(MATCH(B2,$B$1:B1,0)),"",IF(A2<>OFFSET($A$1,MATCH(B2,$B$1:B1,0),0),"","Duplicate"))

Then you will just have to filter the rows that say "Duplicate" in column C and delete them.

Edited to add: Poo, I've just realised that while that works fine for the example data you gave, it won't work on a larger data set.

Hi and welcome to the forum.

You're using XL 2007 so if you select the range and then click on the remove duplicates button on the ribbon, it will remove the duplicates for you.

If you were using an earlier version of XL then you could do this using the Advanced Filter instead.

Hi,

So you want both lists in your workbook? Formulae are not an efficient way to do this.

See the attached example which automates the advanced filter.
You can test it by adding to or removing dates/names from columns A and B; it will automatically update the distinct list in columns F and G.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngList As Range
    Dim rngCopyTo As Range
    
    On Error GoTo ErrorHandler
    
    Set rngList = Range("rList")
    
    If Not Intersect(rngList, Target) Is Nothing Then
        
        Set rngCopyTo = Range("rCopyTo")
        
        Application.EnableEvents = False
        rngList.AdvancedFilter action:=xlFilterCopy, copytoRange:=rngCopyTo, Unique:=True
        
    End If


ErrorExit:

    On Error Resume Next
    
    Application.EnableEvents = True
    
    Exit Sub
    
ErrorHandler:

    MsgBox "Error: " & CStr(Err.Number) & vbNewLine & "Error Description: " & Err.Description
    Resume ErrorExit
    
End Sub

Hi,

thinking of adding an option of let's say i just want the filtered data for a certain date, can we do that based off of the code?

To do that, you need to add a criteria range onto the worksheet and then include it in the Range.AdvancedFilter method. See attached.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngList As Range
    Dim rngCriteria As Range
    Dim rngCopyTo As Range
    
    On Error GoTo ErrorHandler
    
    Set rngList = Range("rList")
    Set rngCriteria = Range("rCriteria")
    
    If Not Intersect(Union(rngList, rngCriteria), Target) Is Nothing Then
        
        Set rngCopyTo = Range("rCopyTo")
        
        Application.EnableEvents = False
        rngList.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCriteria, CopyToRange:=rngCopyTo, Unique:=True
        
    End If


ErrorExit:

    On Error Resume Next
    
    Application.EnableEvents = True
    
    Exit Sub
    
ErrorHandler:

    MsgBox "Error: " & CStr(Err.Number) & vbNewLine & "Error Description: " & Err.Description
    Resume ErrorExit
    
End Sub

You're welcome.

If you are happy with the solution(s) provided, please mark your thread as solved.