Find and delete dupes from different years

bobkap

Board Regular
Joined
Nov 22, 2009
Messages
238
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I have a rather large set of data. (Short example below) I need to create a macro to do the following:
1. Look for duplicates that show the year 2 years apart. In other words, In the case below, we need only to find duplicate first and last names from 2018 and 2020, regardless of the day and month.
2. Once we find a duplicate we need to delete BOTH the 2018 and 2020 record.
3. End result is that we just want to keep 2018 records that DO NOT show up with a 2020 date.
4. While we're at it, remove all 2019 records.
5. With my limited knowledge of Power Query, I thought that this would be a perfect tool for this. But, the goal is to hand this off to users with very limited knowledge of how to use Excel so I thought a macro would be the best route to take as they would only have to enter a few key strokes to get their desired end result.

1599260129507.png


Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,409
in short
- filter for 2018
- remove duplicates
is that what you want?
 

bobkap

Board Regular
Joined
Nov 22, 2009
Messages
238
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Kind of. Say there is a dupe in 2018 and 2020. We need both removed.
 

bobkap

Board Regular
Joined
Nov 22, 2009
Messages
238
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Sorry. End result would be that we only have 2018 records left. So, any record with a 2020 date but no 2018 dupe needs to be deleted.
Thanks!
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,409
End result would be that we only have 2018 records left. So, any record with a 2020 date but no 2018 dupe needs to be deleted
so that is what I said, filter 2019 and 2020 out and remove duplicates from 2018
end result is 2018 without duplicates
see post#2

btw. your example is not representative
 

Watch MrExcel Video

Forum statistics

Threads
1,118,755
Messages
5,574,045
Members
412,565
Latest member
roberttaekim
Top