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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Thanks dude..You are damn genius.

Thread solved. Really appreciate your help.