Compare multiple columns and delete duplicates

Please attach a sample workbook so we can help you out better. Also, will the duplicates be one tab or on 2 separate tabs that need to be compared?

I have managed to write the code, using a combination of some vba I found on this site, and some Macro recording. This could ultimately be a very large file, so I would appreciate it if some one could optimise this.

Please Login or Register  to view this content.

Try it.

Please Login or Register  to view this content.

In the attached file, click the green arrow.

Perhaps a macro using AutoFilter could also be of interests?

Please Login or Register  to view this content.

Alf

Thanks for the feedback. I have tested the macro using an autofilter from this site. It seemed to be slower than the existing solution, though that was with a small sample of 500 rows. I expect that going forward I will incorporate the autofilter.

I will test both of your code to see how they work.

Alf, one thing I have noticed - the data being compared in worksheet Master is in columns A and B, my novice glance at your code suggests you may only be referring to column A.

That's because in your uploaded file if a number in the "Dev" column of the Master sheet match a number in the "Dev" column of the New sheet they both have the same "NM" number.

Are you now telling me that for the same "Dev" number in Master and New sheets you can have different "NM" numbers?

Alf

Absolutely, it is possible to have different NM numbers for the same Dev number.

Hence my macro must check both columns for a match.

Hi Nilem,

I just ran your macro, it did not work how I would like it to. On your file it doesn't appear to make any changes to the existing sample data.

I added it to my sample file, and it removed all data on sheet 'New' regardless of whether or not it matched the data on sheet 'Master'.

There was also a Run-time error '1004': Application-defined or object-defined error for the last line
.Range("A2:B2").Resize(j).Value = y:

Here is the latest version of my code, with an autofilter included.

Please Login or Register  to view this content.

Just testing code tags:

Please Login or Register  to view this content.

Now it works properly.

Alf

You had forgotten the "/" in the ending code tag so it didnt work.

Hi arlu1201

You had forgotten the "/" in the ending code tag so it didnt work.

Thanks I must have deleted the "/" without noticing.

Alf

Hi labrooy

After some more testing of my macro code I found a bug. Word of warning as this macro contains an "Activesheet.Usedrange" comand. It works ok in Excel 2003 but not in Excel 2007.

Please Login or Register  to view this content.

Alf

Thanks Alf, I also noticed an issue with the former code, it now works well.