Data set of people with multiple dates. VBA coding to remove all but most recent?

**tirons** · 09-14-2015, 12:41 PM

Hi all,

The data set that I'm working with lists Employees by First name, last name, hire date and termination date (as well as a bunch of other unrelated columns). Essentially, the data represents Dates of Employment for all employees of my company. It is not uncommon for employees to be terminated (adding a record of a hire date and a termination date to the list) and then coming back at a later date (such that they now have two records. One for their first record of employment, and now they are hired again, so there are two records under the same person). Is there a VBA code that will sort the employees by name and remove the dates that are not the most current? Any help would be greatly appreciated. Thanks.

**Kaper** · 09-15-2015, 09:13 AM

See such short code - will do:

Sub test()
Range("A1").CurrentRegion.Sort Header:=xlYes, key1:=Range("B1"), order1:=xlAscending, _
  key2:=Range("A1"), order2:=xlAscending, key3:=Range("H1"), order3:=xlDescending
Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

assuming you have date starting from A1 with names in A, lastnames in B and dates in H column (row 1 has headers).

See sample file - Cells are color-coded to make it easier to notice what happens.
Yellow are single entries. For other staff - intensive color is the most recent.

Data set of people with multiple dates. VBA coding to remove all but most recent?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Data set of people with multiple dates. VBA coding to remove all but most recent?

Re: Data set of people with multiple dates. VBA coding to remove all but most recent?

Thread Information

Users Browsing this Thread

Similar Threads

Formula to highlight dates on a calendar for multiple people scheduled for school dates

Need help with finding recent and previous dates based on multiple criteria.

VB code query multiple outlook accounts from excel to find out of office dates

Summing product based off multiple dates and finding most recent

text within multiple strings, look @ dates in next column, return most recent date

Compare multiple dates, can enumerate cell based on most recent date

[SOLVED] How to: Compare multiple dates and enumerate cell in row with most recent date

Bookmarks

Bookmarks

Posting Permissions