CODEWITHSUNDEEP
excelexcel-2007vba
Iron Man
Iron Man

Reputation: 849

Remove Duplicates not working on Date values

I am using VBA code to remove duplicates on a sheet where I have copied data from two sources. Prior to copying the data, I am using other VBA code to format the data so that the formats are identical to the respective data by column. However, the problem is that when I run the remove duplicates code, it is not removing the duplicates. I have done testing by running the process manually (Data->Remove Duplicates). I have tested this one column at a time and discovered that the culprit is the column(s) with the date/time in them. So, to make sure that the data was the same, I am using the serial date/time numbers in the cells, and still have the same issue. Please see the pictures below of the steps I took in the manual process (which are the same results if I run this with my code).

This is the data before the RD process This is the data before the RD process

enter image description here This shows my selections

enter image description here And this is the result. I cannot figure out why the last row was not removed as a duplicate of the first row when they are clearly identical. Can anyone help figure this out?

Upvotes: 1

Views: 1151

Answers (1)

Iron Man
Iron Man

Reputation: 849

It is important to check the source of your data when you are pulling from multiple sources. The second hand data I received was imported and saved as Excel 97-2003 .xls. Then the user did a Save As Excel Workbook (.xlsx). Even when you do a save as from .xls to .xlsx, it will keep the formatting of the .xls workbook. I had them run the export again but directly into a .xlsx format. This corrected the issue outlined in the question. It does not matter how much formatting you do (at least everything I tried), it will never match.

The moral of this story is to verify the initial format version of the data you are receiving.

Upvotes: 2

Related Questions