Remove duplicate rows but keeping the one with highest number in a another column

[harrinho]

Hi everyone. I've gone through this thread but unfortunately doesn't work.

What I'm trying to do, is to keep only one of multiple employee IDs; the one with the highest length of service. As per attached, I want to keep only the red highlighted rows instead of the whole bunch of data.

[TABLE="width: 1025"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]effective_date[/TD]
[TD]emp_id[/TD]
[TD]full_name[/TD]
[TD]length_of_service[/TD]
[TD]hire_date[/TD]
[TD]tenure_grouping[/TD]
[TD] manager_id[/TD]
[TD]emp_business_title[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16 0:00[/TD]
[TD="align: right"]703621[/TD]
[TD]John Miles[/TD]
[TD="align: right"]5.43[/TD]
[TD="align: right"]29-11-10[/TD]
[TD]5-10 Years[/TD]
[TD="align: right"]101724[/TD]
[TD]SMB Strategic Account Executive[/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16 0:00[/TD]
[TD="align: right"]703621[/TD]
[TD]John Miles[/TD]
[TD="align: right"]5.68[/TD]
[TD="align: right"]29-11-10[/TD]
[TD]5-10 Years[/TD]
[TD="align: right"]101724[/TD]
[TD]SMB Strategic Account Executive[/TD]
[/TR]
[TR]
[TD="align: right"]31-10-16 0:00[/TD]
[TD="align: right"]703621[/TD]
[TD]John Miles[/TD]
[TD="align: right"]5.93[/TD]
[TD="align: right"]29-11-10[/TD]
[TD]5-10 Years[/TD]
[TD="align: right"]101724[/TD]
[TD]SMB Strategic Account Executive[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16 0:00[/TD]
[TD="align: right"]703778[/TD]
[TD]Andreas Ronson[/TD]
[TD="align: right"]4.88[/TD]
[TD="align: right"]13-06-11[/TD]
[TD]4-5 Years[/TD]
[TD="align: right"]101621[/TD]
[TD]Senior Account Executive (SMB)[/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16 0:00[/TD]
[TD="align: right"]703778[/TD]
[TD]Andreas Ronson[/TD]
[TD="align: right"]5.13[/TD]
[TD="align: right"]13-06-11[/TD]
[TD]5-10 Years[/TD]
[TD="align: right"]101621[/TD]
[TD]Senior Account Executive (SMB)[/TD]
[/TR]
[TR]
[TD="align: right"]31-10-16 0:00[/TD]
[TD="align: right"]703778[/TD]
[TD]Andreas Ronson[/TD]
[TD="align: right"]5.38[/TD]
[TD="align: right"]13-06-11[/TD]
[TD]5-10 Years[/TD]
[TD="align: right"]101621[/TD]
[TD]Senior Account Executive (SMB)[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16 0:00[/TD]
[TD="align: right"]704287[/TD]
[TD]Sina Mirzad[/TD]
[TD="align: right"]5.13[/TD]
[TD="align: right"]14-03-11[/TD]
[TD]5-10 Years[/TD]
[TD="align: right"]702234[/TD]
[TD]Senior Account Executive (SMB)[/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16 0:00[/TD]
[TD="align: right"]704287[/TD]
[TD]Sina Mirzad[/TD]
[TD="align: right"]5.38[/TD]
[TD="align: right"]14-03-11[/TD]
[TD]5-10 Years[/TD]
[TD="align: right"]702234[/TD]
[TD]Senior Account Executive (SMB)[/TD]
[/TR]
[TR]
[TD="align: right"]31-10-16 0:00[/TD]
[TD="align: right"]704287[/TD]
[TD]Sina Mirzad[/TD]
[TD="align: right"]5.63[/TD]
[TD="align: right"]14-03-11[/TD]
[TD]5-10 Years[/TD]
[TD="align: right"]702234[/TD]
[TD]Senior Account Executive (SMB)[/TD]
[/TR]
[TR]
[TD="align: right"]30-04-16 0:00[/TD]
[TD="align: right"]705704[/TD]
[TD]Lauren Lee[/TD]
[TD="align: right"]4.82[/TD]
[TD="align: right"]05-07-11[/TD]
[TD]4-5 Years[/TD]
[TD="align: right"]702234[/TD]
[TD]Account Executive (SMB)[/TD]
[/TR]
[TR]
[TD="align: right"]31-07-16 0:00[/TD]
[TD="align: right"]705704[/TD]
[TD]Lauren Lee[/TD]
[TD="align: right"]5.07[/TD]
[TD="align: right"]05-07-11[/TD]
[TD]5-10 Years[/TD]
[TD="align: right"]702234[/TD]
[TD]Account Executive (SMB)[/TD]
[/TR]
[TR]
[TD="align: right"]31-10-16 0:00[/TD]
[TD="align: right"]705704[/TD]
[TD]Lauren Lee[/TD]
[TD="align: right"]5.32[/TD]
[TD="align: right"]05-07-11[/TD]
[TD]5-10 Years[/TD]
[TD="align: right"]702234[/TD]
[TD]Account Executive (SMB)[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas much appreciated!

 

[JoeMo]

1. Sort by length of service longest to shortest.
2. Data>Remove Duplicates - check only the boxes emp_id and full_name.

 

[harrinho]

1. Sort by length of service longest to shortest.
2. Data>Remove Duplicates - check only the boxes emp_id and full_name.

Thank you very much. It works fine. The only think I had to do is sto create a unique ID concacenating emp_id and full name as I noticed there were a couple of emp_ids for different names. Therefore, I sorted as suggested and removed dups checking on on the unique_id created field. Thanks!!

 

[JoeMo]

Thank you very much. It works fine. The only think I had to do is sto create a unique ID concacenating emp_id and full name as I noticed there were a couple of emp_ids for different names. Therefore, I sorted as suggested and removed dups checking on on the unique_id created field. Thanks!!

You are welcome - thanks or the reply.

 

[carlrubber]

can do it in VBA also?