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.

effective_date	emp_id	full_name	length_of_service	hire_date	tenure_grouping	manager_id	emp_business_title
30-04-16 0:00	703621	John Miles	5.43	29-11-10	5-10 Years	101724	SMB Strategic Account Executive
31-07-16 0:00	703621	John Miles	5.68	29-11-10	5-10 Years	101724	SMB Strategic Account Executive
31-10-16 0:00	703621	John Miles	5.93	29-11-10	5-10 Years	101724	SMB Strategic Account Executive
30-04-16 0:00	703778	Andreas Ronson	4.88	13-06-11	4-5 Years	101621	Senior Account Executive (SMB)
31-07-16 0:00	703778	Andreas Ronson	5.13	13-06-11	5-10 Years	101621	Senior Account Executive (SMB)
31-10-16 0:00	703778	Andreas Ronson	5.38	13-06-11	5-10 Years	101621	Senior Account Executive (SMB)
30-04-16 0:00	704287	Sina Mirzad	5.13	14-03-11	5-10 Years	702234	Senior Account Executive (SMB)
31-07-16 0:00	704287	Sina Mirzad	5.38	14-03-11	5-10 Years	702234	Senior Account Executive (SMB)
31-10-16 0:00	704287	Sina Mirzad	5.63	14-03-11	5-10 Years	702234	Senior Account Executive (SMB)
30-04-16 0:00	705704	Lauren Lee	4.82	05-07-11	4-5 Years	702234	Account Executive (SMB)
31-07-16 0:00	705704	Lauren Lee	5.07	05-07-11	5-10 Years	702234	Account Executive (SMB)
31-10-16 0:00	705704	Lauren Lee	5.32	05-07-11	5-10 Years	702234	Account Executive (SMB)

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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?