Search for duplicates in two sheets, and delete row if duplicate

Hello,

I have a workbook with two sheets named "Kundeforinger" and "Mangler Foringer" with lots of data in both sheets. I have tried for a couple of hours to make this work now, with no luck.

What I want to achieve:
See if there are any duplicates in column "A" between the two sheets, and if a duplicate value is found, delete the entire row in the sheet named "Mangler Foringer"

The code I have come up with works, sort of. Some of the cells are blank with no value, and I think my code stops the duplicate-searching here. It only deletes like 30 lines, instead of 300.

Any help? Please feel free to suggest a different approach with the coding

Here is the code I have so far

Please Login or Register  to view this content.

Hi,

Please attach a sample file for better understanding.

Regards,

Please see attached sample workbook

SampleFile.xlsm

Where are the duplicates in columns A on the sample attached? Are you sure you have attached the correct sample?

Column A in sheet MANGLER FØRING and column A in sheet KUNDEFØRINGER. If value in col A in the first sheet i mentioned exists in colunm A in the second sheet, delete entire row.

I reiterate AB33's suggestion of maybe you attached a wrong, or unduly modified, workbook.

Running your own code deletes just one line, nothing like the 30 you claim to delete, and far from the 300 or so that you claim exist.

An independent code also found only one value in MANGLER FØRINGER ColA that's duplicated from KUNDEFØRINGER ColA

That's the value 89579 on line 1519 in MANGLER FØRINGER ColA.


Perhaps you should consider re-stating your request.

Well, it looks like it's the correct one i uploaded.

Sheet Kundeføringer has 1581 rows in col A
Sheet Mangler Føringer has 1767 rows in col A

After the macro has run I should end up with the result in sheet "What i want"; 183 rows col A. These are the unique values.

As Kalak pointed out to you, there is a single duplicate on the sample. Could you tell us which lines are duplicates on your sample? Give us the row nos.

Sure! I'll list the first seven values, as listing all of them would

Row in sheet Kundeføringer - Row in sheet mangler føringer - value
971 - 11- 11101
972 - 12 - 11115
973 - 13 - 11197
974 - 14 - 11216
975 - 15 - 11245
725 - 16 - 12276
726 - 17 - 12340

You might look at the following code and see if you agree with the logic behind it.

Step through this code (using the attached file in your post#3), note what it does and what result it gives.

Although both of your "11101" may look the same, according to VBA they are not the same. I haven't looked specifically at why they are different because you didn't request that.

Your request was, as I understand, to use VBA to remove duplicates.
VBA doesn't recognize your "duplicates", perhaps explaining the fruitless "couple of hours" as described in your opening post.

It may be some of your "numbers" contain non-printing characters such as Chr(28), Chr(31), Chr(141), Chr(157) etc. in which case they may well look the same but are not. Maybe other reasons. I know nothing about how your data were generated. But you specifically asked about duplicates.

Please Login or Register  to view this content.

stupid stupid me.. Sorry guys, there was some hidden characters. Ran this vba first and the duplicate remover then worked like a charm!

Please Login or Register  to view this content.