Duplicates in multiple columns

[AnnieGr]

Good morning, hope someone can help me out.

I have a large inherited spreadsheet that I have to add data to each week. What I would like to do is ask Excel to find duplicate/matches which is fine if its comparing once column against another (i use conditional formatting). However this spreadsheet has two columns that i need to find duplicates. For example my column has a serial number, and a warranty date, the date and the serial number are unique. My master data, is static, and i merge the new data in to it. I then have to find the duplicates and remove them. At the moment I am using conditional formatting on the serial number, then a sort in to serial number order and manually remove the duplicates.


Below red indicates duplicate data. Green is new data and black is not new and is not a duplicate. The new data in red (duplicate) I can see it then go through my spreadsheet and remove it, hope this makes sense. It probably relatively straight forward to you guys. I have looked on youtube, F1 in Excel and cant find a solution.

Sheet 1 - Master Workbook

Serial Number Date
12345 01/02/2020 - existing master data
34567 01/02/2020 - existing master data
22222 02/02/2020 - existing master data
33333 Not Registered - existing master data (although as and when I add the merge data this will populate)
44444 Not Registered - existing master data (although as and when I add the merge data this will populate)

Sheet 2 - New Data workbook

Serial Number Date
12345 01/02/2020 - need to remove (duplicate)
34567 01/02/2020 - need to remove (duplicate)
55555 01/02/2020 - need to add

Finished spreadsheet week 1
12345 01/02/2020
34567 01/02/2020
22222 02/02/2020
33333 Not Registered
44444 Not Registered
55555 01/02/2020

Finished spreadsheet week 2
12345 01/02/2020
34567 01/02/2020
22222 02/02/2020
33333 03/02/2020
44444 04/02/2020
55555 01/02/2020

Just to add, I cannot delete the master spreadsheet as it has 10,000 entries records dating back to 2016, the spreadsheet I merge has a lot less entries as I am adding data for the last 2 financial years. Hence it is a pain in the backside

Thank you

 

[C Moore]

Have you used Power Query (part of Excel most users don't know about)? You could append all 3 together, tell it to remove duplicates, and load it to a 4th tab that would be the reduced rows. You'd never accidentally remove historical data.