Identify redundant data, but choose the latest date and delete the other one

Hi guys.


I have a problem here. I want to identify the redundant records from thousands of data. Specifically, redundant data means the one that have same Serial Number and Operation Number simultaneously. If the formula can identify them, i want it to keep the data with the latest Finish Execution date , and delete the rest of them. But if the latest date also same, just remove the redundancy. Please refer the attachment and u will get what i mean.

If possible, i prefer the formula to automatically run ( without certain row range) every time i update rows A and B , because i have thousands of data that i need to update everday.


Thanks in advance. Appreciate ur help.redundant forum.xlsx

Yeahh.The filename sounds ugly but i really need one catchy name because my desktop is kind of mess.

I have tried ur method, but it doesnt work in this case. What i mean with redundant data is two consequence rows that has same Serial No and Operation No. BAsically,they may have same Serial No, but different Operation No can mean they are different data anyway. Only then the redundancy will be deleted automatically.

So the matter of redundant or not, will be identified by both Serial No and Operation No, not by only of them.

I hope you gus can help me this.

No need to sort.
redundant forumWithCode.xlsm

Thanks benishiryo and jindon..

Both ways impressively work anyway even you use different methods.


So before i close this thread as 'solved' , i got one special request. The finish date is on column D right. So, what if i want to change it to G4. How do i modify the code so the whole formula still works?
I try to learn the code, but seems nothing much i can understand. So can anyone paste the code and redhighlight the critical part to adjust

1) change "finish date" column to column G
2) change "serial no" column to another column
3) change "operation No " to another column

Thanks both of you.

Adjust

Sub test()
    Dim x As Range, i As Long, txt As String, temp As Range
    Const mySerial As Long = 1
    Const myOpNum As Long = 2
    Const myDate As Long = 4
    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    Application.ScreenUpdating = False
    With Range("a2").CurrentRegion
        For i = 2 To .Rows.Count
            txt = Join$(Array(.Cells(i, mySerial).Value, .Cells(i, myOpNum).Value), Chr(2))
            If Not dic.exists(txt) Then
                Set dic(txt) = .Rows(i)
            Else
                If .Cells(i, myDate).Value >= dic(txt).Cells(myDate).Value Then
                    Set temp = dic(txt)
                    Set dic(txt) = .Rows(i)
                Else
                    Set temp = .Rows(i)
                End If
                If x Is Nothing Then
                    Set x = temp
                Else
                    Set x = Union(x, temp)
                End If
            End If
        Next
    End With
    If Not x Is Nothing Then x.EntireRow.Delete
    Application.ScreenUpdating = True
    Set x = Nothing
    Set dic = Nothing
End Sub

The code perfectly identify the redundant data, however somehow it does eliminating the whole row with the latest finish date.

I really need this feature, remove duplicate and keep the data with latest 'finish date'.


So basically this is the actual worksheet that i'm working on. I have 'red' mark the redundant data with not the latest finish date so you can easily identify them.Then you will know you have solved this problem when you succesful to eliminate the red higlight rows once you run the code.


Can you help me to fix this.Really appreciate ur help.Thanks in advance.

Change to

Const myDate As Long = 7

Thanks dude..You are damn genius.

Thread solved. Really appreciate your help.