Duplicates in multiple columns

AnnieGr

New Member
Joined
Jan 21, 2019
Messages
18
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
558
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top